-- Create database if not exists (Usually in phpMyAdmin you just import to an existing DB)
-- But we can add schema here for reference.

-- 1. ตารางข้อมูลแผนก (Departments)
CREATE TABLE IF NOT EXISTS `departments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `building` VARCHAR(255) DEFAULT NULL,
  `floor` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. ตารางผู้ใช้งาน / เจ้าหน้าที่ (Users)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'manager', 'staff', 'nurse') NOT NULL DEFAULT 'staff',
  `department_id` INT DEFAULT NULL,
  `status` ENUM('online', 'offline', 'busy') DEFAULT 'offline',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. ตารางอุปกรณ์เปล (Equipments)
CREATE TABLE IF NOT EXISTS `equipments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `asset_no` VARCHAR(100) NOT NULL UNIQUE,
  `type` ENUM('wheelchair', 'stretcher', 'icu_bed', 'incubator') NOT NULL,
  `status` ENUM('available', 'in_use', 'maintenance') DEFAULT 'available',
  `current_location_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`current_location_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. ตารางงานเปล (Jobs)
CREATE TABLE IF NOT EXISTS `jobs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job_number` VARCHAR(50) NOT NULL UNIQUE,
  `patient_hn` VARCHAR(50) NOT NULL,
  `patient_name` VARCHAR(255) NOT NULL,
  `from_department_id` INT NOT NULL,
  `to_department_id` INT NOT NULL,
  `equipment_type` ENUM('wheelchair', 'stretcher', 'icu_bed', 'incubator', 'none') DEFAULT 'wheelchair',
  `priority` ENUM('normal', 'urgent', 'emergency') DEFAULT 'normal',
  `status` ENUM('pending', 'assigned', 'accepted', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
  `requester_id` INT NOT NULL,
  `assigned_to` INT DEFAULT NULL,
  `equipment_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `accepted_at` DATETIME DEFAULT NULL,
  `pickup_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `notes` TEXT,
  FOREIGN KEY (`from_department_id`) REFERENCES `departments`(`id`),
  FOREIGN KEY (`to_department_id`) REFERENCES `departments`(`id`),
  FOREIGN KEY (`requester_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`),
  FOREIGN KEY (`equipment_id`) REFERENCES `equipments`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ตาราง Job Timelines (Audit Trail)
CREATE TABLE IF NOT EXISTS `job_timelines` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job_id` INT NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`job_id`) REFERENCES `jobs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Mock Data
INSERT INTO `departments` (`code`, `name`, `building`, `floor`) VALUES
('ER', 'ห้องฉุกเฉิน', 'อาคาร A', '1'),
('OPD1', 'แผนกผู้ป่วยนอก 1', 'อาคาร A', '2'),
('IPD1', 'หอผู้ป่วยใน 1', 'อาคาร B', '3'),
('XRAY', 'แผนกเอกซเรย์', 'อาคาร A', '1');

INSERT INTO `users` (`username`, `password`, `full_name`, `role`, `department_id`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin System', 'admin', NULL), -- password: password
('nurse1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Nurse Somjai', 'nurse', 1),
('staff1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Staff Somchai', 'staff', NULL);

INSERT INTO `equipments` (`asset_no`, `type`, `status`) VALUES
('WC-001', 'wheelchair', 'available'),
('ST-001', 'stretcher', 'available');