-- Governed Province communications, helpdesk tasks and operational digest rules.
CREATE TABLE IF NOT EXISTS province_group_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL,
 subject VARCHAR(250) NOT NULL, body_html MEDIUMTEXT NOT NULL,
 selected_units_json JSON NOT NULL, selected_roles_json JSON NOT NULL,
 status ENUM('draft','previewed','queued','cancelled') NOT NULL DEFAULT 'draft',
 previewed_at DATETIME NULL, queued_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pgm_queue(province_id,status,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_group_message_recipients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 person_id BIGINT UNSIGNED NOT NULL, recipient_email VARCHAR(254) NOT NULL,
 consent_basis ENUM('member_contact','general_consent') NOT NULL,
 status ENUM('previewed','queued','suppressed','sent','failed','bounced') NOT NULL DEFAULT 'previewed',
 suppression_reason VARCHAR(80) NULL, mail_queue_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pgm_recipient(message_id,unit_id,person_id), KEY idx_pgm_recipient_status(message_id,status),
 FOREIGN KEY(message_id) REFERENCES province_group_messages(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT,
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE RESTRICT,
 FOREIGN KEY(mail_queue_id) REFERENCES outbound_mail_queue(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL, person_id BIGINT UNSIGNED NULL,
 title VARCHAR(220) NOT NULL, description TEXT NULL, task_kind ENUM('helpdesk','operational','follow_up') NOT NULL DEFAULT 'helpdesk',
 status ENUM('open','in_progress','awaiting_unit','resolved','cancelled') NOT NULL DEFAULT 'open', priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 owner_user_id BIGINT UNSIGNED NULL, due_at DATETIME NULL, escalated_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pt_queue(province_id,status,due_at), KEY idx_pt_unit(unit_id,status), KEY idx_pt_owner(owner_user_id,status),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL,
 FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_task_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, task_id BIGINT UNSIGNED NOT NULL, note_text TEXT NOT NULL,
 visibility ENUM('province','unit') NOT NULL DEFAULT 'province', created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_ptn_task(task_id,created_at), FOREIGN KEY(task_id) REFERENCES province_tasks(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_alert_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL,
 event_key ENUM('task_overdue','task_escalated','forms_overdue','mail_delivery_failed') NOT NULL,
 recipient_role VARCHAR(60) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, daily_digest TINYINT(1) NOT NULL DEFAULT 1,
 last_digest_date DATE NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_par_rule(province_id,event_key,recipient_role), KEY idx_par_due(province_id,enabled,last_digest_date),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_operational_alerts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 event_key ENUM('task_overdue','task_escalated','forms_overdue','mail_delivery_failed') NOT NULL,
 source_type VARCHAR(40) NOT NULL, source_id BIGINT UNSIGNED NOT NULL, safe_summary VARCHAR(300) NOT NULL,
 occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, resolved_at DATETIME NULL,
 UNIQUE KEY uq_poa_source(province_id,event_key,source_type,source_id), KEY idx_poa_digest(province_id,event_key,occurred_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS province_communication_audit (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, actor_id BIGINT UNSIGNED NULL,
 action_key VARCHAR(80) NOT NULL, message_id BIGINT UNSIGNED NULL, task_id BIGINT UNSIGNED NULL, metadata_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_pca_province(province_id,created_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(message_id) REFERENCES province_group_messages(id) ON DELETE SET NULL, FOREIGN KEY(task_id) REFERENCES province_tasks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;