CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_record_completion_reviews` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `review_status` ENUM('complete','needs_review','schema_missing') NOT NULL DEFAULT 'needs_review',
  `missing_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `missing_json` LONGTEXT NULL,
  `checklist_json` LONGTEXT NULL,
  `review_hash` CHAR(64) NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_completion_encounter` (`encounter_id`,`reviewed_at`),
  KEY `idx_clinical_completion_patient` (`patient_id`,`review_status`,`reviewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}procedure_note_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_name` VARCHAR(160) NOT NULL,
  `procedure_keyword` VARCHAR(160) NULL,
  `template_body` TEXT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_procedure_note_template_name` (`template_name`),
  KEY `idx_procedure_note_template_active` (`is_active`,`template_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}treatment_plan_signatures` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `treatment_plan_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `signer_type` ENUM('patient','guardian','dentist','witness') NOT NULL DEFAULT 'patient',
  `signer_name` VARCHAR(190) NOT NULL,
  `signer_relationship` VARCHAR(120) NULL,
  `signature_method` ENUM('typed','uploaded','on_file') NOT NULL DEFAULT 'typed',
  `attestation_snapshot` TEXT NULL,
  `signature_hash` CHAR(64) NULL,
  `signed_by_user_id` BIGINT UNSIGNED NULL,
  `signed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_plan_signature_plan` (`treatment_plan_id`,`signer_type`,`signed_at`),
  KEY `idx_plan_signature_patient` (`patient_id`,`signed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
