gravity/บริหารจัดการคิวนวดแผนไทย/database/schema.sql

-- ============================================================================
-- Thai Traditional Massage Queue Management System (TTMQMS)
-- Enterprise Production Database Schema (MySQL 8.0)
-- Architecture: 3NF Normalization, ACID Compliant, High Concurrency Ready
-- Character Set: utf8mb4, Collation: utf8mb4_unicode_ci
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. ตารางสาขาและหน่วยบริการ (Branches / Multi-Clinic Support)
DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) NOT NULL COMMENT 'รหัสสาขา เช่น BR-001',
  `name_th` VARCHAR(150) NOT NULL COMMENT 'ชื่อสาขา (ไทย)',
  `name_en` VARCHAR(150) NOT NULL COMMENT 'ชื่อสาขา (อังกฤษ)',
  `address` TEXT NOT NULL COMMENT 'ที่อยู่สาขา',
  `phone` VARCHAR(50) NOT NULL COMMENT 'เบอร์โทรศัพท์ติดต่อ',
  `tax_id` VARCHAR(20) NULL COMMENT 'เลขประจำตัวผู้เสียภาษีสาขา',
  `is_main` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = สำนักงานใหญ่/สาขาหลัก',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = เปิดให้บริการ, 0 = ปิด',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_branch_code` (`code`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางข้อมูลสาขาและหน่วยบริการการแพทย์แผนไทย';

-- 2. ตารางผู้ใช้งานระบบและบุคลากร (Users & Staff - Argon2id & 2FA Ready)
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `national_id` VARCHAR(13) NOT NULL COMMENT 'เลขบัตรประชาชน 13 หลัก (ใช้เป็น Username ในการ Login)',
  `password_hash` VARCHAR(255) NOT NULL COMMENT 'รหัสผ่านที่ผ่านการเข้ารหัสด้วย Argon2id',
  `email` VARCHAR(100) NULL COMMENT 'อีเมลติดต่อ',
  `phone` VARCHAR(20) NULL COMMENT 'เบอร์โทรศัพท์',
  `title` VARCHAR(50) NULL COMMENT 'คำนำหน้าชื่อ (นาย/นาง/นางสาว/นพ./พญ./ผช.)',
  `first_name` VARCHAR(100) NOT NULL COMMENT 'ชื่อจริง',
  `last_name` VARCHAR(100) NOT NULL COMMENT 'นามสกุล',
  `role` ENUM('Admin','Manager','Reception','Therapist','Cashier','Doctor','Auditor','Patient') NOT NULL DEFAULT 'Patient' COMMENT 'บทบาทผู้ใช้งานระบบ',
  `branch_id` BIGINT UNSIGNED NULL COMMENT 'สังกัดสาขาหลัก (NULL ถ้าเป็นส่วนกลาง/Patient)',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'สถานะบัญชี 1=ปกติ, 0=ระงับ',
  `two_factor_secret` VARCHAR(255) NULL COMMENT 'Google Authenticator TOTP Secret Key (RFC6238)',
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'สถานะ 2FA: 1=เปิดใช้งาน, 0=ปิด',
  `two_factor_recovery_codes` JSON NULL COMMENT 'รหัสสำรองกู้คืน 2FA (Recovery Codes ในรูป JSON Array)',
  `remember_token` VARCHAR(100) NULL COMMENT 'Token สำหรับ Remember Device 30 วัน',
  `failed_login_attempts` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'จำนวนครั้งที่ล็อกอินผิดพลาด (Brute Force Guard)',
  `locked_until` DATETIME NULL COMMENT 'เวลาสิ้นสุดการระงับบัญชีชั่วคราวเมื่อล็อกอินผิดเกินกำหนด',
  `last_login_at` DATETIME NULL COMMENT 'เวลาที่ล็อกอินสำเร็จล่าสุด',
  `last_login_ip` VARCHAR(45) NULL COMMENT 'IP Address ที่ล็อกอินล่าสุด (รองรับ IPv4/IPv6)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_national_id` (`national_id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_role_branch` (`role`, `branch_id`),
  KEY `idx_status_locked` (`is_active`, `locked_until`),
  CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางผู้ใช้งานระบบทุกระดับ (Admin ถึง Patient) พร้อมความปลอดภัย Argon2id และ 2FA';

-- 3. ตารางข้อมูลผู้รับบริการ / ผู้ป่วย (Patients - HIS / HOSxP & Smart Card Ready)
DROP TABLE IF EXISTS `patients`;
CREATE TABLE `patients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL COMMENT 'เชื่อมโยงกับตาราง users (กรณีผู้ป่วยมีบัญชีเข้าใช้งาน PWA)',
  `cid` VARCHAR(13) NOT NULL COMMENT 'เลขบัตรประชาชน 13 หลัก จากการอ่าน Smart Card หรือกรอกมือ',
  `hn` VARCHAR(20) NOT NULL COMMENT 'Hospital Number (HN) เชื่อมโยงระบบ HIS/HOSxP ของโรงพยาบาล',
  `first_name_th` VARCHAR(100) NOT NULL COMMENT 'ชื่อจริง (ภาษาไทย)',
  `last_name_th` VARCHAR(100) NOT NULL COMMENT 'นามสกุล (ภาษาไทย)',
  `first_name_en` VARCHAR(100) NULL COMMENT 'ชื่อจริง (ภาษาอังกฤษ)',
  `last_name_en` VARCHAR(100) NULL COMMENT 'นามสกุล (ภาษาอังกฤษ)',
  `birth_date` DATE NOT NULL COMMENT 'วันเดือนปีเกิด',
  `gender` ENUM('Male','Female','Other') NOT NULL COMMENT 'เพศ',
  `blood_group` VARCHAR(10) NULL COMMENT 'กรุ๊ปเลือด (A, B, AB, O)',
  `address` TEXT NULL COMMENT 'ที่อยู่ปัจจุบัน / ตามทะเบียนบ้าน',
  `phone` VARCHAR(20) NOT NULL COMMENT 'เบอร์โทรศัพท์มือถือที่ติดต่อได้',
  `line_id` VARCHAR(100) NULL COMMENT 'LINE ID หรือ LINE User ID สำหรับรับแจ้งเตือน LINE OA',
  `email` VARCHAR(100) NULL COMMENT 'อีเมล',
  `underlying_diseases` TEXT NULL COMMENT 'โรคประจำตัว (เช่น ความดันโลหิตสูง, เบาหวาน, โรคหัวใจ)',
  `massage_contraindications` TEXT NULL COMMENT 'ข้อห้าม/ข้อควรระวังในการนวด (เช่น กระดูกทับเส้น, ผ่าตัดหลัง, ตั้งครรภ์)',
  `drug_allergies` TEXT NULL COMMENT 'ประวัติการแพ้ยาและสมุนไพร',
  `emergency_contact_name` VARCHAR(150) NULL COMMENT 'ชื่อผู้ติดต่อกรณีฉุกเฉิน',
  `emergency_contact_phone` VARCHAR(20) NULL COMMENT 'เบอร์โทรผู้ติดต่อกรณีฉุกเฉิน',
  `photo_url` VARCHAR(255) NULL COMMENT 'ที่เก็บไฟล์รูปภาพใบหน้าผู้ป่วย / รูปจากบัตรประชาชน',
  `his_synced_at` DATETIME NULL COMMENT 'เวลาที่เชื่อมต่อข้อมูลซิงค์จากระบบ HIS/HOSxP ล่าสุด',
  `his_raw_data` JSON NULL COMMENT 'ข้อมูลดิบ JSON จาก HIS/HOSxP / HL7 FHIR Patient Resource',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_patient_cid` (`cid`),
  UNIQUE KEY `uk_patient_hn` (`hn`),
  KEY `idx_name_phone` (`first_name_th`, `last_name_th`, `phone`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_patients_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางประวัติผู้ป่วยและผู้รับบริการนวดแผนไทย รองรับการซิงค์ HIS/HOSxP และ Smart Card';

-- 4. ตารางข้อมูลหมอนวดและการประเมินภาระงาน (Therapists - Smart Workload & Skills)
DROP TABLE IF EXISTS `therapists`;
CREATE TABLE `therapists` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT 'รหัสผู้ใช้งานจากตาราง users',
  `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'ประจำอยู่สาขาหลัก',
  `license_no` VARCHAR(50) NOT NULL COMMENT 'เลขที่ใบประกอบวิชาชีพการแพทย์แผนไทย / ใบรับรอง',
  `specialization` VARCHAR(150) NULL COMMENT 'ความเชี่ยวชาญพิเศษ เช่น นวดรักษาโรค, ออฟฟิศซินโดรม, นวดหญิงตั้งครรภ์',
  `skills` JSON NOT NULL COMMENT 'รายการทักษะที่ทำได้ในรูป JSON Array เช่น ["นวดไทย", "นวดฝ่าเท้า", "ประคบสมุนไพร", "น้ำมัน", "Office Syndrome", "กดจุด"]',
  `working_days` JSON NOT NULL COMMENT 'วันทำงานประจำสัปดาห์ เช่น [1,2,3,4,5] (1=จันทร์)',
  `shift_start` TIME NOT NULL DEFAULT '08:30:00' COMMENT 'เวลาเข้างาน',
  `shift_end` TIME NOT NULL DEFAULT '17:30:00' COMMENT 'เวลาเลิกงาน',
  `max_daily_minutes` INT UNSIGNED NOT NULL DEFAULT 480 COMMENT 'ชั่วโมงนวดสูงสุดต่อวัน (นาที) เพื่อป้องกันความเหนื่อยล้า',
  `current_workload_score` DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT 'คะแนนภาระงานสะสมในวันนี้ (ใช้ในการเลือกหมอนวดอัตโนมัติของ Smart Queue AI)',
  `is_available` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'สถานะความพร้อมปัจจุบัน 1=ว่างรับงาน, 0=ติดคิว/พัก/หยุด',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'สถานะการทำงาน 1=ปฏิบัติงาน, 0=ลาออก/พักงาน',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_therapist_user` (`user_id`),
  UNIQUE KEY `uk_license_no` (`license_no`),
  KEY `idx_branch_available` (`branch_id`, `is_available`, `is_active`),
  KEY `idx_workload` (`current_workload_score`),
  CONSTRAINT `fk_therapists_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_therapists_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางหมอนวดและแพทย์แผนไทย พร้อมข้อมูลทักษะและคะแนนภาระงานสำหรับ Smart Queue';

-- 5. ตารางบริการ แพ็กเกจ และโปรโมชั่น (Services & Packages)
DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL COMMENT 'สาขาที่ให้บริการ (NULL = ให้บริการทุกสาขา)',
  `code` VARCHAR(50) NOT NULL COMMENT 'รหัสบริการ เช่น SVC-TH60, SVC-OIL90',
  `name_th` VARCHAR(150) NOT NULL COMMENT 'ชื่อบริการนวด (ไทย)',
  `name_en` VARCHAR(150) NOT NULL COMMENT 'ชื่อบริการนวด (อังกฤษ)',
  `category` ENUM('Thai_Massage','Foot_Massage','Herbal_Compress','Oil_Massage','Office_Syndrome','Acupressure','Course_Package','Consultation') NOT NULL DEFAULT 'Thai_Massage' COMMENT 'หมวดหมู่บริการ',
  `description` TEXT NULL COMMENT 'รายละเอียดและขั้นตอนการให้บริการ',
  `duration_minutes` INT UNSIGNED NOT NULL COMMENT 'ระยะเวลาที่ใช้ในการให้บริการ (นาที) เช่น 60, 90, 120',
  `price` DECIMAL(10,2) NOT NULL COMMENT 'ราคามาตรฐาน (บาท)',
  `promo_price` DECIMAL(10,2) NULL COMMENT 'ราคาโปรโมชั่น (ถ้ามี)',
  `is_package` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=เป็นแพ็กเกจคอร์สรวมหลายบริการ, 0=บริการเดี่ยว',
  `package_items` JSON NULL COMMENT 'รายการบริการย่อยในแพ็กเกจในรูป JSON',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'สถานะ 1=เปิดให้บริการ, 0=งดให้บริการ',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_service_code` (`code`),
  KEY `idx_category_active` (`category`, `is_active`),
  KEY `idx_branch_service` (`branch_id`),
  CONSTRAINT `fk_services_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางรายการบริการนวดแผนไทย ราคา ระยะเวลา และแพ็กเกจ';

-- 6. ตารางห้องนวดและสถานะ (Rooms Management)
DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'ประจำสาขา',
  `room_no` VARCHAR(20) NOT NULL COMMENT 'หมายเลขห้อง เช่น R101, VIP-01',
  `name` VARCHAR(100) NOT NULL COMMENT 'ชื่อเรียกห้อง เช่น ห้องนวดไทยรวม A, ห้องสปา VIP',
  `room_type` ENUM('Normal','VIP','Thai_Bed','Oil_Bed','Herbal_Steam') NOT NULL DEFAULT 'Normal' COMMENT 'ประเภทห้อง',
  `capacity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'ความจุเตียง/ผู้รับบริการพร้อมกันในห้อง',
  `status` ENUM('Available','Occupied','Cleaning','Maintenance') NOT NULL DEFAULT 'Available' COMMENT 'สถานะห้องแบบ Realtime',
  `current_queue_id` BIGINT UNSIGNED NULL COMMENT 'รหัสคิวที่กำลังใช้บริการในห้องนี้ในปัจจุบัน',
  `notes` TEXT NULL COMMENT 'หมายเหตุ เช่น แอร์เย็น, เตียงปรับไฟฟ้า',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=เปิดใช้งาน, 0=ปิดซ่อมแซม',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_room_branch_no` (`branch_id`, `room_no`),
  KEY `idx_room_status` (`status`, `is_active`),
  CONSTRAINT `fk_rooms_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางจัดการห้องนวดและสถานะความพร้อมแบบ Realtime';

-- 7. ตารางระบบคิวหลัก (Queues - Walk-in, Appointment, Online & Smart AI Queue)
DROP TABLE IF EXISTS `queues`;
CREATE TABLE `queues` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'สาขาที่รับบริการ',
  `queue_no` VARCHAR(20) NOT NULL COMMENT 'หมายเลขคิว เช่น A001 (ปกติ), V001 (VIP), E001 (ฉุกเฉิน)',
  `queue_date` DATE NOT NULL COMMENT 'วันที่รับบริการ',
  `patient_id` BIGINT UNSIGNED NOT NULL COMMENT 'ผู้รับบริการ (ตาราง patients)',
  `therapist_id` BIGINT UNSIGNED NULL COMMENT 'หมอนวดที่ได้รับมอบหมาย (NULL หากอยู่ระหว่างรอคิว AI จัดสรร)',
  `service_id` BIGINT UNSIGNED NOT NULL COMMENT 'บริการที่เลือก',
  `room_id` BIGINT UNSIGNED NULL COMMENT 'ห้องนวดที่จัดสรรให้',
  `booking_type` ENUM('Walk_in','Appointment','Online') NOT NULL DEFAULT 'Walk_in' COMMENT 'ช่องทางการมาใช้บริการ',
  `priority` ENUM('Normal','VIP','Emergency') NOT NULL DEFAULT 'Normal' COMMENT 'ลำดับความสำคัญของคิว',
  `status` ENUM('Waiting','Assigned','In_Progress','Completed','Cancelled','No_Show') NOT NULL DEFAULT 'Waiting' COMMENT 'สถานะคิวปัจจุบัน',
  `checkin_time` DATETIME NOT NULL COMMENT 'เวลาที่ออกใบคิว / เช็คอินหน้าเคาน์เตอร์',
  `est_start_time` DATETIME NULL COMMENT 'เวลาที่คาดว่าจะได้เริ่มบริการ (AI Estimate)',
  `est_end_time` DATETIME NULL COMMENT 'เวลาที่คาดว่าจะเสร็จสิ้น (AI Estimate)',
  `actual_start_time` DATETIME NULL COMMENT 'เวลาที่เริ่มเข้าห้องนวดจริง',
  `actual_end_time` DATETIME NULL COMMENT 'เวลาที่ให้บริการเสร็จสิ้นจริง',
  `wait_time_mins` INT UNSIGNED NULL COMMENT 'เวลารอคิวจริง (นาที) ตั้งแต่ checkin ถึง actual_start',
  `service_time_mins` INT UNSIGNED NULL COMMENT 'เวลาที่ใช้บริการจริง (นาที)',
  `cancel_reason` VARCHAR(255) NULL COMMENT 'เหตุผลที่ยกเลิกคิว / ไม่มาตามนัด',
  `booking_reference` VARCHAR(100) NULL COMMENT 'เลขอ้างอิงการจองออนไลน์ / Google Calendar Event ID / Outlook ICS ID',
  `created_by` BIGINT UNSIGNED NOT NULL COMMENT 'พนักงานผู้บันทึกคิว (จากตาราง users)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_queue_date_branch` (`queue_date`, `branch_id`, `status`),
  KEY `idx_queue_patient` (`patient_id`),
  KEY `idx_queue_therapist` (`therapist_id`, `queue_date`),
  KEY `idx_queue_status_priority` (`status`, `priority`),
  CONSTRAINT `fk_queues_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_queues_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_queues_therapist` FOREIGN KEY (`therapist_id`) REFERENCES `therapists` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_queues_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_queues_room` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_queues_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางระบบคิวหลัก รองรับการคำนวณเวลาอัตโนมัติ AI Estimate และ Priority Queue';

-- เพิ่ม FK กลับไปยัง rooms (current_queue_id) หลังจากสร้างตาราง queues แล้ว
ALTER TABLE `rooms` ADD CONSTRAINT `fk_rooms_current_queue` FOREIGN KEY (`current_queue_id`) REFERENCES `queues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- 8. ตารางบันทึกการรักษาและเวชระเบียนคลินิก (SOAP Notes, VAS Pain Score, ROM & Digital Signature)
DROP TABLE IF EXISTS `soap_notes`;
CREATE TABLE `soap_notes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue_id` BIGINT UNSIGNED NOT NULL COMMENT 'อ้างอิงจากคิวการรับบริการในครั้งนั้น',
  `patient_id` BIGINT UNSIGNED NOT NULL COMMENT 'ผู้ป่วย',
  `therapist_id` BIGINT UNSIGNED NOT NULL COMMENT 'หมอนวดผู้ให้บริการและบันทึก',
  `doctor_id` BIGINT UNSIGNED NULL COMMENT 'แพทย์แผนไทยผู้กำกับดูแล/ตรวจวินิจฉัย (ถ้ามี)',
  `subjective_symptoms` TEXT NOT NULL COMMENT 'S: อาการที่ผู้ป่วยบอกเล่า (เช่น ปวดตึงคอบ่าไหล่ ร้าวลงแขนขวา มา 3 วัน)',
  `objective_signs` TEXT NOT NULL COMMENT 'O: อาการที่ตรวจพบโดยหมอนวด/แพทย์ (เช่น กล้ามเนื้อ Trapezius ตึงตัวแข็ง มี Trigger point)',
  `pre_pain_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'ระดับความปวดก่อนนวด (VAS Score 0 - 10)',
  `post_pain_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'ระดับความปวดหลังนวด (VAS Score 0 - 10)',
  `pre_rom` VARCHAR(150) NULL COMMENT 'องศาการเคลื่อนไหวของข้อต่อก่อนนวด (Range of Motion)',
  `post_rom` VARCHAR(150) NULL COMMENT 'องศาการเคลื่อนไหวของข้อต่อหลังนวด',
  `assessment_diag` TEXT NOT NULL COMMENT 'A: การวินิจฉัยโรคตามคัมภีร์แพทย์แผนไทย (เช่น ลมปลายปัตฆาตสัญญาณ 4-5)',
  `treatment_plan` TEXT NOT NULL COMMENT 'P: แผนการรักษาและคำแนะนำกลับบ้าน (เช่น นวดคลายกล้ามเนื้อ, ประคบสมุนไพร, ยืดเหยียด)',
  `thai_med_outcome` ENUM('Cured','Improved','Unchanged','Worse') NOT NULL DEFAULT 'Improved' COMMENT 'ผลลัพธ์การรักษา (หาย, ดีขึ้น, เท่าเดิม, แย่ลง)',
  `digital_signature_url` VARCHAR(255) NOT NULL COMMENT 'เส้นทางไฟล์รูปภาพลายเซ็นอิเล็กทรอนิกส์ยินยอมรับบริการของผู้ป่วย (PNG/SVG)',
  `patient_consent_signed_at` DATETIME NOT NULL COMMENT 'เวลาที่ผู้ป่วยลงลายเซ็นในหน้าจอสัมผัส',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_soap_queue` (`queue_id`),
  KEY `idx_soap_patient_date` (`patient_id`, `created_at`),
  KEY `idx_soap_therapist` (`therapist_id`),
  CONSTRAINT `fk_soap_queue` FOREIGN KEY (`queue_id`) REFERENCES `queues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_soap_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_soap_therapist` FOREIGN KEY (`therapist_id`) REFERENCES `therapists` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_soap_doctor` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางเวชระเบียน SOAP Note, ประเมิน Pain Score (VAS), ROM และ Digital Signature';

-- 9. ตารางการชำระเงินและการออกใบเสร็จ (Payments & POS Checkout - PromptPay / ESC-POS Ready)
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue_id` BIGINT UNSIGNED NOT NULL COMMENT 'อ้างอิงจากคิวที่รับบริการ',
  `patient_id` BIGINT UNSIGNED NOT NULL COMMENT 'ผู้ชำระเงิน / ผู้รับบริการ',
  `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'สาขาที่รับชำระเงิน',
  `receipt_no` VARCHAR(50) NOT NULL COMMENT 'เลขที่ใบเสร็จรับเงิน เช่น REC-202607-0001',
  `tax_invoice_no` VARCHAR(50) NULL COMMENT 'เลขที่ใบกำกับภาษีเต็มรูปแบบ เช่น TAX-202607-0001',
  `subtotal` DECIMAL(10,2) NOT NULL COMMENT 'ยอดรวมก่อนหักส่วนลด',
  `discount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'ส่วนลด (คูปอง/สมาชิก/โปรโมชั่น)',
  `net_amount` DECIMAL(10,2) NOT NULL COMMENT 'ยอดสุทธิที่ต้องชำระจริง',
  `payment_method` ENUM('Cash','PromptPay','Credit_Card','E_Wallet') NOT NULL DEFAULT 'Cash' COMMENT 'ช่องทางการชำระเงิน',
  `payment_status` ENUM('Pending','Paid','Refunded') NOT NULL DEFAULT 'Pending' COMMENT 'สถานะการชำระเงิน',
  `promptpay_ref` VARCHAR(100) NULL COMMENT 'เลขอ้างอิงธุรกรรม PromptPay QR / Ref1-Ref2',
  `credit_card_ref` VARCHAR(100) NULL COMMENT 'เลขอ้างอิงอนุมัติบัตรเครดิต (Approval Code / TID)',
  `paid_at` DATETIME NULL COMMENT 'เวลาที่ชำระเงินสำเร็จ',
  `cashier_id` BIGINT UNSIGNED NOT NULL COMMENT 'พนักงานการเงินผู้ทำรายการ (จากตาราง users)',
  `refund_reason` VARCHAR(255) NULL COMMENT 'เหตุผลในการคืนเงิน (กรณี Refund)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_payment_queue` (`queue_id`),
  UNIQUE KEY `uk_receipt_no` (`receipt_no`),
  UNIQUE KEY `uk_tax_invoice_no` (`tax_invoice_no`),
  KEY `idx_payment_date_branch` (`paid_at`, `branch_id`, `payment_status`),
  KEY `idx_payment_method` (`payment_method`),
  CONSTRAINT `fk_payments_queue` FOREIGN KEY (`queue_id`) REFERENCES `queues` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_cashier` FOREIGN KEY (`cashier_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางการชำระเงิน POS รองรับ PromptPay QR, บัตรเครดิต, ใบเสร็จ และ ESC/POS Thermal Print';

-- 10. ตารางโปรโมชั่น คูปอง และ CRM Loyalty (Promotions, Coupons & Loyalty Points)
DROP TABLE IF EXISTS `promotions_coupons`;
CREATE TABLE `promotions_coupons` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL COMMENT 'สาขาที่ใช้ได้ (NULL = ใช้ได้ทุกสาขา)',
  `code` VARCHAR(50) NOT NULL COMMENT 'รหัสคูปอง เช่น WELCOME50, THAI10',
  `name` VARCHAR(150) NOT NULL COMMENT 'ชื่อโปรโมชั่น/คูปอง',
  `discount_type` ENUM('Fixed','Percentage') NOT NULL DEFAULT 'Fixed' COMMENT 'ประเภทส่วนลด: บาท หรือ %',
  `discount_value` DECIMAL(10,2) NOT NULL COMMENT 'มูลค่าส่วนลด (เช่น 50 บาท หรือ 10%)',
  `min_spend` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'ยอดซื้อขั้นต่ำที่ใช้คูปองได้',
  `max_uses` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'จำนวนสิทธิ์สูงสุดที่ปล่อยให้ใช้',
  `used_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'จำนวนสิทธิ์ที่ถูกใช้ไปแล้ว',
  `start_date` DATETIME NOT NULL COMMENT 'เวลาเริ่มใช้โปรโมชั่น',
  `end_date` DATETIME NOT NULL COMMENT 'เวลาสิ้นสุดโปรโมชั่น',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=เปิดใช้งาน, 0=ปิด',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_coupon_code` (`code`),
  KEY `idx_coupon_dates` (`start_date`, `end_date`, `is_active`),
  CONSTRAINT `fk_coupons_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางจัดการโปรโมชั่น คูปองส่วนลด และ CRM';

-- 11. ตารางคะแนนสะสมและสมาชิกลูกค้า (Patient Loyalty & CRM Tiers)
DROP TABLE IF EXISTS `patient_loyalty`;
CREATE TABLE `patient_loyalty` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL COMMENT 'ผู้ป่วย (ตาราง patients)',
  `total_points` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'คะแนนสะสมคงเหลือปัจจุบัน',
  `tier` ENUM('Silver','Gold','Platinum') NOT NULL DEFAULT 'Silver' COMMENT 'ระดับสมาชิกลูกค้า',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_loyalty_patient` (`patient_id`),
  KEY `idx_loyalty_tier_points` (`tier`, `total_points`),
  CONSTRAINT `fk_loyalty_patient` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางสมาชิกลูกค้าและคะแนนสะสม CRM Loyalty';

-- 12. ตารางการตั้งค่าระบบ (System Settings & Announcements)
DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL COMMENT 'สาขาที่ตั้งค่า (NULL = การตั้งค่าส่วนกลาง)',
  `setting_key` VARCHAR(100) NOT NULL COMMENT 'คีย์การตั้งค่า เช่น clinic_open_time, tv_voice_language, default_room_count',
  `setting_value` TEXT NOT NULL COMMENT 'ค่าที่ตั้งค่า เช่น 08:00, TH_EN, 10',
  `setting_group` VARCHAR(50) NOT NULL DEFAULT 'general' COMMENT 'กลุ่มการตั้งค่า เช่น general, queue, tv_display, notification, integration',
  `description` VARCHAR(255) NULL COMMENT 'คำอธิบายการตั้งค่า',
  `updated_by` BIGINT UNSIGNED NULL COMMENT 'ผู้แก้ไขล่าสุด',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_setting_key_branch` (`setting_key`, `branch_id`),
  KEY `idx_setting_group` (`setting_group`),
  CONSTRAINT `fk_settings_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_settings_updater` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตารางจัดการการตั้งค่าระบบ เวลาทำการ เสียงเรียกคิวทีวี และข้อความประกาศ';

-- 13. ตารางบันทึกการใช้งานระบบด้านความปลอดภัย (Audit Logs - OWASP Compliant)
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL COMMENT 'ผู้ทำรายการ (NULL กรณีผู้เข้าชมภายนอก หรือพยายามล็อกอินล้มเหลว)',
  `branch_id` BIGINT UNSIGNED NULL COMMENT 'สาขาที่เกิดเหตุการณ์',
  `action` ENUM('Login','Logout','Login_Failed','Create','Update','Delete','Export','Print','View_EMR','2FA_Verify','HIS_Sync') NOT NULL COMMENT 'ประเภทของกิจกรรมที่ทำ',
  `entity_type` VARCHAR(50) NOT NULL COMMENT 'ชื่อตาราง/โมดูลที่ถูกกระทำ เช่น queues, patients, soap_notes, billing',
  `entity_id` BIGINT UNSIGNED NULL COMMENT 'รหัส ID ของข้อมูลที่ถูกกระทำ',
  `old_values` JSON NULL COMMENT 'ข้อมูลก่อนแก้ไข (ในรูป JSON สำหรับเปรียบเทียบการเปลี่ยนแปลง)',
  `new_values` JSON NULL COMMENT 'ข้อมูลหลังแก้ไข (ในรูป JSON)',
  `ip_address` VARCHAR(45) NOT NULL COMMENT 'IP Address ของผู้ใช้งาน',
  `user_agent` TEXT NOT NULL COMMENT 'Browser และระบบปฏิบัติการที่ใช้งาน',
  `device_type` VARCHAR(50) NULL COMMENT 'ประเภทอุปกรณ์ เช่น Desktop, iPad, Mobile, Smart TV, POS',
  `location_info` VARCHAR(150) NULL COMMENT 'ข้อมูลตำแหน่ง Geolocation / รหัสสาขา',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'เวลาที่บันทึก Log',
  PRIMARY KEY (`id`),
  KEY `idx_audit_user_time` (`user_id`, `created_at`),
  KEY `idx_audit_action_entity` (`action`, `entity_type`, `entity_id`),
  KEY `idx_audit_created_at` (`created_at`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_audit_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ตาราง Audit Log บันทึกทุกกิจกรรมด้านความปลอดภัยตามมาตรฐาน OWASP และ พ.ร.บ. คุ้มครองข้อมูลส่วนบุคคล (PDPA)';

SET FOREIGN_KEY_CHECKS = 1;


-- ============================================================================
-- Thai Traditional Massage Queue Management System (TTMQMS)
-- Enterprise Production Stored Procedures, Views & Scheduled Events (MySQL 8.0)
-- Architecture: AI Workload Balancer, Realtime Dashboard Views, Automated Cron
-- ============================================================================

SET NAMES utf8mb4;
SET GLOBAL event_scheduler = ON;

DELIMITER $$

-- ----------------------------------------------------------------------------
-- Stored Procedure 1: อัลกอริทึมจัดสรรคิวอัจฉริยะ (Smart Queue AI Allocation & Workload Balancer)
-- ทำหน้าที่เลือกหมอนวดที่ว่าง ทักษะตรงกับบริการ และมีคะแนนภาระงานน้อยที่สุด (Load Balancing) พร้อมจัดสรรห้องนวด
-- ----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_assign_smart_queue`$$
CREATE PROCEDURE `sp_assign_smart_queue`(
    IN p_queue_id BIGINT UNSIGNED,
    OUT p_assigned_therapist_id BIGINT UNSIGNED,
    OUT p_assigned_room_id BIGINT UNSIGNED,
    OUT p_est_start DATETIME,
    OUT p_est_end DATETIME,
    OUT p_status_msg VARCHAR(255)
)
BEGIN
    DECLARE v_branch_id BIGINT UNSIGNED;
    DECLARE v_service_id BIGINT UNSIGNED;
    DECLARE v_service_cat VARCHAR(50);
    DECLARE v_duration INT UNSIGNED;
    DECLARE v_priority VARCHAR(20);
    DECLARE v_skill_needed VARCHAR(100);

    -- 1. ดึงข้อมูลคิวและบริการ
    SELECT q.branch_id, q.service_id, s.category, s.duration_minutes, q.priority
    INTO v_branch_id, v_service_id, v_service_cat, v_duration, v_priority
    FROM `queues` q
    JOIN `services` s ON q.service_id = s.id
    WHERE q.id = p_queue_id AND q.status = 'Waiting';

    IF v_branch_id IS NULL THEN
        SET p_status_msg = 'ERROR: Queue not found or not in Waiting status.';
    ELSE
        -- แปลงหมวดหมู่บริการเป็นชื่อทักษะ (Skill Mapping)
        CASE v_service_cat
            WHEN 'Thai_Massage' THEN SET v_skill_needed = '"นวดไทย"';
            WHEN 'Foot_Massage' THEN SET v_skill_needed = '"นวดฝ่าเท้า"';
            WHEN 'Herbal_Compress' THEN SET v_skill_needed = '"ประคบสมุนไพร"';
            WHEN 'Oil_Massage' THEN SET v_skill_needed = '"น้ำมัน"';
            WHEN 'Office_Syndrome' THEN SET v_skill_needed = '"Office Syndrome"';
            WHEN 'Acupressure' THEN SET v_skill_needed = '"กดจุด"';
            ELSE SET v_skill_needed = '"นวดไทย"';
        END CASE;

        -- 2. ค้นหาหมอนวดที่ว่าง (is_available=1), อยู่สาขาเดียวกัน, มีทักษะตรง, และเลือกคนที่คะแนนภาระงานต่ำสุด (Balance Workload)
        SELECT id INTO p_assigned_therapist_id
        FROM `therapists`
        WHERE branch_id = v_branch_id
          AND is_available = 1
          AND is_active = 1
          AND JSON_CONTAINS(skills, v_skill_needed)
        ORDER BY current_workload_score ASC, id ASC
        LIMIT 1;

        -- 3. ค้นหาห้องนวดที่ว่าง (status='Available') ในสาขาเดียวกัน
        SELECT id INTO p_assigned_room_id
        FROM `rooms`
        WHERE branch_id = v_branch_id
          AND status = 'Available'
          AND is_active = 1
        ORDER BY room_no ASC
        LIMIT 1;

        -- 4. คำนวณเวลาประเมินและทำการจัดสรรคิวถ้ามีหมอและห้องพร้อม
        IF p_assigned_therapist_id IS NOT NULL AND p_assigned_room_id IS NOT NULL THEN
            SET p_est_start = NOW();
            SET p_est_end = DATE_ADD(NOW(), INTERVAL v_duration MINUTE);
            
            UPDATE `queues` 
            SET `therapist_id` = p_assigned_therapist_id,
                `room_id` = p_assigned_room_id,
                `status` = 'Assigned',
                `est_start_time` = p_est_start,
                `est_end_time` = p_est_end,
                `updated_at` = NOW()
            WHERE `id` = p_queue_id;

            -- ล็อกห้องนวดชั่วคราว
            UPDATE `rooms` SET `status` = 'Occupied', `current_queue_id` = p_queue_id WHERE `id` = p_assigned_room_id;
            
            SET p_status_msg = 'SUCCESS: Smart Queue assigned successfully.';
        ELSE
            -- หากไม่มีหมอหรือห้องว่าง ให้ประเมินเวลาต่อจากคิวที่กำลังให้บริการอยู่
            SELECT DATE_ADD(COALESCE(MAX(est_end_time), NOW()), INTERVAL 10 MINUTE) INTO p_est_start
            FROM `queues`
            WHERE branch_id = v_branch_id AND status IN ('Assigned', 'In_Progress');
            
            SET p_est_end = DATE_ADD(p_est_start, INTERVAL v_duration MINUTE);
            
            UPDATE `queues` 
            SET `est_start_time` = p_est_start,
                `est_end_time` = p_est_end,
                `updated_at` = NOW()
            WHERE `id` = p_queue_id;

            SET p_status_msg = 'WAITING: No therapist or room available right now. Estimated wait time updated.';
        END IF;
    END IF;
END$$

-- ----------------------------------------------------------------------------
-- Stored Procedure 2: คำนวณสรุปรายงาน KPI ประจำวัน (Daily KPI Analytics Report)
-- ----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_generate_daily_kpi`$$
CREATE PROCEDURE `sp_generate_daily_kpi`(
    IN p_date DATE,
    IN p_branch_id BIGINT UNSIGNED
)
BEGIN
    SELECT 
        COUNT(*) AS total_queues,
        SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) AS completed_queues,
        SUM(CASE WHEN status = 'Waiting' THEN 1 ELSE 0 END) AS waiting_queues,
        SUM(CASE WHEN status IN ('Cancelled', 'No_Show') THEN 1 ELSE 0 END) AS cancelled_queues,
        ROUND(AVG(CASE WHEN wait_time_mins IS NOT NULL THEN wait_time_mins ELSE TIMESTAMPDIFF(MINUTE, checkin_time, COALESCE(actual_start_time, NOW())) END), 1) AS avg_wait_time_mins,
        ROUND(AVG(COALESCE(service_time_mins, 0)), 1) AS avg_service_time_mins,
        (SELECT COALESCE(SUM(net_amount), 0.00) FROM `payments` WHERE DATE(paid_at) = p_date AND (p_branch_id IS NULL OR branch_id = p_branch_id) AND payment_status = 'Paid') AS total_revenue_baht,
        (SELECT CONCAT(u.first_name, ' ', u.last_name) 
         FROM `therapists` t 
         JOIN `users` u ON t.user_id = u.id 
         JOIN `queues` q ON q.therapist_id = t.id 
         WHERE q.queue_date = p_date AND (p_branch_id IS NULL OR q.branch_id = p_branch_id) AND q.status = 'Completed' 
         GROUP BY t.id ORDER BY COUNT(*) DESC LIMIT 1) AS top_therapist_name
    FROM `queues`
    WHERE queue_date = p_date 
      AND (p_branch_id IS NULL OR branch_id = p_branch_id);
END$$

-- ----------------------------------------------------------------------------
-- Stored Procedure 3: รีเซ็ตคะแนนภาระงานหมอนวด (Reset Workload) ประจำวัน
-- ----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_reset_daily_workloads`$$
CREATE PROCEDURE `sp_reset_daily_workloads`()
BEGIN
    UPDATE `therapists` SET `current_workload_score` = 0.00, `is_available` = 1 WHERE `is_active` = 1;
    UPDATE `rooms` SET `status` = 'Available', `current_queue_id` = NULL WHERE `status` != 'Maintenance';
END$$

DELIMITER ;

-- ----------------------------------------------------------------------------
-- View 1: มุมมองกระดานคิวแบบ Realtime สำหรับหน้า Reception และจอ TV Display
-- ----------------------------------------------------------------------------
DROP VIEW IF EXISTS `vw_realtime_queue_board`;
CREATE VIEW `vw_realtime_queue_board` AS
SELECT 
    q.id AS queue_id,
    q.branch_id,
    b.name_th AS branch_name,
    q.queue_no,
    q.priority,
    q.status,
    q.booking_type,
    p.cid,
    p.hn,
    CONCAT(p.first_name_th, ' ', p.last_name_th) AS patient_name,
    s.name_th AS service_name_th,
    s.name_en AS service_name_en,
    s.duration_minutes,
    COALESCE(CONCAT(u.first_name, ' ', u.last_name), 'กำลังรอหมอนวด (AI Allocation)') AS therapist_name,
    COALESCE(r.room_no, 'รอจัดห้อง') AS room_no,
    q.checkin_time,
    q.est_start_time,
    q.est_end_time,
    TIMESTAMPDIFF(MINUTE, q.checkin_time, NOW()) AS current_waiting_mins
FROM `queues` q
JOIN `branches` b ON q.branch_id = b.id
JOIN `patients` p ON q.patient_id = p.id
JOIN `services` s ON q.service_id = s.id
LEFT JOIN `therapists` t ON q.therapist_id = t.id
LEFT JOIN `users` u ON t.user_id = u.id
LEFT JOIN `rooms` r ON q.room_id = r.id
WHERE q.queue_date = CURDATE()
ORDER BY 
    CASE q.priority WHEN 'Emergency' THEN 1 WHEN 'VIP' THEN 2 ELSE 3 END ASC,
    q.checkin_time ASC;

-- ----------------------------------------------------------------------------
-- View 2: มุมมองสรุปภาระงานและรายได้หมอนวดประจำวัน (Therapist Workload & Earnings View)
-- ----------------------------------------------------------------------------
DROP VIEW IF EXISTS `vw_therapist_workload_summary`;
CREATE VIEW `vw_therapist_workload_summary` AS
SELECT 
    t.id AS therapist_id,
    t.branch_id,
    u.national_id,
    CONCAT(u.title, u.first_name, ' ', u.last_name) AS therapist_fullname,
    t.license_no,
    t.specialization,
    t.current_workload_score,
    t.is_available,
    COUNT(q.id) AS total_assigned_today,
    SUM(CASE WHEN q.status = 'Completed' THEN 1 ELSE 0 END) AS completed_queues_today,
    SUM(CASE WHEN q.status = 'In_Progress' THEN 1 ELSE 0 END) AS in_progress_queues,
    COALESCE(SUM(CASE WHEN q.status = 'Completed' THEN COALESCE(q.service_time_mins, s.duration_minutes) ELSE 0 END), 0) AS total_massage_minutes_today,
    COALESCE(SUM(CASE WHEN q.status = 'Completed' THEN pm.net_amount ELSE 0.00 END), 0.00) AS total_generated_revenue_today
FROM `therapists` t
JOIN `users` u ON t.user_id = u.id
LEFT JOIN `queues` q ON q.therapist_id = t.id AND q.queue_date = CURDATE()
LEFT JOIN `services` s ON q.service_id = s.id
LEFT JOIN `payments` pm ON pm.queue_id = q.id AND pm.payment_status = 'Paid'
WHERE t.is_active = 1
GROUP BY t.id, t.branch_id, u.national_id, u.title, u.first_name, u.last_name, t.license_no, t.specialization, t.current_workload_score, t.is_available;

-- ----------------------------------------------------------------------------
-- Scheduled Event: ตั้งเวลาตั้งค่า Workload ใหม่ทุกเที่ยงคืน (Cron Job Event)
-- ----------------------------------------------------------------------------
DROP EVENT IF EXISTS `ev_daily_reset_workload`;
CREATE EVENT `ev_daily_reset_workload`
ON SCHEDULE EVERY 1 DAY STARTS (TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '00:00:00'))
ON COMPLETION PRESERVE
ENABLE
DO
    CALL sp_reset_daily_workloads();


-- ============================================================================
-- Thai Traditional Massage Queue Management System (TTMQMS)
-- Enterprise Production Database Triggers (MySQL 8.0)
-- Architecture: Automated Audit Logging, Realtime Workload & Loyalty Sync
-- ============================================================================

SET NAMES utf8mb4;

DELIMITER $$

-- ----------------------------------------------------------------------------
-- Trigger 1: สร้างข้อมูลสมาชิกลูกค้าและสะสมแต้ม (Loyalty Account) อัตโนมัติเมื่อเพิ่มผู้ป่วยใหม่
-- ----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_patients_after_insert`$$
CREATE TRIGGER `trg_patients_after_insert`
AFTER INSERT ON `patients`
FOR EACH ROW
BEGIN
    INSERT INTO `patient_loyalty` (`patient_id`, `total_points`, `tier`, `created_at`, `updated_at`)
    VALUES (NEW.id, 100, 'Silver', NOW(), NOW()); -- มอบแต้มต้อนรับ 100 คะแนนสำหรับผู้รับบริการใหม่

    -- บันทึก Audit Log
    INSERT INTO `audit_logs` (`user_id`, `branch_id`, `action`, `entity_type`, `entity_id`, `new_values`, `ip_address`, `user_agent`, `device_type`, `created_at`)
    VALUES (NEW.user_id, NULL, 'Create', 'patients', NEW.id, JSON_OBJECT('cid', NEW.cid, 'hn', NEW.hn, 'name', CONCAT(NEW.first_name_th, ' ', NEW.last_name_th)), 'SYSTEM_TRIGGER', 'MySQL Trigger Engine', 'Database', NOW());
END$$

-- ----------------------------------------------------------------------------
-- Trigger 2: อัปเดตสถานะห้องพัก, คะแนนภาระงานหมอนวด (Workload) และ Audit Log เมื่อสถานะคิวเปลี่ยนแปลง
-- ----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_queues_after_update`$$
CREATE TRIGGER `trg_queues_after_update`
AFTER UPDATE ON `queues`
FOR EACH ROW
BEGIN
    -- กรณีคิวเปลี่ยนเป็น In_Progress (เริ่มนวดจริง)
    IF NEW.status = 'In_Progress' AND OLD.status != 'In_Progress' THEN
        -- อัปเดตห้องนวดเป็น Occupied และเชื่อมโยงคิว
        IF NEW.room_id IS NOT NULL THEN
            UPDATE `rooms` SET `status` = 'Occupied', `current_queue_id` = NEW.id WHERE `id` = NEW.room_id;
        END IF;
        
        -- อัปเดตหมอนวดเป็น ไม่ว่างรับงาน (is_available = 0)
        IF NEW.therapist_id IS NOT NULL THEN
            UPDATE `therapists` SET `is_available` = 0 WHERE `id` = NEW.therapist_id;
        END IF;
    END IF;

    -- กรณีคิวเปลี่ยนเป็น Completed (เสร็จสิ้นบริการ)
    IF NEW.status = 'Completed' AND OLD.status != 'Completed' THEN
        -- อัปเดตห้องนวดเป็น Cleaning (รอทำความสะอาดก่อนรับคิวใหม่)
        IF NEW.room_id IS NOT NULL THEN
            UPDATE `rooms` SET `status` = 'Cleaning', `current_queue_id` = NULL WHERE `id` = NEW.room_id;
        END IF;

        -- อัปเดตหมอนวดเป็น ว่างรับงาน (is_available = 1) และเพิ่มคะแนน Workload ตามเวลาบริการจริง
        IF NEW.therapist_id IS NOT NULL THEN
            UPDATE `therapists` 
            SET `is_available` = 1, 
                `current_workload_score` = `current_workload_score` + (COALESCE(NEW.service_time_mins, 60) / 60.0) 
            WHERE `id` = NEW.therapist_id;
        END IF;
    END IF;

    -- กรณีคิวถูกยกเลิก (Cancelled หรือ No_Show)
    IF NEW.status IN ('Cancelled', 'No_Show') AND OLD.status NOT IN ('Cancelled', 'No_Show') THEN
        IF NEW.room_id IS NOT NULL THEN
            UPDATE `rooms` SET `status` = 'Available', `current_queue_id` = NULL WHERE `id` = NEW.room_id;
        END IF;
        IF NEW.therapist_id IS NOT NULL THEN
            UPDATE `therapists` SET `is_available` = 1 WHERE `id` = NEW.therapist_id;
        END IF;
    END IF;

    -- บันทึก Audit Log การเปลี่ยนสถานะคิว
    IF NEW.status != OLD.status THEN
        INSERT INTO `audit_logs` (`user_id`, `branch_id`, `action`, `entity_type`, `entity_id`, `old_values`, `new_values`, `ip_address`, `user_agent`, `device_type`, `created_at`)
        VALUES (NEW.created_by, NEW.branch_id, 'Update', 'queues', NEW.id, 
                JSON_OBJECT('status', OLD.status, 'therapist_id', OLD.therapist_id, 'room_id', OLD.room_id),
                JSON_OBJECT('status', NEW.status, 'therapist_id', NEW.therapist_id, 'room_id', NEW.room_id),
                'SYSTEM_TRIGGER', 'MySQL Trigger Engine', 'Database', NOW());
    END IF;
END$$

-- ----------------------------------------------------------------------------
-- Trigger 3: คำนวณแต้มสะสม CRM Loyalty และปรับระดับสมาชิกลูกค้าอัตโนมัติเมื่อรับชำระเงินสำเร็จ
-- ----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_payments_after_insert`$$
CREATE TRIGGER `trg_payments_after_insert`
AFTER INSERT ON `payments`
FOR EACH ROW
BEGIN
    DECLARE v_earned_points INT UNSIGNED DEFAULT 0;
    DECLARE v_new_total INT UNSIGNED DEFAULT 0;
    DECLARE v_new_tier VARCHAR(20) DEFAULT 'Silver';

    IF NEW.payment_status = 'Paid' THEN
        -- คำนวณแต้มสะสม (ทุก 25 บาท = 1 แต้ม)
        SET v_earned_points = FLOOR(NEW.net_amount / 25);

        -- อัปเดตแต้มสะสม
        UPDATE `patient_loyalty` 
        SET `total_points` = `total_points` + v_earned_points,
            `updated_at` = NOW()
        WHERE `patient_id` = NEW.patient_id;

        -- ดึงแต้มรวมล่าสุดมาปรับระดับสมาชิกลูกค้า (Tier)
        SELECT `total_points` INTO v_new_total FROM `patient_loyalty` WHERE `patient_id` = NEW.patient_id;
        
        IF v_new_total >= 5000 THEN
            SET v_new_tier = 'Platinum';
        ELSEIF v_new_total >= 2000 THEN
            SET v_new_tier = 'Gold';
        ELSE
            SET v_new_tier = 'Silver';
        END IF;

        UPDATE `patient_loyalty` SET `tier` = v_new_tier WHERE `patient_id` = NEW.patient_id;

        -- บันทึก Audit Log การเงิน
        INSERT INTO `audit_logs` (`user_id`, `branch_id`, `action`, `entity_type`, `entity_id`, `new_values`, `ip_address`, `user_agent`, `device_type`, `created_at`)
        VALUES (NEW.cashier_id, NEW.branch_id, 'Create', 'payments', NEW.id, 
                JSON_OBJECT('receipt_no', NEW.receipt_no, 'net_amount', NEW.net_amount, 'method', NEW.payment_method, 'earned_points', v_earned_points), 
                'POS_SYSTEM', 'Cashier Terminal', 'POS', NOW());
    END IF;
END$$

-- ----------------------------------------------------------------------------
-- Trigger 4: ป้องกัน Brute Force - ระงับบัญชี (Lockout) อัตโนมัติเมื่อกรอกรหัสผ่านผิดเกิน 5 ครั้ง
-- ----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_users_before_update`$$
CREATE TRIGGER `trg_users_before_update`
BEFORE UPDATE ON `users`
FOR EACH ROW
BEGIN
    -- ถ้ากรอกรหัสผิดครบ 5 ครั้ง และบัญชียังไม่ถูกระงับ ให้ล็อก 15 นาที
    IF NEW.failed_login_attempts >= 5 AND (OLD.locked_until IS NULL OR OLD.locked_until < NOW()) THEN
        SET NEW.locked_until = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
        
        -- บันทึก Audit Log แจ้งเตือนความมั่งคงปลอดภัย
        INSERT INTO `audit_logs` (`user_id`, `branch_id`, `action`, `entity_type`, `entity_id`, `new_values`, `ip_address`, `user_agent`, `device_type`, `created_at`)
        VALUES (NEW.id, NEW.branch_id, 'Login_Failed', 'users', NEW.id, 
                JSON_OBJECT('event', 'ACCOUNT_LOCKED', 'attempts', NEW.failed_login_attempts, 'locked_until', NEW.locked_until), 
                COALESCE(NEW.last_login_ip, 'UNKNOWN'), 'Security Shield Engine', 'Firewall Guard', NOW());
    END IF;
    
    -- ถ้าล็อกอินสำเร็จ (รีเซ็ต failed_login_attempts เป็น 0) ให้ปลดล็อก
    IF NEW.failed_login_attempts = 0 THEN
        SET NEW.locked_until = NULL;
    END IF;
END$$

DELIMITER ;


-- ============================================================================
-- Thai Traditional Massage Queue Management System (TTMQMS)
-- Enterprise Production Seed Data (MySQL 8.0)
-- Sample Data: Admin (with 2FA), Staff, Therapists, Patients, Services, Rooms
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. ล้างข้อมูลตารางและรีเซ็ต AUTO_INCREMENT
TRUNCATE TABLE `audit_logs`;
TRUNCATE TABLE `system_settings`;
TRUNCATE TABLE `patient_loyalty`;
TRUNCATE TABLE `promotions_coupons`;
TRUNCATE TABLE `payments`;
TRUNCATE TABLE `soap_notes`;
TRUNCATE TABLE `queues`;
TRUNCATE TABLE `rooms`;
TRUNCATE TABLE `services`;
TRUNCATE TABLE `therapists`;
TRUNCATE TABLE `patients`;
TRUNCATE TABLE `users`;
TRUNCATE TABLE `branches`;

-- 2. ข้อมูลสาขาและหน่วยบริการ (Branches)
INSERT INTO `branches` (`id`, `code`, `name_th`, `name_en`, `address`, `phone`, `tax_id`, `is_main`, `is_active`) VALUES
(1, 'BR-001', 'ศูนย์การแพทย์แผนไทยและสปา (สำนักงานใหญ่)', 'Thai Traditional Medicine & Spa Center (HQ)', '123 ถนนสุขุมวิท แขวงคลองเตย เขตคลองเตย กรุงเทพมหานคร 10110', '02-123-4567', '0105558000123', 1, 1),
(2, 'BR-002', 'คลินิกเวชกรรมแผนไทย สาขาสยามสแควร์', 'Siam Square Thai Traditional Clinic Branch', '456 ชั้น 3 สยามสแควร์วัน ถนนพระราม 1 เขตปทุมวัน กรุงเทพมหานคร 10330', '02-987-6543', '0105558000123', 0, 1);

-- 3. ข้อมูลผู้ใช้งานระบบทุกระดับ (Users & Staff - รหัสผ่านเริ่มต้นสำหรับทุกคนคือ Admin@2026 เข้ารหัสด้วย Argon2id)
-- Argon2id Hash สำหรับ Admin@2026 : $argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k
INSERT INTO `users` (`id`, `national_id`, `password_hash`, `email`, `phone`, `title`, `first_name`, `last_name`, `role`, `branch_id`, `is_active`, `two_factor_secret`, `two_factor_enabled`, `two_factor_recovery_codes`) VALUES
(1, '1111111111111', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'admin@ttmqms.com', '081-111-1111', 'นาย', 'สมเกียรติ', 'ผู้ดูแลระบบ', 'Admin', 1, 1, 'JBSWY3DPEHPK3PXP', 1, '["REC-1001", "REC-1002", "REC-1003", "REC-1004"]'),
(2, '2222222222222', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'manager@ttmqms.com', '082-222-2222', 'นาง', 'กนกวรรณ', 'บริหารการแพทย์', 'Manager', 1, 1, NULL, 0, NULL),
(3, '3333333333333', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'reception@ttmqms.com', '083-333-3333', 'นางสาว', 'สุดา', 'ต้อนรับดี', 'Reception', 1, 1, NULL, 0, NULL),
(4, '4444444444444', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'cashier@ttmqms.com', '084-444-4444', 'นางสาว', 'การเงิน', 'แม่นยำ', 'Cashier', 1, 1, NULL, 0, NULL),
(5, '5555555555555', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'doctor@ttmqms.com', '085-555-5555', 'พญ.', 'ชีวกา', 'โกมารภัจจ์', 'Doctor', 1, 1, NULL, 0, NULL),
(6, '6666666666661', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'therapist1@ttmqms.com', '086-666-0001', 'นาง', 'ทองดี', 'มือทอง', 'Therapist', 1, 1, NULL, 0, NULL),
(7, '6666666666662', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'therapist2@ttmqms.com', '086-666-0002', 'นาย', 'บุญมี', 'นวดคลายเส้น', 'Therapist', 1, 1, NULL, 0, NULL),
(8, '6666666666663', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'therapist3@ttmqms.com', '086-666-0003', 'นางสาว', 'สายฝน', 'หัตถเวช', 'Therapist', 2, 1, NULL, 0, NULL),
(9, '7777777777777', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'patient1@example.com', '087-777-7777', 'นาย', 'สมชาย', 'รักสุขภาพ', 'Patient', NULL, 1, NULL, 0, NULL),
(10, '8888888888888', '$argon2id$v=19$m=65536,t=4,p=1$c2FsdF9zYWx0X3NhbHRfc2FsdA$k8/Z4N9wP5vL0r3mJ6xY7uZ8vW9aB0cD1eF2gH3iJ4k', 'patient2@example.com', '088-888-8888', 'นาง', 'ปราณี', 'ใจดีมาก', 'Patient', NULL, 1, NULL, 0, NULL);

-- 4. ข้อมูลหมอนวด (Therapists & Workload Skills)
INSERT INTO `therapists` (`id`, `user_id`, `branch_id`, `license_no`, `specialization`, `skills`, `working_days`, `shift_start`, `shift_end`, `max_daily_minutes`, `current_workload_score`, `is_available`, `is_active`) VALUES
(1, 6, 1, 'TM-2565-00123', 'ผู้เชี่ยวชาญการนวดรักษาออฟฟิศซินโดรมและปวดไมเกรน', '["นวดไทย", "นวดฝ่าเท้า", "ประคบสมุนไพร", "Office Syndrome", "กดจุด"]', '[1,2,3,4,5,6]', '08:30:00', '17:30:00', 480, 1.50, 1, 1),
(2, 7, 1, 'TM-2564-00987', 'ผู้เชี่ยวชาญการนวดน้ำมันและสปาคลายเครียด', '["นวดไทย", "นวดฝ่าเท้า", "น้ำมัน"]', '[1,2,3,5,6,7]', '09:00:00', '18:00:00', 480, 0.00, 1, 1),
(3, 8, 2, 'TM-2566-00456', 'ผู้เชี่ยวชาญการนวดไทยราชสำนักและประคบสมุนไพรสด', '["นวดไทย", "ประคบสมุนไพร", "กดจุด"]', '[2,3,4,5,6,7]', '10:00:00', '19:00:00', 480, 2.00, 1, 1);

-- 5. ข้อมูลผู้ป่วยและผู้รับบริการ (Patients - พร้อม HIS/HOSxP Sync Data)
INSERT INTO `patients` (`id`, `user_id`, `cid`, `hn`, `first_name_th`, `last_name_th`, `first_name_en`, `last_name_en`, `birth_date`, `gender`, `blood_group`, `address`, `phone`, `line_id`, `email`, `underlying_diseases`, `massage_contraindications`, `drug_allergies`, `emergency_contact_name`, `emergency_contact_phone`, `his_synced_at`, `his_raw_data`) VALUES
(1, 9, '7777777777777', 'HN-690001', 'สมชาย', 'รักสุขภาพ', 'Somchai', 'Raksookhapap', '1985-05-15', 'Male', 'O', '99/1 ซอย 5 ถนนรัชดาภิเษก แขวงดินแดง เขตดินแดง กรุงเทพมหานคร 10400', '087-777-7777', '@somchai_health', 'patient1@example.com', 'ความดันโลหิตสูง (ทานยาควบคุมประจำ)', 'หลีกเลี่ยงการกดจุดบริเวณคอและท่าดัดหลังรุนแรง', 'ไม่มีประวัติแพ้ยา', 'นางสาวใจดี รักสุขภาพ (ภรรยา)', '087-777-8888', NOW(), '{"his_id": 100456, "pt_status": "Active", "insurance": "Social Security", "hosxp_sync": true}'),
(2, 10, '8888888888888', 'HN-690002', 'ปราณี', 'ใจดีมาก', 'Pranee', 'Jaideemak', '1992-11-20', 'Female', 'A', '55/9 หมู่ 3 ถนนพหลโยธิน แขวงลาดยาว เขตจตุจักร กรุงเทพมหานคร 10900', '088-888-8888', '@pranee_j', 'patient2@example.com', 'ไม่มี', 'ไม่มีข้อห้าม', 'แพ้สมุนไพรไพล (มีผื่นแดงเมื่อสัมผัส)', 'นายบุญมา ใจดีมาก (สามี)', '088-888-9999', NOW(), '{"his_id": 100457, "pt_status": "Active", "insurance": "Private", "hosxp_sync": true}');

-- 6. รายการบริการและแพ็กเกจการรักษา (Services)
INSERT INTO `services` (`id`, `branch_id`, `code`, `name_th`, `name_en`, `category`, `description`, `duration_minutes`, `price`, `promo_price`, `is_package`, `package_items`, `is_active`) VALUES
(1, NULL, 'SVC-TH60', 'นวดไทยแบบราชสำนักคลายกล้ามเนื้อ (60 นาที)', 'Traditional Thai Royal Massage (60 Mins)', 'Thai_Massage', 'การนวดกดจุดตามแนวเส้นประธานสิบ เพื่อคลายกล้ามเนื้อที่ตึงเครียดและกระตุ้นการไหลเวียนโลหิต', 60, 450.00, 399.00, 0, NULL, 1),
(2, NULL, 'SVC-TH90', 'นวดไทยราชสำนักและประคบสมุนไพรสด (90 นาที)', 'Thai Massage with Fresh Herbal Compress (90 Mins)', 'Herbal_Compress', 'นวดไทยร่วมกับการประคบด้วยลูกประคบสมุนไพรร้อน (ไพล ขมิ้น ตะไคร้ มะกรูด) ช่วยบรรเทาอาการปวดข้อและลดอักเสบ', 90, 750.00, 690.00, 0, NULL, 1),
(3, NULL, 'SVC-FOOT60', 'นวดกดจุดสะท้อนฝ่าเท้าเพื่อสุขภาพ (60 นาที)', 'Reflexology Foot Massage (60 Mins)', 'Foot_Massage', 'กดจุดสะท้อนที่ฝ่าเท้า เชื่อมโยงกับอวัยวะภายใน ช่วยผ่อนคลายและลดอาการเมื่อยล้าจากการเดินยืนนาน', 60, 400.00, NULL, 0, NULL, 1),
(4, NULL, 'SVC-OIL90', 'นวดน้ำมันหอมระเหยอโรมาเธอราพี (90 นาที)', 'Aromatherapy Oil Massage (90 Mins)', 'Oil_Massage', 'นวดรีแลกซ์ด้วยน้ำมันหอมระเหยสกัดธรรมชาติ ช่วยผ่อนคลายระบบประสาทและบำรุงผิวพรรณ', 90, 950.00, 890.00, 0, NULL, 1),
(5, 1, 'SVC-OFFICE60', 'นวดรักษาอาการออฟฟิศซินโดรมและเน้นคอบ่าไหล่ (60 นาที)', 'Office Syndrome & Neck-Shoulder Therapy (60 Mins)', 'Office_Syndrome', 'การรักษาเชิงคลินิก เน้นคลายจุด Trigger Point บริเวณคอ บ่า ไหล่ และสะบัก สำหรับผู้ที่ทำงานหน้าคอมพิวเตอร์', 60, 650.00, 599.00, 0, NULL, 1),
(6, NULL, 'PKG-GOLD120', 'คอร์สแพ็กเกจฟื้นฟูสุขภาพทองคำ (120 นาที)', 'Golden Health Revitalization Package (120 Mins)', 'Course_Package', 'รวมสุดยอดการบำบัด: นวดไทย 60 นาที + นวดประคบสมุนไพร 30 นาที + นวดฝ่าเท้า 30 นาที', 120, 1200.00, 999.00, 1, '["SVC-TH60", "SVC-FOOT60", "SVC-TH90"]', 1);

-- 7. จัดการห้องนวดในสาขา (Rooms)
INSERT INTO `rooms` (`id`, `branch_id`, `room_no`, `name`, `room_type`, `capacity`, `status`, `notes`, `is_active`) VALUES
(1, 1, 'R101', 'ห้องนวดไทยรวม A (เตียง 1-2)', 'Thai_Bed', 2, 'Available', 'เตียงกว้างพิเศษ แอร์เย็นสบาย', 1),
(2, 1, 'R102', 'ห้องนวดไทยรวม B (เตียง 3-4)', 'Thai_Bed', 2, 'Available', 'ติดหน้าต่าง วิวสวน', 1),
(3, 1, 'VIP-01', 'ห้องสปา VIP รักษาความเป็นส่วนตัว', 'VIP', 1, 'Available', 'มีห้องน้ำในตัว เตียงปรับไฟฟ้า เสียงเพลงสปา', 1),
(4, 1, 'OIL-01', 'ห้องนวดน้ำมันและอโรมา', 'Oil_Bed', 1, 'Available', 'เตียงนวดมีช่องวางหน้า ผ้าปูที่นอนกันน้ำมัน', 1),
(5, 2, 'S-101', 'ห้องเวชกรรมไทย 1 (สาขาสยาม)', 'Normal', 1, 'Available', 'เครื่องปรับอากาศอินเวอร์เตอร์เงียบพิเศษ', 1);

-- 8. การตั้งค่าระบบมาตรฐาน (System Settings)
INSERT INTO `system_settings` (`branch_id`, `setting_key`, `setting_value`, `setting_group`, `description`) VALUES
(NULL, 'clinic_name_th', 'ศูนย์การแพทย์แผนไทยและสปา (TTMQMS)', 'general', 'ชื่อหน่วยบริการภาษาไทย'),
(NULL, 'clinic_name_en', 'Thai Traditional Medicine & Spa Center', 'general', 'ชื่อหน่วยบริการภาษาอังกฤษ'),
(NULL, 'open_time', '08:30', 'general', 'เวลาเปิดทำการ'),
(NULL, 'close_time', '20:00', 'general', 'เวลาปิดทำการ'),
(NULL, 'tv_voice_lang', 'TH_EN', 'tv_display', 'ภาษาเสียงเรียกคิว TV (TH=ไทยอย่างเดียว, TH_EN=ไทยและอังกฤษ)'),
(NULL, 'tv_speech_speed', '0.9', 'tv_display', 'ความเร็วเสียงอ่าน TTS (0.5 - 1.5)'),
(NULL, 'escpos_printer_ip', '192.168.1.200', 'integration', 'IP Address เครื่องพิมพ์ ESC/POS LAN'),
(NULL, 'escpos_printer_port', '9100', 'integration', 'Port เครื่องพิมพ์ความร้อน ESC/POS'),
(NULL, 'his_api_endpoint', 'https://his.hospital.local/api/v1', 'integration', 'HIS / HOSxP REST API Base URL'),
(NULL, 'line_notify_token', 'DEMO_LINE_NOTIFY_TOKEN_XXXXX', 'notification', 'LINE Notify Access Token สำหรับแจ้งคิว');

-- 9. ข้อมูลโปรโมชั่นและคูปองส่วนลด (Coupons)
INSERT INTO `promotions_coupons` (`branch_id`, `code`, `name`, `discount_type`, `discount_value`, `min_spend`, `max_uses`, `used_count`, `start_date`, `end_date`, `is_active`) VALUES
(NULL, 'WELCOME50', 'ส่วนลดต้อนรับผู้รับบริการใหม่ 50 บาท', 'Fixed', 50.00, 300.00, 1000, 12, '2026-01-01 00:00:00', '2026-12-31 23:59:59', 1),
(NULL, 'VIP10', 'ส่วนลดสมาชิกระดับ VIP และผู้สูงอายุ 10%', 'Percentage', 10.00, 500.00, 500, 45, '2026-01-01 00:00:00', '2026-12-31 23:59:59', 1);

-- 10. ข้อมูลจำลองคิวในวันนี้ (Demo Queues for Realtime Dashboard & TV Display)
INSERT INTO `queues` (`id`, `branch_id`, `queue_no`, `queue_date`, `patient_id`, `therapist_id`, `service_id`, `room_id`, `booking_type`, `priority`, `status`, `checkin_time`, `est_start_time`, `est_end_time`, `actual_start_time`, `actual_end_time`, `wait_time_mins`, `service_time_mins`, `created_by`) VALUES
(1, 1, 'A001', CURDATE(), 1, 1, 1, 1, 'Walk_in', 'Normal', 'Completed', DATE_SUB(NOW(), INTERVAL 150 MINUTE), DATE_SUB(NOW(), INTERVAL 135 MINUTE), DATE_SUB(NOW(), INTERVAL 75 MINUTE), DATE_SUB(NOW(), INTERVAL 135 MINUTE), DATE_SUB(NOW(), INTERVAL 75 MINUTE), 15, 60, 3),
(2, 1, 'V001', CURDATE(), 2, 2, 4, 3, 'Appointment', 'VIP', 'In_Progress', DATE_SUB(NOW(), INTERVAL 45 MINUTE), DATE_SUB(NOW(), INTERVAL 30 MINUTE), DATE_ADD(NOW(), INTERVAL 60 MINUTE), DATE_SUB(NOW(), INTERVAL 30 MINUTE), NULL, 15, NULL, 3),
(3, 1, 'E001', CURDATE(), 1, NULL, 5, NULL, 'Walk_in', 'Emergency', 'Waiting', DATE_SUB(NOW(), INTERVAL 10 MINUTE), DATE_ADD(NOW(), INTERVAL 5 MINUTE), DATE_ADD(NOW(), INTERVAL 65 MINUTE), NULL, NULL, NULL, NULL, 3);

-- อัปเดตห้องนวด 3 ที่กำลังใช้งาน (V001)
UPDATE `rooms` SET `status` = 'Occupied', `current_queue_id` = 2 WHERE `id` = 3;

-- 11. ข้อมูลบันทึกเวชระเบียนคลินิก (SOAP Notes for Queue 1)
INSERT INTO `soap_notes` (`queue_id`, `patient_id`, `therapist_id`, `doctor_id`, `subjective_symptoms`, `objective_signs`, `pre_pain_score`, `post_pain_score`, `pre_rom`, `post_rom`, `assessment_diag`, `treatment_plan`, `thai_med_outcome`, `digital_signature_url`, `patient_consent_signed_at`) VALUES
(1, 1, 1, 5, 'ปวดตึงกล้ามเนื้อคอบ่าไหล่ทั้งสองข้าง ร้าวขึ้นขมับ มีอาการมา 3 วันหลังนั่งทำงานหน้าคอมนาน 8 ชั่วโมง', 'กล้ามเนื้อ Trapezius และ Levator scapulae ทั้งสองข้างตึงตัวแข็ง (Hypertonicity) ตรวจพบ Trigger point ที่บ่าขวา กดแล้วปวดร้าวขึ้นศีรษะ', 7, 2, 'คอหันขวาได้ 45 องศา (ปกติ 70) ก้มคอได้ 30 องศา มีอาการปวดรั้ง', 'คอหันขวาได้ 65 องศา ก้มคอได้ 50 องศา อาการปวดรั้งลดลงชัดเจน', 'ลมปลายปัตฆาตสัญญาณ 4-5 คอ ร่วมกับอาการออฟฟิศซินโดรม', 'นวดคลายกล้ามเนื้อตามแนวเส้นประธานสิบ กดสัญญาณ 4-5 คอ ประคบสมุนไพรร้อน แนะนำท่าบริหารยืดเหยียดคอบ่าไหล่ทุก 2 ชั่วโมง', 'Improved', '/storage/signatures/sig_patient1_20260727.png', DATE_SUB(NOW(), INTERVAL 140 MINUTE));

-- 12. ข้อมูลชำระเงินของคิวที่ 1 (Payments & POS)
INSERT INTO `payments` (`queue_id`, `patient_id`, `branch_id`, `receipt_no`, `tax_invoice_no`, `subtotal`, `discount`, `net_amount`, `payment_method`, `payment_status`, `promptpay_ref`, `paid_at`, `cashier_id`) VALUES
(1, 1, 1, 'REC-202607-0001', 'TAX-202607-0001', 450.00, 51.00, 399.00, 'PromptPay', 'Paid', 'PP-REF-0987654321', DATE_SUB(NOW(), INTERVAL 70 MINUTE), 4);

SET FOREIGN_KEY_CHECKS = 1;