-- ============================================================
-- نظام إدارة القضايا القضائية - Legal Case Management System
-- قاعدة البيانات الكاملة
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_unicode_ci';

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

USE `legal_cms`;

-- ============================================================
-- الجدول: users (جميع المستخدمين)
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `full_name`  VARCHAR(150) NOT NULL,
  `email`      VARCHAR(100) NOT NULL UNIQUE,
  `password`   VARCHAR(255) NOT NULL,
  `role`       ENUM('admin','clerk','lawyer') NOT NULL,
  `status`     ENUM('active','inactive','pending','rejected') DEFAULT 'active',
  `phone`      VARCHAR(20) DEFAULT NULL,
  `avatar`     VARCHAR(255) DEFAULT NULL,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: lawyer_profiles (ملفات المحامين)
-- ============================================================
CREATE TABLE IF NOT EXISTS `lawyer_profiles` (
  `id`                    INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`               INT NOT NULL UNIQUE,
  `bar_number`            VARCHAR(50) DEFAULT NULL COMMENT 'رقم التسجيل بنقابة المحامين',
  `bar_region`            VARCHAR(100) DEFAULT NULL COMMENT 'مجلس قضاء الانتساب',
  `specialization`        VARCHAR(150) DEFAULT NULL,
  `address`               TEXT DEFAULT NULL,
  `professional_card_path` VARCHAR(255) DEFAULT NULL COMMENT 'البطاقة المهنية',
  `accreditation_path`    VARCHAR(255) DEFAULT NULL COMMENT 'شهادة الاعتماد',
  `id_card_path`          VARCHAR(255) DEFAULT NULL COMMENT 'بطاقة الهوية',
  `verification_status`   ENUM('pending_upload','under_review','approved','rejected') DEFAULT 'pending_upload',
  `rejection_reason`      TEXT DEFAULT NULL,
  `reviewed_by`           INT DEFAULT NULL,
  `reviewed_at`           TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: cases (القضايا)
-- ============================================================
CREATE TABLE IF NOT EXISTS `cases` (
  `id`                   INT AUTO_INCREMENT PRIMARY KEY,
  `case_number`          VARCHAR(60) NOT NULL UNIQUE COMMENT 'رقم الملف',
  `title`                VARCHAR(255) NOT NULL,
  `description`          TEXT DEFAULT NULL,
  `case_type`            ENUM('civil','criminal','commercial','administrative','family') NOT NULL,
  `stage`                ENUM('opening','first_instance','appeal','cassation','final') DEFAULT 'first_instance',
  `status`               ENUM('active','closed','suspended') DEFAULT 'active',
  `plaintiff`            VARCHAR(150) DEFAULT NULL COMMENT 'المدعي',
  `defendant`            VARCHAR(150) DEFAULT NULL COMMENT 'المدعى عليه',
  `plaintiff_lawyer_id`  INT DEFAULT NULL,
  `defendant_lawyer_id`  INT DEFAULT NULL,
  `assigned_clerk_id`    INT NOT NULL,
  `court_name`           VARCHAR(150) DEFAULT NULL,
  `court_type`           ENUM('primary','appeal','supreme') DEFAULT 'primary',
  `filing_date`          DATE DEFAULT NULL COMMENT 'تاريخ إيداع القضية',
  `closed_at`            TIMESTAMP NULL DEFAULT NULL,
  `created_at`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`plaintiff_lawyer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`defendant_lawyer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_clerk_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: sessions (الجلسات)
-- ============================================================
CREATE TABLE IF NOT EXISTS `sessions` (
  `id`               INT AUTO_INCREMENT PRIMARY KEY,
  `case_id`          INT NOT NULL,
  `session_number`   INT NOT NULL DEFAULT 1,
  `session_date`     DATE NOT NULL,
  `session_time`     TIME NOT NULL DEFAULT '09:00:00',
  `room`             VARCHAR(50) DEFAULT NULL COMMENT 'قاعة الجلسة',
  `judge_name`       VARCHAR(100) DEFAULT NULL COMMENT 'اسم القاضي',
  `prosecutor_name`  VARCHAR(100) DEFAULT NULL COMMENT 'ممثل النيابة',
  `status`           ENUM('scheduled','held','postponed','cancelled') DEFAULT 'scheduled',
  `decision_type`    ENUM('pending','postponed','verdict','referral') DEFAULT 'pending',
  `next_session_date` DATE DEFAULT NULL,
  `postpone_reason`  TEXT DEFAULT NULL,
  `verdict_text`     TEXT DEFAULT NULL COMMENT 'منطوق الحكم',
  `verdict_type`     ENUM('conviction','acquittal','dismissal','referral') DEFAULT NULL,
  `created_by`       INT NOT NULL,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: session_minutes (محاضر الجلسات / PV)
-- ============================================================
CREATE TABLE IF NOT EXISTS `session_minutes` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `session_id`  INT NOT NULL UNIQUE,
  `content`     TEXT NOT NULL COMMENT 'نص المحضر',
  `notes`       TEXT DEFAULT NULL,
  `written_by`  INT NOT NULL,
  `written_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`written_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: session_attendance (حضور الجلسة)
-- ============================================================
CREATE TABLE IF NOT EXISTS `session_attendance` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `session_id`   INT NOT NULL,
  `person_name`  VARCHAR(150) NOT NULL,
  `person_type`  ENUM('lawyer','defendant','plaintiff','witness','expert','other') NOT NULL,
  `user_id`      INT DEFAULT NULL,
  `present`      TINYINT(1) DEFAULT 1,
  `notes`        VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: appeals (الاستئناف والنقض)
-- ============================================================
CREATE TABLE IF NOT EXISTS `appeals` (
  `id`                         INT AUTO_INCREMENT PRIMARY KEY,
  `original_case_id`           INT NOT NULL,
  `original_session_id`        INT NOT NULL COMMENT 'الجلسة التي صدر فيها الحكم',
  `appeal_type`                ENUM('appeal','cassation') NOT NULL,
  `filed_by`                   INT NOT NULL,
  `filing_date`                DATE NOT NULL,
  `appeal_reason`              TEXT NOT NULL,
  `status`                     ENUM('filed','accepted','rejected','under_review') DEFAULT 'filed',
  `new_case_id`                INT DEFAULT NULL COMMENT 'قضية الاستئناف الجديدة',
  `clerk_notes`                TEXT DEFAULT NULL,
  `created_at`                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`original_case_id`) REFERENCES `cases`(`id`),
  FOREIGN KEY (`original_session_id`) REFERENCES `sessions`(`id`),
  FOREIGN KEY (`filed_by`) REFERENCES `users`(`id`),
  FOREIGN KEY (`new_case_id`) REFERENCES `cases`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: notifications (الإشعارات)
-- ============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`         INT NOT NULL,
  `title`           VARCHAR(200) NOT NULL,
  `message`         TEXT NOT NULL,
  `type`            ENUM('info','success','warning','danger') DEFAULT 'info',
  `is_read`         TINYINT(1) DEFAULT 0,
  `related_case_id` INT DEFAULT NULL,
  `action_url`      VARCHAR(255) DEFAULT NULL,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`related_case_id`) REFERENCES `cases`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- الجدول: case_documents (وثائق القضايا)
