-- ============================================================
-- نظام إدارة القضايا القضائية - STORISMO Legal CMS
-- Database Schema v1.0
-- Encoding: UTF8MB4
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+01:00";
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `legal_cms`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `legal_cms`;

-- ============================================================
-- Table: users
-- ============================================================
CREATE TABLE `users` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `full_name`     VARCHAR(150) NOT NULL,
    `email`         VARCHAR(191) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `phone`         VARCHAR(20)  DEFAULT NULL,
    `role`          ENUM('admin','clerk','lawyer') NOT NULL,
    `status`        ENUM('active','inactive','pending','rejected') NOT NULL DEFAULT 'pending',
    `avatar`        VARCHAR(255) DEFAULT NULL,
    `last_login`    DATETIME     DEFAULT NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_role`   (`role`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: lawyer_profiles
-- ============================================================
CREATE TABLE `lawyer_profiles` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`               INT UNSIGNED NOT NULL UNIQUE,
    `bar_number`            VARCHAR(50)  DEFAULT NULL,
    `bar_association`       VARCHAR(150) DEFAULT NULL,
    `specialization`        VARCHAR(150) DEFAULT NULL,
    `professional_card`     VARCHAR(255) DEFAULT NULL,  -- file path
    `accreditation_doc`     VARCHAR(255) DEFAULT NULL,  -- file path
    `id_card`               VARCHAR(255) DEFAULT NULL,  -- file path
    `review_status`         ENUM('pending','under_review','approved','rejected') NOT NULL DEFAULT 'pending',
    `rejection_reason`      TEXT         DEFAULT NULL,
    `reviewed_by`           INT UNSIGNED DEFAULT NULL,
    `reviewed_at`           DATETIME     DEFAULT NULL,
    `submission_count`      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_lp_user`     FOREIGN KEY (`user_id`)     REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lp_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: courts
-- ============================================================
CREATE TABLE `courts` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`       VARCHAR(200) NOT NULL,
    `city`       VARCHAR(100) NOT NULL,
    `type`       ENUM('ibtidai','istinaf','naqd') NOT NULL DEFAULT 'ibtidai',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: cases
