-- =============================================================================
-- Zimbabwe Cricket Safeguarding Digital Platform
-- Database schema — MySQL 8 / MariaDB, InnoDB, utf8mb4
-- =============================================================================
--
-- Conventions
--   * All PKs: BIGINT UNSIGNED AUTO_INCREMENT.
--   * created_at / updated_at on every mutable table; deleted_at (soft delete)
--     on tables where accidental hard deletion would be costly.
--   * Text fields holding safeguarding narrative content are named
--     `*_ciphertext` — the application encrypts these with AES-256-GCM
--     before writing (Master Prompt §45); the column itself is just TEXT/
--     MEDIUMTEXT/BLOB storage, encryption is handled in /app/services.
--   * Provinces are stored as VARCHAR(60), not an ENUM, so the list can be
--     maintained centrally in system_settings without a schema migration.
--   * `anonymous_reports` is a fully separate table from `case_reports` and
--     carries ZERO personally identifying columns — this is the anonymity
--     isolation the Master Prompt requires "by architecture, not a checkbox"
--     (§10, §46, §60). Never add name/email/phone columns to this table.
--
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================================================
-- 1. IDENTITY, ROLES & ACCESS CONTROL
-- =============================================================================

CREATE TABLE roles (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(80) NOT NULL UNIQUE,          -- e.g. 'Super Administrator', 'Safeguarding Officer'
  description  VARCHAR(255) NULL,
  is_system    TINYINT(1) NOT NULL DEFAULT 0,        -- system roles cannot be deleted via admin UI
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  permission_key  VARCHAR(100) NOT NULL UNIQUE,      -- e.g. 'cases.view', 'cases.assign', 'audit.export'
  description     VARCHAR(255) NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
  role_id        BIGINT UNSIGNED NOT NULL,
  permission_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role       FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid                 CHAR(36) NOT NULL UNIQUE,
  full_name            VARCHAR(150) NOT NULL,
  email                VARCHAR(190) NOT NULL UNIQUE,
  phone                VARCHAR(30) NULL,
  password_hash        VARCHAR(255) NOT NULL,        -- Argon2id
  province             VARCHAR(60) NULL,              -- for Provincial Chairman scoping
  status               ENUM('active','disabled','pending') NOT NULL DEFAULT 'pending',
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at        TIMESTAMP NULL,
  created_by           BIGINT UNSIGNED NULL,
  created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at           TIMESTAMP NULL,
  CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_roles (
  user_id      BIGINT UNSIGNED NOT NULL,
  role_id      BIGINT UNSIGNED NOT NULL,
  assigned_by  BIGINT UNSIGNED NULL,
  assigned_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user   FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role   FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_by     FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per user. Recovery codes stored as a JSON array of hashes (each
-- one-time-use, marked consumed at the app layer) to avoid an extra table
-- for a small, rarely-queried set.
CREATE TABLE two_factor_auth (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id             BIGINT UNSIGNED NOT NULL UNIQUE,
  totp_secret_encrypted VARBINARY(255) NOT NULL,
  recovery_codes_json JSON NULL,                     -- [{"hash":"...","used_at":null}, ...]
  is_enabled          TINYINT(1) NOT NULL DEFAULT 0,
  enrolled_at         TIMESTAMP NULL,
  last_verified_at    TIMESTAMP NULL,
  created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_2fa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_attempts (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id            BIGINT UNSIGNED NULL,
  username_attempted VARCHAR(190) NULL,
  ip_address         VARBINARY(16) NULL,              -- INET6_ATON()
  user_agent         VARCHAR(255) NULL,
  success            TINYINT(1) NOT NULL,
  failure_reason     VARCHAR(100) NULL,               -- 'bad_password','2fa_failed','locked', etc.
  attempted_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_la_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_la_user_time (user_id, attempted_at),
  INDEX idx_la_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sessions (
  id            CHAR(64) PRIMARY KEY,                 -- secure random session id
  user_id       BIGINT UNSIGNED NOT NULL,
  ip_address    VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  last_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    TIMESTAMP NOT NULL,
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sessions_user (user_id),
  INDEX idx_sessions_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 2. SAFEGUARDING CASE MANAGEMENT (confidential / identified path)
-- =============================================================================

-- Case-level container. Deliberately holds NO reporter PII — that lives only
-- in case_reports (confidential) and never in anonymous_reports (anonymous).
CREATE TABLE safeguarding_cases (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_number          VARCHAR(20) NOT NULL UNIQUE,   -- cryptographically random, not sequential
  is_anonymous         TINYINT(1) NOT NULL DEFAULT 0,
  category             VARCHAR(80) NOT NULL,          -- abuse / neglect / harassment / grooming / etc.
  status               VARCHAR(40) NOT NULL DEFAULT 'new', -- configurable list, validated in app layer
  risk_level           ENUM('low','medium','high','critical') NULL,
  incident_date_text   VARCHAR(100) NULL,             -- free text: exact or approximate, as reported
  is_ongoing           TINYINT(1) NOT NULL DEFAULT 0,
  location             VARCHAR(255) NULL,
  province             VARCHAR(60) NULL,
  description_ciphertext MEDIUMTEXT NULL,             -- AES-256-GCM ciphertext
  current_risk_flag    ENUM('yes','no','unsure') NULL,
  assigned_officer_id  BIGINT UNSIGNED NULL,
  received_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at            TIMESTAMP NULL,
  closed_by            BIGINT UNSIGNED NULL,
  outcome_summary      VARCHAR(500) NULL,
  created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at           TIMESTAMP NULL,
  CONSTRAINT fk_case_officer FOREIGN KEY (assigned_officer_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_case_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_case_status (status),
  INDEX idx_case_risk (risk_level),
  INDEX idx_case_province (province),
  INDEX idx_case_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Raw confidential intake. One case can accumulate more than one report
-- (e.g. duplicate or follow-on reports merged by the Safeguarding Officer).
CREATE TABLE case_reports (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id                BIGINT UNSIGNED NULL,        -- null until triaged/linked
  channel                ENUM('web','whatsapp','in_person','email','phone') NOT NULL DEFAULT 'web',
  reporter_name          VARCHAR(150) NULL,
  reporter_email         VARCHAR(190) NULL,
  reporter_phone         VARCHAR(30) NULL,
  reporter_relationship  VARCHAR(60) NULL,
  affected_party_text    VARCHAR(255) NULL,
  concern_type           VARCHAR(80) NOT NULL,
  incident_date_text     VARCHAR(100) NULL,
  is_ongoing             TINYINT(1) NOT NULL DEFAULT 0,
  location               VARCHAR(255) NULL,
  province                VARCHAR(60) NULL,
  description_ciphertext MEDIUMTEXT NULL,
  witnesses_text          VARCHAR(500) NULL,
  current_risk            ENUM('yes','no','unsure') NULL,
  ip_hash                 CHAR(64) NULL,               -- salted hash, isolated from case handlers' view
  submitted_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_creport_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  INDEX idx_creport_case (case_id),
  INDEX idx_creport_submitted (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Free-form narrative timeline entries (distinct from status transitions,
-- which are recorded in case_status_history).
CREATE TABLE case_updates (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id      BIGINT UNSIGNED NOT NULL,
  update_type  VARCHAR(60) NOT NULL,                  -- 'general','review','follow_up', etc.
  summary_ciphertext MEDIUMTEXT NOT NULL,
  created_by   BIGINT UNSIGNED NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cupdate_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_cupdate_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_cupdate_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Status transitions — append-only, never overwritten (Master Prompt §25, §28).
CREATE TABLE case_status_history (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id     BIGINT UNSIGNED NOT NULL,
  old_status  VARCHAR(40) NULL,
  new_status  VARCHAR(40) NOT NULL,
  changed_by  BIGINT UNSIGNED NOT NULL,
  reason      VARCHAR(255) NULL,
  changed_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_csh_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_csh_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_csh_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Officer assignment history — kept append-only with is_current, so
-- reassignment never destroys who handled a case and when.
CREATE TABLE case_assignments (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id       BIGINT UNSIGNED NOT NULL,
  assigned_to   BIGINT UNSIGNED NOT NULL,
  assigned_by   BIGINT UNSIGNED NOT NULL,
  assigned_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unassigned_at TIMESTAMP NULL,
  is_current    TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_casn_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_casn_to   FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_casn_by   FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_casn_case_current (case_id, is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-case risk classification log — historical decisions are never
-- overwritten (Master Prompt §26); the current one is flagged.
CREATE TABLE case_risk_assessments (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id        BIGINT UNSIGNED NOT NULL,
  risk_level     ENUM('low','medium','high','critical') NOT NULL,
  classified_by  BIGINT UNSIGNED NOT NULL,
  reason         VARCHAR(500) NULL,
  is_current     TINYINT(1) NOT NULL DEFAULT 1,
  classified_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cra_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_cra_user FOREIGN KEY (classified_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_cra_case_current (case_id, is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- External / internal escalation contacts referenced by case_referrals.
CREATE TABLE safeguarding_contacts (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organisation  VARCHAR(150) NOT NULL,               -- 'Zimbabwe Republic Police', 'ICC Safeguarding Unit', ...
  contact_type  ENUM('police','social_welfare','icc','regulatory','internal','other') NOT NULL,
  contact_name  VARCHAR(150) NULL,
  phone         VARCHAR(30) NULL,
  email         VARCHAR(190) NULL,
  province      VARCHAR(60) NULL,                    -- for province-specific police/social-welfare contacts
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE case_referrals (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id       BIGINT UNSIGNED NOT NULL,
  contact_id    BIGINT UNSIGNED NULL,
  referral_type ENUM('police','social_welfare','icc','regulatory','other') NOT NULL,
  referred_by   BIGINT UNSIGNED NOT NULL,
  referred_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes         VARCHAR(500) NULL,
  outcome       VARCHAR(500) NULL,
  CONSTRAINT fk_referral_case    FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_referral_contact FOREIGN KEY (contact_id) REFERENCES safeguarding_contacts(id) ON DELETE SET NULL,
  CONSTRAINT fk_referral_user    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_referral_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE case_actions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id      BIGINT UNSIGNED NOT NULL,
  action_type  VARCHAR(80) NOT NULL,
  description  VARCHAR(500) NOT NULL,
  performed_by BIGINT UNSIGNED NOT NULL,
  due_date     DATE NULL,
  status       ENUM('pending','in_progress','completed') NOT NULL DEFAULT 'pending',
  performed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_caction_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_caction_user FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_caction_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE case_notes (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id         BIGINT UNSIGNED NOT NULL,
  author_id       BIGINT UNSIGNED NOT NULL,
  note_ciphertext MEDIUMTEXT NOT NULL,
  is_confidential TINYINT(1) NOT NULL DEFAULT 1,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cnote_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_cnote_user FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_cnote_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Logged external communications about a case (calls, emails, in-person) —
-- distinct from anonymous_followups, which is the anonymous two-way portal.
CREATE TABLE case_communications (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id          BIGINT UNSIGNED NOT NULL,
  direction        ENUM('inbound','outbound') NOT NULL,
  channel          ENUM('email','phone','in_person','whatsapp','portal') NOT NULL,
  summary_ciphertext MEDIUMTEXT NOT NULL,
  communicated_by  BIGINT UNSIGNED NULL,
  communicated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ccomm_case FOREIGN KEY (case_id) REFERENCES safeguarding_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_ccomm_user FOREIGN KEY (communicated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_ccomm_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 3. ANONYMOUS REPORTING (architecturally isolated — zero PII columns)
-- =============================================================================

CREATE TABLE anonymous_reports (
  id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_reference_hash     CHAR(64) NOT NULL UNIQUE,   -- SHA-256 of the reference shown once to the reporter
  follow_up_code_hash     CHAR(64) NOT NULL UNIQUE,   -- SHA-256 of the follow-up code shown once to the reporter
  concern_type            VARCHAR(80) NOT NULL,
  affected_party_text     VARCHAR(255) NULL,
  incident_date_text      VARCHAR(100) NULL,
  is_ongoing              TINYINT(1) NOT NULL DEFAULT 0,
  location                VARCHAR(255) NULL,
  province                VARCHAR(60) NULL,
  description_ciphertext  MEDIUMTEXT NULL,
  witnesses_text          VARCHAR(500) NULL,
  current_risk            ENUM('yes','no','unsure') NULL,
  linked_case_id          BIGINT UNSIGNED NULL,       -- populated once triaged into safeguarding_cases
  status                  VARCHAR(40) NOT NULL DEFAULT 'received',
  submitted_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_activity_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_anon_linked_case FOREIGN KEY (linked_case_id) REFERENCES safeguarding_cases(id) ON DELETE SET NULL,
  INDEX idx_anon_status (status)
  -- Deliberately NO index on any free-text column that could be used to
  -- correlate reports to a person; NO ip_address column at all.
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Two-way anonymous communication thread. Neither party's identity is
-- resolved from this table — the reporter authenticates only with their
-- case reference + follow-up code, never a login.
CREATE TABLE anonymous_followups (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  anonymous_report_id BIGINT UNSIGNED NOT NULL,
  sender_type         ENUM('reporter','safeguarding_team') NOT NULL,
  message_ciphertext  MEDIUMTEXT NOT NULL,
  created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  read_at             TIMESTAMP NULL,
  CONSTRAINT fk_followup_report FOREIGN KEY (anonymous_report_id) REFERENCES anonymous_reports(id) ON DELETE CASCADE,
  INDEX idx_followup_report (anonymous_report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 4. EVIDENCE MANAGEMENT
-- =============================================================================

CREATE TABLE evidence_files (
  id                        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uploadable_type           ENUM('case_report','anonymous_report','safeguarding_case') NOT NULL,
  uploadable_id             BIGINT UNSIGNED NOT NULL,
  stored_filename           CHAR(36) NOT NULL,        -- UUID + extension; never the original name
  original_filename_ciphertext VARBINARY(500) NULL,   -- only retained for confidential reports, encrypted
  mime_type                 VARCHAR(100) NOT NULL,
  size_bytes                BIGINT UNSIGNED NOT NULL,
  storage_path              VARCHAR(500) NOT NULL,    -- above web root, e.g. /storage/evidence/{uuid}
  checksum_sha256            CHAR(64) NOT NULL,
  is_anonymous               TINYINT(1) NOT NULL DEFAULT 0,
  uploaded_by_user_id         BIGINT UNSIGNED NULL,    -- null for public/anonymous submissions
  virus_scan_status           ENUM('pending','clean','flagged') NOT NULL DEFAULT 'pending',
  created_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_evidence_user FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_evidence_uploadable (uploadable_type, uploadable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every evidence download must be audited (Master Prompt §29) — see
-- audit_logs, entity_type = 'evidence_files'.

-- =============================================================================
-- 5. NOTIFICATIONS & WHATSAPP (UltraMsg)
-- =============================================================================

CREATE TABLE notification_templates (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_key   VARCHAR(80) NOT NULL UNIQUE,        -- 'new_report_alert','case_assignment', ...
  channel        ENUM('whatsapp','email','sms') NOT NULL,
  subject        VARCHAR(190) NULL,                  -- email only
  body_template  VARCHAR(1000) NOT NULL,             -- may include {{case_reference}} style tokens, never raw case detail
  is_active      TINYINT(1) NOT NULL DEFAULT 1,
  updated_by     BIGINT UNSIGNED NULL,
  updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ntpl_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  type            VARCHAR(60) NOT NULL,
  title           VARCHAR(190) NOT NULL,
  body            VARCHAR(500) NULL,
  related_case_id BIGINT UNSIGNED NULL,
  is_read         TINYINT(1) NOT NULL DEFAULT 0,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notif_case FOREIGN KEY (related_case_id) REFERENCES safeguarding_cases(id) ON DELETE SET NULL,
  INDEX idx_notif_user_read (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE whatsapp_messages (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  direction         ENUM('inbound','outbound') NOT NULL,
  whatsapp_number   VARCHAR(30) NOT NULL,             -- operationally required for this channel; access restricted
  template_id       BIGINT UNSIGNED NULL,
  related_case_id   BIGINT UNSIGNED NULL,
  message_body      VARCHAR(1000) NOT NULL,
  ultramsg_message_id VARCHAR(100) NULL,
  status            ENUM('queued','sent','delivered','read','failed') NOT NULL DEFAULT 'queued',
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_wa_template FOREIGN KEY (template_id) REFERENCES notification_templates(id) ON DELETE SET NULL,
  CONSTRAINT fk_wa_case FOREIGN KEY (related_case_id) REFERENCES safeguarding_cases(id) ON DELETE SET NULL,
  INDEX idx_wa_case (related_case_id),
  INDEX idx_wa_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE whatsapp_delivery_logs (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  whatsapp_message_id BIGINT UNSIGNED NOT NULL,
  status             ENUM('sent','delivered','read','failed') NOT NULL,
  status_detail      VARCHAR(255) NULL,
  logged_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_wadl_message FOREIGN KEY (whatsapp_message_id) REFERENCES whatsapp_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 6. DOCUMENTS & POLICY VERSION CONTROL
-- =============================================================================

CREATE TABLE documents (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(190) NOT NULL,
  category     ENUM('policy','resource','induction_pack','training_material','report_export','other') NOT NULL,
  file_path    VARCHAR(500) NOT NULL,
  mime_type    VARCHAR(100) NOT NULL,
  size_bytes   BIGINT UNSIGNED NOT NULL,
  uploaded_by  BIGINT UNSIGNED NOT NULL,
  is_public    TINYINT(1) NOT NULL DEFAULT 0,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_doc_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_doc_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE policy_versions (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  version_label VARCHAR(40) NOT NULL,
  document_id   BIGINT UNSIGNED NOT NULL,
  effective_date DATE NULL,
  is_current    TINYINT(1) NOT NULL DEFAULT 0,
  adopted_by    BIGINT UNSIGNED NULL,                 -- Board adoption reference
  adopted_at    TIMESTAMP NULL,
  archived_at   TIMESTAMP NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pv_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pv_user FOREIGN KEY (adopted_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_pv_current (is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 7. TRAINING & AWARENESS
-- =============================================================================

CREATE TABLE training_courses (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title           VARCHAR(190) NOT NULL,
  description     VARCHAR(500) NULL,
  delivery_format ENUM('workshop','online','roadshow','induction') NOT NULL DEFAULT 'workshop',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE training_records (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  course_id       BIGINT UNSIGNED NOT NULL,
  status          ENUM('not_started','in_progress','completed') NOT NULL DEFAULT 'not_started',
  completed_at    TIMESTAMP NULL,
  certificate_path VARCHAR(500) NULL,
  expires_at      DATE NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tr_user   FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_tr_course FOREIGN KEY (course_id) REFERENCES training_courses(id) ON DELETE CASCADE,
  UNIQUE KEY uq_user_course (user_id, course_id),
  INDEX idx_tr_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 8. RISK REGISTER (organisational) & GENERAL INCIDENTS
-- =============================================================================

CREATE TABLE risk_register (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title             VARCHAR(190) NOT NULL,
  description       VARCHAR(1000) NULL,
  location          VARCHAR(255) NULL,
  affected_group    VARCHAR(190) NULL,
  likelihood        TINYINT UNSIGNED NOT NULL,        -- 1–5
  impact            TINYINT UNSIGNED NOT NULL,        -- 1–5
  risk_score        TINYINT UNSIGNED GENERATED ALWAYS AS (likelihood * impact) STORED,
  existing_controls VARCHAR(1000) NULL,
  mitigation        VARCHAR(1000) NULL,
  responsible_user_id BIGINT UNSIGNED NULL,
  review_date       DATE NULL,
  status            ENUM('open','monitoring','closed') NOT NULL DEFAULT 'open',
  created_by        BIGINT UNSIGNED NOT NULL,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_risk_responsible FOREIGN KEY (responsible_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_risk_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_risk_status (status),
  INDEX idx_risk_score (risk_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historical trend of assessments against a risk_register entry.
CREATE TABLE risk_assessments (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  risk_register_id BIGINT UNSIGNED NOT NULL,
  assessed_by      BIGINT UNSIGNED NOT NULL,
  likelihood       TINYINT UNSIGNED NOT NULL,
  impact           TINYINT UNSIGNED NOT NULL,
  score            TINYINT UNSIGNED GENERATED ALWAYS AS (likelihood * impact) STORED,
  notes            VARCHAR(500) NULL,
  assessed_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ra_risk FOREIGN KEY (risk_register_id) REFERENCES risk_register(id) ON DELETE CASCADE,
  CONSTRAINT fk_ra_user FOREIGN KEY (assessed_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_ra_risk (risk_register_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- General safety/security incident log — narrower than a full safeguarding
-- case, escalatable into one via related_case_id.
CREATE TABLE incidents (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title           VARCHAR(190) NOT NULL,
  description     VARCHAR(1000) NULL,
  incident_type   VARCHAR(80) NOT NULL,
  severity        ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
  location        VARCHAR(255) NULL,
  occurred_at     TIMESTAMP NOT NULL,
  reported_by     BIGINT UNSIGNED NULL,
  related_case_id BIGINT UNSIGNED NULL,
  status          ENUM('open','under_review','closed') NOT NULL DEFAULT 'open',
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_incident_user FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_incident_case FOREIGN KEY (related_case_id) REFERENCES safeguarding_cases(id) ON DELETE SET NULL,
  INDEX idx_incident_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 9. AUDIT TRAIL (append-only)
-- =============================================================================

CREATE TABLE audit_logs (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           BIGINT UNSIGNED NULL,             -- null for automated/system actions
  user_role_snapshot VARCHAR(80) NULL,                -- role at time of action, in case roles later change
  action            VARCHAR(100) NOT NULL,            -- 'case.viewed','case.assigned','risk.changed', ...
  entity_type       VARCHAR(60) NOT NULL,
  entity_id         VARCHAR(60) NULL,
  previous_value    JSON NULL,
  new_value         JSON NULL,
  ip_address        VARBINARY(16) NULL,
  occurred_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_entity (entity_type, entity_id),
  INDEX idx_audit_time (occurred_at),
  INDEX idx_audit_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Application layer only ever INSERTs into this table. Grant the app's
-- database user INSERT + SELECT but not UPDATE/DELETE on audit_logs.

-- =============================================================================
-- 10. PRIVACY-CONSCIOUS WEBSITE ANALYTICS (kept fully separate from
--     safeguarding case data — Master Prompt §35, §36, §60)
-- =============================================================================

CREATE TABLE website_visitors (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visitor_hash  CHAR(64) NOT NULL UNIQUE,             -- salted hash of IP+UA, rotated daily; never raw IP
  country       VARCHAR(80) NULL,
  region        VARCHAR(80) NULL,
  city          VARCHAR(80) NULL,
  device_type   VARCHAR(30) NULL,
  browser       VARCHAR(60) NULL,
  first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE visitor_sessions (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visitor_id         BIGINT UNSIGNED NOT NULL,
  session_start      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  session_end        TIMESTAMP NULL,
  entry_page         VARCHAR(255) NULL,
  referrer_source    VARCHAR(190) NULL,
  page_view_count    INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_vsession_visitor FOREIGN KEY (visitor_id) REFERENCES website_visitors(id) ON DELETE CASCADE,
  INDEX idx_vsession_visitor (visitor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE visitor_events (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visitor_session_id BIGINT UNSIGNED NOT NULL,
  event_type         ENUM('page_view','policy_download','whatsapp_cta_click','report_start','report_submit') NOT NULL,
  page_path          VARCHAR(255) NULL,
  occurred_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_vevent_session FOREIGN KEY (visitor_session_id) REFERENCES visitor_sessions(id) ON DELETE CASCADE,
  INDEX idx_vevent_session (visitor_session_id),
  INDEX idx_vevent_type_time (event_type, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pre-aggregated daily rollups for fast dashboard rendering.
CREATE TABLE geographic_statistics (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stat_date             DATE NOT NULL,
  country               VARCHAR(80) NOT NULL,
  region                VARCHAR(80) NULL,
  visitor_count         INT UNSIGNED NOT NULL DEFAULT 0,
  unique_visitor_count  INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_geo_stat (stat_date, country, region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 11. SYSTEM CONFIGURATION
-- =============================================================================

CREATE TABLE system_settings (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key   VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  is_sensitive  TINYINT(1) NOT NULL DEFAULT 0,        -- true = mask in admin UI (e.g. UltraMsg token)
  updated_by    BIGINT UNSIGNED NULL,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- 12. SEED DATA (roles + case-critical settings placeholders only —
--     no demo safeguarding cases; see Master Prompt §71 on demo data)
-- =============================================================================

INSERT INTO roles (name, description, is_system) VALUES
  ('Super Administrator', 'Full system administration', 1),
  ('Safeguarding Officer', 'Full safeguarding case management', 1),
  ('Case Manager', 'Manage assigned cases', 1),
  ('Safeguarding Reviewer', 'Review cases and reports', 1),
  ('Executive Leadership', 'View approved dashboards and reports', 1),
  ('Auditor', 'Read-only audit access', 1),
  ('Content Administrator', 'Manage website content and policy documents', 1),
  ('Analytics Administrator', 'Access visitor analytics', 1);

INSERT INTO system_settings (setting_key, setting_value, is_sensitive) VALUES
  ('org_name', 'Zimbabwe Cricket', 0),
  ('data_retention_period', 'CONFIGURATION REQUIRED FROM ZIMBABWE CRICKET', 0),
  ('emergency_contact_line', 'CONFIGURATION REQUIRED FROM ZIMBABWE CRICKET', 0),
  ('safeguarding_officer_contact', 'CONFIGURATION REQUIRED FROM ZIMBABWE CRICKET', 0),
  ('ultramsg_instance_id', NULL, 1),
  ('ultramsg_api_token', NULL, 1),
  ('ultramsg_whatsapp_number', 'CONFIGURATION REQUIRED FROM ZIMBABWE CRICKET', 0);

SET FOREIGN_KEY_CHECKS = 1;