-- Governed recurring Province forms. Submission payloads are stored separately
-- from status data so Province summaries never need to expose form contents.
CREATE TABLE IF NOT EXISTS province_form_definitions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(180) NOT NULL,
 form_kind ENUM('annual_return','almoner','administrative') NOT NULL,
 owner_role VARCHAR(60) NOT NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 order_key VARCHAR(60) NULL,
 unit_id BIGINT UNSIGNED NULL,
 due_rule_json JSON NOT NULL,
 required_fields_json JSON NOT NULL,
 reminder_schedule_json JSON NOT NULL,
 retention_months SMALLINT UNSIGNED NOT NULL DEFAULT 84,
 critical TINYINT(1) NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 version_no INT UNSIGNED NOT NULL DEFAULT 1,
 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_pfd_scope(province_id,active,order_key,unit_id),
 CONSTRAINT fk_pfd_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 CONSTRAINT fk_pfd_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_pfd_owner FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_pfd_creator FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_form_cycles (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 definition_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 period_key CHAR(7) NOT NULL,
 due_at DATETIME NOT NULL,
 status ENUM('outstanding','submitted','overdue','closed') NOT NULL DEFAULT 'outstanding',
 critical TINYINT(1) NOT NULL DEFAULT 0,
 last_reminder_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_form_cycle(definition_id,unit_id,period_key),
 KEY idx_pfc_queue(status,due_at,critical),
 KEY idx_pfc_unit(unit_id,status,due_at),
 CONSTRAINT fk_pfc_definition FOREIGN KEY(definition_id) REFERENCES province_form_definitions(id) ON DELETE RESTRICT,
 CONSTRAINT fk_pfc_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_form_submissions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cycle_id BIGINT UNSIGNED NOT NULL,
 version_no INT UNSIGNED NOT NULL,
 submission_cipher LONGTEXT NOT NULL,
 document_id BIGINT UNSIGNED NULL,
 submission_status ENUM('draft','submitted','correction_requested','superseded') NOT NULL DEFAULT 'draft',
 review_state ENUM('pending','accepted','changes_requested','rejected') NOT NULL DEFAULT 'pending',
 submitted_by BIGINT UNSIGNED NOT NULL,
 submitted_at DATETIME NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 review_note VARCHAR(500) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_province_form_submission_version(cycle_id,version_no),
 KEY idx_pfs_review(review_state,submitted_at),
 CONSTRAINT fk_pfs_cycle FOREIGN KEY(cycle_id) REFERENCES province_form_cycles(id) ON DELETE RESTRICT,
 CONSTRAINT fk_pfs_document FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE SET NULL,
 CONSTRAINT fk_pfs_submitter FOREIGN KEY(submitted_by) REFERENCES users(id),
 CONSTRAINT fk_pfs_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_form_audit (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cycle_id BIGINT UNSIGNED NULL,
 submission_id BIGINT UNSIGNED NULL,
 actor_id BIGINT UNSIGNED NULL,
 event_key ENUM('definition_created','cycle_created','submitted','correction_requested','accepted','rejected','reminder_queued') NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_pfa_cycle(cycle_id,created_at),
 CONSTRAINT fk_pfa_cycle FOREIGN KEY(cycle_id) REFERENCES province_form_cycles(id) ON DELETE SET NULL,
 CONSTRAINT fk_pfa_submission FOREIGN KEY(submission_id) REFERENCES province_form_submissions(id) ON DELETE SET NULL,
 CONSTRAINT fk_pfa_actor FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;