-- ============================================================
CREATE TABLE `cases` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `case_number`   VARCHAR(50)  NOT NULL UNIQUE,
    `title`         VARCHAR(300) NOT NULL,
    `case_type`     ENUM('civil','criminal','commercial','administrative','family') NOT NULL,
    `plaintiff`     VARCHAR(200) NOT NULL,
    `defendant`     VARCHAR(200) NOT NULL,
    `description`   TEXT         DEFAULT NULL,
    `stage`         ENUM('ibtidai','istinaf','naqd','final') NOT NULL DEFAULT 'ibtidai',
    `status`        ENUM('open','adjourned','closed','appealed','archived') NOT NULL DEFAULT 'open',
    `court_id`      INT UNSIGNED DEFAULT NULL,
    `clerk_id`      INT UNSIGNED NOT NULL,
    `priority`      ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `filing_date`   DATE         NOT NULL,
    `closed_at`     DATETIME     DEFAULT NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_case_court` FOREIGN KEY (`court_id`) REFERENCES `courts`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_case_clerk` FOREIGN KEY (`clerk_id`) REFERENCES `users`(`id`),
    INDEX `idx_case_status` (`status`),
    INDEX `idx_case_stage`  (`stage`),
    INDEX `idx_filing_date` (`filing_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: case_lawyers  (many-to-many)
-- ============================================================
CREATE TABLE `case_lawyers` (
    `case_id`    INT UNSIGNED NOT NULL,
    `lawyer_id`  INT UNSIGNED NOT NULL,
    `role`       ENUM('plaintiff_lawyer','defendant_lawyer','observer') NOT NULL DEFAULT 'plaintiff_lawyer',
    `assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`case_id`, `lawyer_id`),
    CONSTRAINT `fk_cl_case`   FOREIGN KEY (`case_id`)   REFERENCES `cases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cl_lawyer` FOREIGN KEY (`lawyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: sessions  (جلسات)
-- ============================================================
CREATE TABLE `sessions` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `case_id`         INT UNSIGNED NOT NULL,
    `session_number`  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `session_date`    DATETIME     NOT NULL,
    `room`            VARCHAR(50)  DEFAULT NULL,
    `judge_name`      VARCHAR(150) DEFAULT NULL,
    `session_type`    ENUM('initial','continuation','judgment','appeal') NOT NULL DEFAULT 'initial',
    `status`          ENUM('scheduled','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled',
    `clerk_id`        INT UNSIGNED NOT NULL,
    `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_sess_case`  FOREIGN KEY (`case_id`)  REFERENCES `cases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sess_clerk` FOREIGN KEY (`clerk_id`) REFERENCES `users`(`id`),
    INDEX `idx_sess_date`   (`session_date`),
    INDEX `idx_sess_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: session_minutes  (محاضر الجلسات)
-- ============================================================
CREATE TABLE `session_minutes` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id`      INT UNSIGNED NOT NULL UNIQUE,
    `content`         LONGTEXT     NOT NULL,
    `attendees`       TEXT         DEFAULT NULL,   -- JSON or comma-separated
    `decision_type`   ENUM('adjournment','judgment','interlocutory','dismissal') NOT NULL,
    `new_session_date` DATETIME    DEFAULT NULL,   -- if adjourned
    `judgment_text`   TEXT         DEFAULT NULL,   -- if judgment
    `judge_signature` TINYINT(1)   NOT NULL DEFAULT 0,
    `clerk_signature` TINYINT(1)   NOT NULL DEFAULT 0,
    `finalized`       TINYINT(1)   NOT NULL DEFAULT 0,
    `created_by`      INT UNSIGNED NOT NULL,
    `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_sm_session` FOREIGN KEY (`session_id`)  REFERENCES `sessions`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sm_clerk`   FOREIGN KEY (`created_by`)  REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: judgments  (الأحكام)
-- ============================================================
CREATE TABLE `judgments` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `case_id`         INT UNSIGNED NOT NULL,
    `session_id`      INT UNSIGNED DEFAULT NULL,
    `judgment_number` VARCHAR(50)  DEFAULT NULL,
    `judgment_type`   ENUM('ibtidai','istinaf','naqd') NOT NULL DEFAULT 'ibtidai',
    `content`         LONGTEXT     NOT NULL,
    `operative_part`  TEXT         NOT NULL,    -- منطوق الحكم
    `judge_name`      VARCHAR(150) DEFAULT NULL,
    `judgment_date`   DATE         NOT NULL,
    `status`          ENUM('issued','appealed','final','cancelled') NOT NULL DEFAULT 'issued',
    `created_by`      INT UNSIGNED NOT NULL,
    `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_j_case`    FOREIGN KEY (`case_id`)    REFERENCES `cases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_j_session` FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_j_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
    INDEX `idx_j_date`   (`judgment_date`),
    INDEX `idx_j_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: appeals  (الاستئناف)
-- ============================================================
CREATE TABLE `appeals` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `case_id`         INT UNSIGNED NOT NULL,
    `judgment_id`     INT UNSIGNED NOT NULL,
    `appeal_type`     ENUM('istinaf','naqd') NOT NULL,
    `appeal_date`     DATE         NOT NULL,
    `appellant`       VARCHAR(200) NOT NULL,
    `grounds`         TEXT         DEFAULT NULL,
    `status`          ENUM('filed','under_review','decided','rejected') NOT NULL DEFAULT 'filed',
    `new_case_id`     INT UNSIGNED DEFAULT NULL,   -- link to the appeal case
    `result`          TEXT         DEFAULT NULL,
    `decided_at`      DATE         DEFAULT NULL,
    `created_by`      INT UNSIGNED NOT NULL,
    `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_app_case`     FOREIGN KEY (`case_id`)     REFERENCES `cases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_app_judgment` FOREIGN KEY (`judgment_id`) REFERENCES `judgments`(`id`),
    CONSTRAINT `fk_app_newcase`  FOREIGN KEY (`new_case_id`) REFERENCES `cases`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_app_creator`  FOREIGN KEY (`created_by`)  REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: documents
-- ============================================================
CREATE TABLE `documents` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `case_id`     INT UNSIGNED DEFAULT NULL,
    `user_id`     INT UNSIGNED NOT NULL,
    `doc_type`    VARCHAR(100) NOT NULL,
    `title`       VARCHAR(255) NOT NULL,
    `file_path`   VARCHAR(500) NOT NULL,
    `file_size`   INT UNSIGNED DEFAULT NULL,
    `mime_type`   VARCHAR(100) DEFAULT NULL,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_doc_case` FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_doc_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    INDEX `idx_doc_case` (`case_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: notifications
-- ============================================================
CREATE TABLE `notifications` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`     INT UNSIGNED NOT NULL,
    `type`        VARCHAR(50)  NOT NULL,
    `title`       VARCHAR(255) NOT NULL,
    `message`     TEXT         NOT NULL,
    `link`        VARCHAR(500) DEFAULT NULL,
    `is_read`     TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_notif_user`   (`user_id`),
    INDEX `idx_notif_unread` (`user_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: audit_log
-- ============================================================
CREATE TABLE `audit_log` (
    `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`     INT UNSIGNED DEFAULT NULL,
    `action`      VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50)  NOT NULL,
    `entity_id`   INT UNSIGNED DEFAULT NULL,
    `description` TEXT         DEFAULT NULL,
    `ip_address`  VARCHAR(45)  DEFAULT NULL,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_audit_entity` (`entity_type`, `entity_id`),
    INDEX `idx_audit_date`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Seed: default admin + courts
-- ============================================================
INSERT INTO `users` (`full_name`, `email`, `password_hash`, `role`, `status`) VALUES
('مدير النظام', 'admin@legalcms.dz', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active'),
('أمين الضبط الرئيسي', 'clerk@legalcms.dz', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'clerk', 'active');

-- Default password: "password" (hashed with bcrypt)

INSERT INTO `courts` (`name`, `city`, `type`) VALUES
('المحكمة الابتدائية بتلمسان', 'تلمسان', 'ibtidai'),
('محكمة الاستئناف بتلمسان', 'تلمسان', 'istinaf'),
('المحكمة العليا', 'الجزائر', 'naqd'),
('المحكمة الابتدائية بوهران', 'وهران', 'ibtidai');
