-- ============================================================
-- migration_2026_07_bars_complaints.sql
-- نظام "قضيتي" — ترحيل إضافة الميزات الجديدة:
--   1) الهيكل التنظيمي: مجالس قضائية + نقابات المحامين (تبدأ بنقابة البليدة)
--   2) ربط المحاكم بالمجالس القضائية
--   3) نظام شكاوي كامل للمنصة
--
-- هذا الملف "إضافي وآمن" (additive): يستعمل IF NOT EXISTS ما أمكن
-- بحيث يمكن تنفيذه على قاعدة بيانات تعمل بالفعل بدون كسر أي شيء.
-- شغّله عبر: SOURCE database/migration_2026_07_bars_complaints.sql;
--
-- ملاحظة: إن كانت نسخة MySQL/MariaDB لديك لا تدعم
-- "ADD COLUMN IF NOT EXISTS"، احذف "IF NOT EXISTS" من أسطر ALTER
-- وتحقق يدوياً من عدم وجود العمود مسبقاً.
-- ============================================================

SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- fallback: جدول المحاكم (فقط إذا لم يكن موجوداً على الإطلاق)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `courts` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`        VARCHAR(200) NOT NULL,
    `court_code`  VARCHAR(20)  DEFAULT NULL,
    `branch`      ENUM('ordinary','administrative') NOT NULL DEFAULT 'ordinary',
    `level`       VARCHAR(30)  NOT NULL,
    `wilaya_code` VARCHAR(5)   DEFAULT NULL,
    `daira`       VARCHAR(100) DEFAULT NULL,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- جدول: bar_associations (نقابات المحامين)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bar_associations` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`        VARCHAR(150) NOT NULL,          -- مثال: نقابة البليدة
    `seat_wilaya_code` VARCHAR(5) DEFAULT NULL,    -- ولاية مقر النقابة
    `phone`       VARCHAR(30)  DEFAULT NULL,
    `address`     VARCHAR(255) DEFAULT NULL,
    `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_bar_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- جدول: judicial_councils (مجالس القضاء)
