91 lines
4.4 KiB
SQL
91 lines
4.4 KiB
SQL
-- 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');
|