-- DentalCare v0.5.9.8.2
-- Schedule density, future assigned-patient scope, persistent stock catalog, and governed treatment-plan handoff.


CREATE TABLE IF NOT EXISTS `{{prefix}}treatment_plan_reassignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `treatment_plan_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `request_type` ENUM('consultation','transfer') NOT NULL DEFAULT 'consultation',
  `from_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `to_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `status` ENUM('pending','accepted','declined','cancelled') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` BIGINT UNSIGNED NULL,
  `decided_at` DATETIME NULL,
  `decision_note` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_treatment_handoff_plan` (`treatment_plan_id`,`status`,`requested_at`),
  KEY `idx_treatment_handoff_target` (`to_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_treatment_handoff_patient` (`patient_id`,`status`,`requested_at`),
  CONSTRAINT `{{prefix}}fk_treatment_handoff_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_from` FOREIGN KEY (`from_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_to` FOREIGN KEY (`to_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_requester` FOREIGN KEY (`requested_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_decider` FOREIGN KEY (`decided_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS `{{prefix}}inhouse_checkup_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `provider_user_id` BIGINT UNSIGNED NULL,
  `status` ENUM('in_progress','completed','referred') NOT NULL DEFAULT 'in_progress',
  `blood_pressure_systolic` SMALLINT UNSIGNED NULL,
  `blood_pressure_diastolic` SMALLINT UNSIGNED NULL,
  `pulse_rate` SMALLINT UNSIGNED NULL,
  `respiratory_rate` SMALLINT UNSIGNED NULL,
  `temperature_c` DECIMAL(4,1) NULL,
  `oxygen_saturation` DECIMAL(5,2) NULL,
  `weight_kg` DECIMAL(7,2) NULL,
  `height_cm` DECIMAL(7,2) NULL,
  `bmi` DECIMAL(5,2) NULL,
  `blood_glucose_mg_dl` DECIMAL(7,2) NULL,
  `clearance_status` ENUM('not_applicable','received','pending','incomplete','rejected') NOT NULL DEFAULT 'not_applicable',
  `findings` TEXT NULL,
  `notes` TEXT NULL,
  `abnormal_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `referral_dentist_user_id` BIGINT UNSIGNED NULL,
  `referral_reason` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NOT NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inhouse_checkup_queue` (`queue_entry_id`),
  KEY `idx_inhouse_checkup_patient` (`patient_id`,`created_at`),
  KEY `idx_inhouse_checkup_branch_status` (`branch_id`,`status`,`created_at`),
  KEY `idx_inhouse_checkup_provider` (`provider_user_id`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_provider` FOREIGN KEY (`provider_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_referral_dentist` FOREIGN KEY (`referral_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_recorder` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `{{prefix}}inventory_items` (`sku`,`name`,`category`,`unit`,`track_lots`,`is_clinical_supply`,`purchase_cost`,`usage_cost`,`reorder_level`,`current_stock`,`status`,`created_at`) VALUES
('SUP-PPE-001','Nitrile Examination Gloves - Small','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-002','Nitrile Examination Gloves - Medium','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-003','Nitrile Examination Gloves - Large','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-004','Surgical Face Masks','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-005','Disposable Isolation Gowns','PPE','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-006','Disposable Head Caps','PPE','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-007','Face Shields','PPE','piece',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-001','Patient Bibs','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-002','Cotton Rolls','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-003','Cotton Pellets','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-004','Sterile Gauze Pads 2 x 2','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-005','Sterile Gauze Pads 4 x 4','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-006','Saliva Ejectors','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-007','High-Volume Evacuation Tips','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-008','Disposable Air-Water Syringe Tips','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-009','Disposable Prophy Cups','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-010','Disposable Prophy Brushes','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-001','Lidocaine 2% with Epinephrine Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-002','Articaine 4% with Epinephrine Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-003','Mepivacaine 3% Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-004','Topical Anesthetic Gel','Anesthetics','tube',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-005','Dental Needles - Short','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-006','Dental Needles - Long','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-001','Phosphoric Acid Etchant Gel 37%','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-002','Universal Dental Bonding Agent','Restorative','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-003','Universal Composite Resin','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-004','Flowable Composite Resin','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-005','Glass Ionomer Restorative Cement','Restorative','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-006','Temporary Filling Material','Restorative','jar',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-007','Calcium Hydroxide Liner','Restorative','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-008','Matrix Bands','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-009','Interdental Wedges','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-010','Articulating Paper','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-001','Prophylaxis Paste','Preventive','jar',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-002','Fluoride Varnish','Preventive','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-003','Pit and Fissure Sealant','Preventive','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-001','Gutta-Percha Points','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-002','Absorbent Paper Points','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-003','Sodium Hypochlorite Irrigant','Endodontics','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-004','EDTA Root Canal Lubricant','Endodontics','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-005','Endodontic Irrigation Syringes','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-006','Rubber Dam Sheets','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-001','Alginate Impression Material','Impression','bag',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-002','PVS Impression Material','Impression','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-003','Bite Registration Material','Impression','cartridge',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-LAB-001','Dental Stone','Laboratory','bag',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-LAB-002','Cold-Cure Acrylic Resin','Laboratory','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-001','Sterilization Pouches - Small','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-002','Sterilization Pouches - Medium','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-003','Sterilization Pouches - Large','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-004','Chemical Indicator Tape','Infection control','roll',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-005','Biological Indicator Tests','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-006','Enzymatic Instrument Cleaner','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-007','Surface Disinfectant','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-008','Ultrasonic Cleaning Solution','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-001','Digital Sensor Sleeves','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-002','Phosphor Plate Barrier Envelopes','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-003','Intraoral X-Ray Film','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-001','Orthodontic Elastics','Orthodontics','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-002','Orthodontic Archwires','Orthodontics','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-003','Orthodontic Brackets','Orthodontics','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-001','Sutures','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-002','Hemostatic Sponges','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-003','Surgical Blades','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`category`=VALUES(`category`),`unit`=VALUES(`unit`),`status`='active';


-- Persistent internal in-house checkup catalog.
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Vital Signs Screening','Blood pressure, pulse, respiration, temperature, oxygen saturation, weight, and height screening before care.',15,0.00,0,0,'inhouse','dental_assistant',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Vital Signs Screening' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Blood Pressure Check','Focused blood pressure measurement and escalation of an abnormal reading.',10,0.00,0,0,'inhouse','dental_assistant',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Blood Pressure Check' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Weight and BMI Check','Weight, height, and body-mass-index screening.',10,0.00,0,0,'inhouse','staff',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Weight and BMI Check' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Blood Glucose Screening','Capillary blood glucose screening when offered by the clinic and permitted by policy.',15,0.00,0,0,'inhouse','dental_assistant',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Blood Glucose Screening' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Pre-treatment Health Screening','Non-diagnostic health screening before a dental procedure.',20,0.00,0,0,'inhouse','dental_assistant',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Pre-treatment Health Screening' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Medical Clearance Document Intake','Receipt and completeness review of a medical clearance document.',15,0.00,0,0,'inhouse','staff',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Medical Clearance Document Intake' AND provider_type='inhouse');
INSERT INTO `{{prefix}}service_catalog` (branch_id,name,description,duration_minutes,standard_fee,preparation_minutes,cleanup_minutes,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at)
SELECT NULL,'Post-treatment Monitoring','Short observation and vital-sign monitoring after treatment.',20,0.00,0,0,'inhouse','dental_assistant',1,0,0,1,NOW()
WHERE NOT EXISTS (SELECT 1 FROM `{{prefix}}service_catalog` WHERE name='Post-treatment Monitoring' AND provider_type='inhouse');
