-- Baltique OS Core 0.66.0 — Clinical Documentation Compliance Center

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_checklists (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  checklist_key VARCHAR(120) NOT NULL,
  procedure_key VARCHAR(190) NULL,
  procedure_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_cdc_checklist_key (checklist_key),
  KEY idx_cdc_checklist_proc (procedure_key, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_checklist_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  checklist_id BIGINT UNSIGNED NOT NULL,
  item_code VARCHAR(120) NOT NULL,
  item_label VARCHAR(255) NOT NULL,
  document_family VARCHAR(120) NULL,
  required_stage VARCHAR(80) NULL,
  evidence_type VARCHAR(80) NOT NULL DEFAULT 'document',
  required_signoff_role VARCHAR(80) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  required_for_implant TINYINT(1) NOT NULL DEFAULT 0,
  required_for_general_anesthesia TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 100,
  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_cdc_checklist_item (checklist_id, item_code),
  KEY idx_cdc_item_family (document_family, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_case_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  procedure_key VARCHAR(190) NULL,
  checklist_id BIGINT UNSIGNED NULL,
  compliance_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  completed_count INT UNSIGNED NOT NULL DEFAULT 0,
  missing_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  risk_score INT UNSIGNED NOT NULL DEFAULT 0,
  legal_medical_risk_level VARCHAR(40) NOT NULL DEFAULT 'unknown',
  matrix_json JSON NULL,
  last_scan_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_cdc_case_status (operative_case_id),
  KEY idx_cdc_case_risk (legal_medical_risk_level, compliance_status),
  KEY idx_cdc_case_patient (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_gaps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_status_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  checklist_item_id BIGINT UNSIGNED NULL,
  item_code VARCHAR(120) NOT NULL,
  gap_type VARCHAR(80) NOT NULL DEFAULT 'missing',
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  responsible_role VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  due_at DATETIME NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_cdc_gap_case (operative_case_id, status, severity),
  KEY idx_cdc_gap_item (item_code, status),
  KEY idx_cdc_gap_role (responsible_role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  compliant_cases INT UNSIGNED NOT NULL DEFAULT 0,
  incomplete_cases INT UNSIGNED NOT NULL DEFAULT 0,
  high_risk_cases INT UNSIGNED NOT NULL DEFAULT 0,
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  missing_total INT UNSIGNED NOT NULL DEFAULT 0,
  compliance_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_cdc_report_month (report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_doc_compliance_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(80) NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_cdc_audit_case (operative_case_id, created_at),
  KEY idx_cdc_audit_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
