-- ============================================================
--  مستشفى الكوثر التعليمي الاستثماري
--  نظام إدارة الصيانة (CMMS) - مخطط قاعدة البيانات
-- ============================================================

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

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

USE `cmms_hospital`;

-- ============================================================
-- 1. الأقسام
-- ============================================================
CREATE TABLE IF NOT EXISTS `departments` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(150) NOT NULL,
  `created_at` TIMESTAMP   DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. المستخدمون
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(150) NOT NULL,
  `email`         VARCHAR(200) NOT NULL UNIQUE,
  `password`      VARCHAR(255) NOT NULL,
  `role`          ENUM('employee','manager','admin') NOT NULL DEFAULT 'employee',
  `department_id` INT UNSIGNED NULL DEFAULT NULL,
  `created_at`    TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_dept`
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. طلبات الصيانة (التذاكر)
-- ============================================================
CREATE TABLE IF NOT EXISTS `tickets` (
  `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id`           INT UNSIGNED NOT NULL,
  `target_department_id`  INT UNSIGNED NOT NULL,
  `assigned_technician_id` INT UNSIGNED NULL DEFAULT NULL,
  `title`                 VARCHAR(255) NOT NULL,
  `description`           TEXT         NOT NULL,
  `priority`              ENUM('normal','urgent','critical') NOT NULL DEFAULT 'normal',
  `status`                ENUM('pending','in_progress','completed') NOT NULL DEFAULT 'pending',
  `image_path`            VARCHAR(500) NULL DEFAULT NULL,
  `created_at`            TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at`          TIMESTAMP   NULL DEFAULT NULL,
  CONSTRAINT `fk_tickets_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tickets_dept`
    FOREIGN KEY (`target_department_id`) REFERENCES `departments`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tickets_technician`
    FOREIGN KEY (`assigned_technician_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. الإشعارات
-- ============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `ticket_id`  INT UNSIGNED NOT NULL,
  `message`    VARCHAR(500) NOT NULL,
  `is_read`    TINYINT(1)  NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_notif_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_notif_ticket`
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- فهارس الأداء
-- ============================================================
CREATE INDEX `idx_tickets_status`      ON `tickets`(`status`);
CREATE INDEX `idx_tickets_priority`    ON `tickets`(`priority`);
CREATE INDEX `idx_tickets_dept`        ON `tickets`(`target_department_id`);
CREATE INDEX `idx_tickets_employee`    ON `tickets`(`employee_id`);
CREATE INDEX `idx_tickets_technician`  ON `tickets`(`assigned_technician_id`);
CREATE INDEX `idx_notif_user_read`     ON `notifications`(`user_id`, `is_read`);

-- ============================================================
-- بيانات أولية - الأقسام
-- ============================================================
INSERT INTO `departments` (`name`) VALUES
  ('كهرباء'),
  ('تبريد وتكييف'),
  ('تقنية المعلومات (IT)'),
  ('نظام معلومات المستشفى (HIS)'),
  ('سباكة'),
  ('أجهزة طبية'),
  ('صيانة عامة'),
  ('أمن وسلامة');

-- ============================================================
-- بيانات أولية - المستخدمون
-- كلمة المرور الافتراضية: Admin@1234  (مشفرة بـ password_hash)
-- ============================================================
INSERT INTO `users` (`name`, `email`, `password`, `role`, `department_id`) VALUES
  ('المدير العام',        'admin@kowsar.hospital',    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'admin',    NULL),
  ('مدير قسم الكهرباء',   'elec.mgr@kowsar.hospital', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'manager',  1),
  ('مدير تقنية المعلومات','it.mgr@kowsar.hospital',   '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'manager',  3),
  ('أحمد محمد (موظف)',    'ahmed@kowsar.hospital',    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'employee', NULL),
  ('فني كهرباء - سعيد',   'saeed@kowsar.hospital',   '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'employee', 1),
  ('فني IT - خالد',       'khalid@kowsar.hospital',   '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrIrFGXi', 'employee', 3);

-- ملاحظة: كلمة مرور جميع الحسابات الافتراضية هي  password
-- يُرجى تغييرها فور التثبيت عبر لوحة الإدارة