CREATE TABLE IF NOT EXISTS `{{prefix}}patient_intakes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `intake_type` ENUM('new_patient','returning_patient','walk_in','follow_up','recall') NOT NULL DEFAULT 'returning_patient',
  `chief_complaint` TEXT NULL,
  `history_present_condition` TEXT NULL,
  `allergies_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `allergy_changes` TEXT NULL,
  `medications_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `medication_changes` TEXT NULL,
  `medical_changes` TEXT NULL,
  `dental_history_changes` TEXT NULL,
  `pain_score` TINYINT UNSIGNED NULL,
  `consent_acknowledged` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('draft','complete','reviewed') NOT NULL DEFAULT 'draft',
  `completed_by` BIGINT UNSIGNED NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_intake_queue` (`queue_entry_id`),
  KEY `idx_intake_patient` (`patient_id`,`created_at`),
  KEY `idx_intake_appointment` (`appointment_id`),
  CONSTRAINT `{{prefix}}fk_intake_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_intake_request` FOREIGN KEY (`appointment_request_id`) REFERENCES `{{prefix}}appointment_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_user` FOREIGN KEY (`completed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_assessments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `extraoral_findings` TEXT NULL,
  `intraoral_findings` TEXT NULL,
  `soft_tissue_findings` TEXT NULL,
  `tmj_findings` TEXT NULL,
  `occlusion_findings` TEXT NULL,
  `oral_hygiene_status` VARCHAR(80) NULL,
  `radiographic_findings` TEXT NULL,
  `risk_assessment` TEXT NULL,
  `prognosis` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_assessment_encounter` (`encounter_id`),
  KEY `idx_assessment_patient` (`patient_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_assessment_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_assessment_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_assessment_user` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}diagnoses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `tooth_region` VARCHAR(80) NULL,
  `diagnosis_code` VARCHAR(80) NULL,
  `diagnosis_name` VARCHAR(255) NOT NULL,
  `severity` ENUM('mild','moderate','severe','critical') NULL,
  `diagnosis_status` ENUM('provisional','confirmed','resolved','chronic','under_observation','referred') NOT NULL DEFAULT 'confirmed',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `diagnosed_by` BIGINT UNSIGNED NULL,
  `diagnosed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_diagnosis_patient` (`patient_id`,`diagnosed_at`),
  KEY `idx_diagnosis_encounter` (`encounter_id`,`is_primary`),
  CONSTRAINT `{{prefix}}fk_diagnosis_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diagnosis_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diagnosis_user` FOREIGN KEY (`diagnosed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_workflow_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `stage_code` VARCHAR(60) NOT NULL,
  `stage_label` VARCHAR(150) NOT NULL,
  `details` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_workflow_patient` (`patient_id`,`created_at`),
  KEY `idx_workflow_appointment` (`appointment_id`,`created_at`),
  KEY `idx_workflow_queue` (`queue_entry_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_workflow_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_workflow_request` FOREIGN KEY (`appointment_request_id`) REFERENCES `{{prefix}}appointment_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_followup` FOREIGN KEY (`follow_up_id`) REFERENCES `{{prefix}}follow_ups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_user` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