-- كل مجلس قضاء ينتمي إلى نقابة محامين واحدة، وله عدة محاكم تابعة له
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `judicial_councils` (
    `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`               VARCHAR(150) NOT NULL,        -- مثال: مجلس قضاء البليدة
    `wilaya_code`        VARCHAR(5)   DEFAULT NULL,
    `bar_association_id` INT UNSIGNED DEFAULT NULL,
    `is_complete`        TINYINT(1)   NOT NULL DEFAULT 0,  -- تم استكمال بيانات محاكمه أم لا
    `created_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_council_bar` FOREIGN KEY (`bar_association_id`) REFERENCES `bar_associations`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `uq_council_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- ربط جدول courts الحالي بالمجلس القضائي التابع له
-- ------------------------------------------------------------
ALTER TABLE `courts`
    ADD COLUMN IF NOT EXISTS `council_id` INT UNSIGNED DEFAULT NULL AFTER `wilaya_code`,
    ADD COLUMN IF NOT EXISTS `is_seat`    TINYINT(1)   NOT NULL DEFAULT 0 AFTER `council_id`;

-- إضافة القيد الأجنبي فقط إن لم يكن موجوداً (يُتجاهل الخطأ يدوياً إن كان مكرراً)
ALTER TABLE `courts`
    ADD CONSTRAINT `fk_court_council` FOREIGN KEY (`council_id`) REFERENCES `judicial_councils`(`id`) ON DELETE SET NULL;

-- ------------------------------------------------------------
-- ربط ملف المحامي بالنقابة المهيكلة (مع إبقاء الحقل النصي القديم كما هو لأجل التوافق)
-- ------------------------------------------------------------
ALTER TABLE `lawyer_profiles`
    ADD COLUMN IF NOT EXISTS `bar_association_id` INT UNSIGNED DEFAULT NULL AFTER `bar_association`;

ALTER TABLE `lawyer_profiles`
    ADD CONSTRAINT `fk_lp_bar` FOREIGN KEY (`bar_association_id`) REFERENCES `bar_associations`(`id`) ON DELETE SET NULL;

-- ------------------------------------------------------------
-- fallback: جدول طلبات تعديل الملف الحساسة (إن لم يكن موجوداً بالفعل)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `profile_change_requests` (
    `id`                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`                INT UNSIGNED NOT NULL,
    `new_bar_number`         VARCHAR(50)  DEFAULT NULL,
    `new_bar_association`    VARCHAR(150) DEFAULT NULL,
    `new_bar_association_id` INT UNSIGNED DEFAULT NULL,
    `new_specialization`     VARCHAR(150) DEFAULT NULL,
    `new_professional_card`  VARCHAR(255) DEFAULT NULL,
    `new_accreditation_doc`  VARCHAR(255) DEFAULT NULL,
    `new_id_card`            VARCHAR(255) DEFAULT NULL,
    `status`                 ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `rejection_reason`       TEXT DEFAULT NULL,
    `reviewed_by`            INT UNSIGNED DEFAULT NULL,
    `reviewed_at`            DATETIME DEFAULT NULL,
    `created_at`             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_pcr_user`     FOREIGN KEY (`user_id`)     REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_pcr_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_pcr_bar`      FOREIGN KEY (`new_bar_association_id`) REFERENCES `bar_associations`(`id`) ON DELETE SET NULL,
    INDEX `idx_pcr_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- في حال كان الجدول موجوداً مسبقاً بدون هذا العمود
ALTER TABLE `profile_change_requests`
    ADD COLUMN IF NOT EXISTS `new_bar_association_id` INT UNSIGNED DEFAULT NULL AFTER `new_bar_association`;

-- ------------------------------------------------------------
-- نظام الشكاوي — جدول complaints
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `complaints` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `complaint_number` VARCHAR(30)  NOT NULL UNIQUE,      -- SHK-2026-0001
    `user_id`          INT UNSIGNED NOT NULL,             -- صاحب الشكوى
    `user_role`        ENUM('admin','clerk','lawyer') NOT NULL,
    `category`         VARCHAR(50)  NOT NULL,             -- انظر COMPLAINT_CATEGORIES
    `subject`          VARCHAR(255) NOT NULL,
    `description`      TEXT NOT NULL,
    `related_case_id`  INT UNSIGNED DEFAULT NULL,
    `priority`         ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status`           ENUM('new','in_review','resolved','rejected','closed') NOT NULL DEFAULT 'new',
    `assigned_to`      INT UNSIGNED DEFAULT NULL,         -- مدير مكلّف بالمتابعة
    `resolution_note`  TEXT DEFAULT NULL,
    `resolved_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_cmp_user`     FOREIGN KEY (`user_id`)         REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cmp_case`     FOREIGN KEY (`related_case_id`) REFERENCES `cases`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_cmp_admin`    FOREIGN KEY (`assigned_to`)     REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_cmp_status`   (`status`),
    INDEX `idx_cmp_user`     (`user_id`),
    INDEX `idx_cmp_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- نظام الشكاوي — سجل الردود/المراسلات على كل شكوى (Thread)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `complaint_messages` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `complaint_id`   INT UNSIGNED NOT NULL,
    `sender_id`      INT UNSIGNED NOT NULL,
    `sender_role`    ENUM('admin','clerk','lawyer') NOT NULL,
    `message`        TEXT NOT NULL,
    `is_internal`    TINYINT(1) NOT NULL DEFAULT 0,   -- ملاحظة داخلية بين الإدارة فقط، لا يراها صاحب الشكوى
    `attachment_path` VARCHAR(255) DEFAULT NULL,
    `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cmsg_complaint` FOREIGN KEY (`complaint_id`) REFERENCES `complaints`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cmsg_sender`    FOREIGN KEY (`sender_id`)    REFERENCES `users`(`id`),
    INDEX `idx_cmsg_complaint` (`complaint_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- بيانات أولية: نقابة البليدة (عيّنة) — 4 مجالس قضائية ومحاكمها
-- ============================================================

INSERT IGNORE INTO `bar_associations` (`name`, `seat_wilaya_code`) VALUES
('نقابة البليدة', '09');

SET @bar_blida := (SELECT id FROM `bar_associations` WHERE name = 'نقابة البليدة' LIMIT 1);

-- [1] مجلس قضاء الشلف (غير مكتمل — أُدرجت محاكمه المذكورة فقط)
INSERT IGNORE INTO `judicial_councils` (`name`, `wilaya_code`, `bar_association_id`, `is_complete`) VALUES
('مجلس قضاء الشلف', '02', @bar_blida, 0);
SET @council_chlef := (SELECT id FROM `judicial_councils` WHERE name = 'مجلس قضاء الشلف' LIMIT 1);

INSERT IGNORE INTO `courts` (`name`, `branch`, `level`, `wilaya_code`, `council_id`, `is_seat`) VALUES
('محكمة الشلف',    'ordinary', 'ibtidaiya', '02', @council_chlef, 1),
('محكمة تنس',      'ordinary', 'ibtidaiya', '02', @council_chlef, 0),
('محكمة بوقادير',  'ordinary', 'ibtidaiya', '02', @council_chlef, 0),
('محكمة عين مران', 'ordinary', 'ibtidaiya', '02', @council_chlef, 0);

-- [2] مجلس قضاء عين الدفلى (غير مكتمل)
INSERT IGNORE INTO `judicial_councils` (`name`, `wilaya_code`, `bar_association_id`, `is_complete`) VALUES
('مجلس قضاء عين الدفلى', '44', @bar_blida, 0);
SET @council_aindefla := (SELECT id FROM `judicial_councils` WHERE name = 'مجلس قضاء عين الدفلى' LIMIT 1);

INSERT IGNORE INTO `courts` (`name`, `branch`, `level`, `wilaya_code`, `council_id`, `is_seat`) VALUES
('محكمة مليانة',   'ordinary', 'ibtidaiya', '44', @council_aindefla, 1),
('محكمة خميس مليانة (ملحقة محكمة مليانة)', 'ordinary', 'ibtidaiya', '44', @council_aindefla, 0),
('محكمة العطاف',   'ordinary', 'ibtidaiya', '44', @council_aindefla, 0),
('محكمة بومدفع',   'ordinary', 'ibtidaiya', '44', @council_aindefla, 0),
('محكمة العبادية', 'ordinary', 'ibtidaiya', '44', @council_aindefla, 0);

-- [3] مجلس قضاء تيبازة (المكتمل)
INSERT IGNORE INTO `judicial_councils` (`name`, `wilaya_code`, `bar_association_id`, `is_complete`) VALUES
('مجلس قضاء تيبازة', '42', @bar_blida, 1);
SET @council_tipaza := (SELECT id FROM `judicial_councils` WHERE name = 'مجلس قضاء تيبازة' LIMIT 1);

INSERT IGNORE INTO `courts` (`name`, `branch`, `level`, `wilaya_code`, `council_id`, `is_seat`) VALUES
('محكمة تيبازة', 'ordinary', 'ibtidaiya', '42', @council_tipaza, 1),
('محكمة شرشال',  'ordinary', 'ibtidaiya', '42', @council_tipaza, 0),
('محكمة حجوط',   'ordinary', 'ibtidaiya', '42', @council_tipaza, 0),
('محكمة القليعة','ordinary', 'ibtidaiya', '42', @council_tipaza, 0);

-- [4] مجلس قضاء البليدة (المكتمل)
INSERT IGNORE INTO `judicial_councils` (`name`, `wilaya_code`, `bar_association_id`, `is_complete`) VALUES
('مجلس قضاء البليدة', '09', @bar_blida, 1);
SET @council_blida := (SELECT id FROM `judicial_councils` WHERE name = 'مجلس قضاء البليدة' LIMIT 1);

INSERT IGNORE INTO `courts` (`name`, `branch`, `level`, `wilaya_code`, `council_id`, `is_seat`) VALUES
('محكمة البليدة',   'ordinary', 'ibtidaiya', '09', @council_blida, 1),
('محكمة بوفاريك',   'ordinary', 'ibtidaiya', '09', @council_blida, 0),
('محكمة العفرون',   'ordinary', 'ibtidaiya', '09', @council_blida, 0),
('محكمة الأربعاء',  'ordinary', 'ibtidaiya', '09', @council_blida, 0);

-- كل مجلس قضاء هو أيضاً جهة استئناف (majlis) لمحاكمه الابتدائية
INSERT IGNORE INTO `courts` (`name`, `branch`, `level`, `wilaya_code`, `council_id`, `is_seat`) VALUES
('مجلس قضاء الشلف (استئناف)',      'ordinary', 'majlis', '02', @council_chlef,    1),
('مجلس قضاء عين الدفلى (استئناف)', 'ordinary', 'majlis', '44', @council_aindefla, 1),
('مجلس قضاء تيبازة (استئناف)',     'ordinary', 'majlis', '42', @council_tipaza,   1),
('مجلس قضاء البليدة (استئناف)',    'ordinary', 'majlis', '09', @council_blida,    1);