-- ============================================================
CREATE TABLE IF NOT EXISTS `case_documents` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `case_id`      INT DEFAULT NULL,
  `session_id`   INT DEFAULT NULL,
  `uploaded_by`  INT NOT NULL,
  `doc_type`     VARCHAR(100) DEFAULT NULL,
  `doc_name`     VARCHAR(255) NOT NULL,
  `file_path`    VARCHAR(255) NOT NULL,
  `file_size`    INT DEFAULT NULL,
  `uploaded_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- البيانات الأولية - Default Data
-- ============================================================

-- Admin user (password: Admin@1234)
INSERT INTO `users` (`full_name`, `email`, `password`, `role`, `status`) VALUES
('مدير النظام', 'admin@legal.dz', '$2y$10$TKh8H1.PfKBLV9KoSJI8MuApD5wDjWPlEKkwwlJnbC7j3v0bWF7SC', 'admin', 'active');

-- Clerk user (password: Clerk@1234)
INSERT INTO `users` (`full_name`, `email`, `password`, `role`, `status`) VALUES
('أمين الضبط الرئيسي', 'clerk@legal.dz', '$2y$10$TKh8H1.PfKBLV9KoSJI8MuApD5wDjWPlEKkwwlJnbC7j3v0bWF7SC', 'clerk', 'active');

-- Note: The hashed passwords above use password_hash('Admin@1234', PASSWORD_DEFAULT)
-- Run the install script to auto-generate correct hashes, or update manually via:
-- UPDATE users SET password = PASSWORD_HASH_VALUE WHERE email = 'admin@legal.dz';
