CREATE TABLE IF NOT EXISTS `{{prefix}}patient_portal_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `request_type` VARCHAR(60) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `requested_start_at` DATETIME NULL,
  `requested_end_at` DATETIME NULL,
  `preferred_date` DATE NULL,
  `preferred_time_from` TIME NULL,
  `preferred_time_to` TIME NULL,
  `reason` VARCHAR(500) NULL,
  `payload_json` LONGTEXT NULL,
  `staff_note` TEXT NULL,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_portal_request_patient` (`patient_id`,`created_at`),
  KEY `idx_portal_request_status` (`status`,`request_type`,`created_at`),
  KEY `idx_portal_request_appointment` (`appointment_id`,`status`),
  KEY `idx_portal_request_followup` (`follow_up_id`,`status`),
  CONSTRAINT `{{prefix}}fk_portal_request_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_request_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_request_followup` FOREIGN KEY (`follow_up_id`) REFERENCES `{{prefix}}follow_ups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_request_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dental_condition_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'condition',
  `default_severity` VARCHAR(30) NULL,
  `surface_specific` TINYINT(1) NOT NULL DEFAULT 1,
  `description` TEXT NULL,
  `recommended_procedure` VARCHAR(190) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dental_condition_code` (`code`),
  KEY `idx_dental_condition_category` (`category`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dental_chart_reviews` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentition` ENUM('permanent','primary','mixed') NOT NULL DEFAULT 'permanent',
  `review_date` DATE NOT NULL,
  `caries_risk` VARCHAR(30) NULL,
  `periodontal_risk` VARCHAR(30) NULL,
  `oral_hygiene_status` VARCHAR(80) NULL,
  `occlusion_summary` VARCHAR(255) NULL,
  `clinical_summary` TEXT NULL,
  `status` ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
  `finalized_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_chart_review_patient` (`patient_id`,`review_date`),
  KEY `idx_chart_review_encounter` (`encounter_id`),
  CONSTRAINT `{{prefix}}fk_chart_review_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chart_review_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chart_review_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}dental_condition_catalog` (`code`,`name`,`category`,`default_severity`,`surface_specific`,`description`,`recommended_procedure`) VALUES
('sound','Sound tooth','condition','information',0,'No clinically significant finding.','Preventive care'),
('incipient_caries','Incipient caries','caries','mild',1,'Early enamel demineralization without cavitation.','Preventive or minimally invasive management'),
('enamel_caries','Enamel caries','caries','mild',1,'Caries clinically limited to enamel.','Preventive or restorative management'),
('dentinal_caries','Dentinal caries','caries','moderate',1,'Caries extending into dentin.','Caries removal and restoration'),
('deep_caries','Deep caries','caries','severe',1,'Deep lesion approaching the pulp.','Pulp assessment and definitive treatment'),
('recurrent_caries','Recurrent caries','caries','moderate',1,'Caries adjacent to an existing restoration.','Replace or repair restoration'),
('fractured','Fractured tooth','trauma','severe',1,'Loss of tooth structure due to fracture.','Restore, endodontic assessment, or extract as indicated'),
('wear_attrition','Attrition','wear','mild',1,'Tooth wear caused by tooth-to-tooth contact.','Occlusal assessment and protective management'),
('wear_abrasion','Abrasion','wear','mild',1,'Mechanical cervical tooth wear.','Risk-factor control and restoration if indicated'),
('wear_erosion','Erosion','wear','moderate',1,'Chemical loss of dental hard tissue.','Risk-factor control and restorative management'),
('mobility','Tooth mobility','periodontal','moderate',0,'Abnormal tooth mobility.','Periodontal assessment'),
('periapical_lesion','Periapical lesion','endodontic','severe',0,'Clinical or radiographic periapical pathology.','Endodontic evaluation'),
('pulpitis_suspected','Suspected pulpitis','endodontic','severe',0,'Symptoms or tests suggest pulpal inflammation.','Pulpal diagnosis and treatment'),
('root_fragment','Retained root fragment','surgical','severe',0,'Residual root structure without a maintainable crown.','Surgical assessment'),
('impacted','Impacted tooth','developmental','moderate',0,'Tooth prevented from normal eruption.','Surgical or orthodontic assessment'),
('unerupted','Unerupted tooth','developmental','information',0,'Tooth has not erupted.','Observe or investigate based on age and imaging'),
('missing','Missing tooth','missing','moderate',0,'Tooth absent from the arch.','Replacement-options assessment'),
('for_extraction','Indicated for extraction','surgical','severe',0,'Tooth judged non-restorable or otherwise indicated for removal.','Extraction'),
('under_observation','Under observation','monitoring','information',0,'Finding requires surveillance before definitive action.','Review appointment'),
('amalgam_restoration','Amalgam restoration','restoration','information',1,'Existing amalgam restoration.','Monitor, repair, or replace as indicated'),
('composite_restoration','Composite restoration','restoration','information',1,'Existing resin-composite restoration.','Monitor, repair, or replace as indicated'),
('temporary_restoration','Temporary restoration','restoration','moderate',1,'Interim restorative material.','Definitive restoration'),
('crown','Crown','prosthesis','information',0,'Full-coverage indirect restoration.','Monitor or replace as indicated'),
('pontic','Pontic','prosthesis','information',0,'Artificial tooth component of a fixed bridge.','Monitor prosthesis'),
('implant','Dental implant','prosthesis','information',0,'Implant-supported site.','Implant maintenance'),
('root_canal_treated','Root-canal treated','endodontic','information',0,'Previously completed endodontic treatment.','Monitor and restore as indicated'),
('completed_treatment','Completed treatment','completed','information',1,'Documented completion of a treatment-plan item.','Maintenance and review')
ON DUPLICATE KEY UPDATE name=VALUES(name),category=VALUES(category),default_severity=VALUES(default_severity),surface_specific=VALUES(surface_specific),description=VALUES(description),recommended_procedure=VALUES(recommended_procedure),is_active=1;
