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

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;

-- ============================================================
-- 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)'),
  ('سباكة'),
  ('أجهزة طبية'),
  ('صيانة عامة'),
  ('أمن وسلامة');

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