SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE return_template_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  province_id BIGINT UNSIGNED NOT NULL,
  order_key VARCHAR(40) NOT NULL,
  return_type ENUM('annual','installation') NOT NULL,
  version_no INT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  terminology_json JSON NOT NULL,
  field_config_json JSON NOT NULL,
  supports_pdf TINYINT(1) NOT NULL DEFAULT 0,
  supports_csv TINYINT(1) NOT NULL DEFAULT 1,
  supports_xlsx TINYINT(1) NOT NULL DEFAULT 0,
  requirements_verified_at DATETIME NULL,
  requirements_source VARCHAR(500) NULL,
  effective_from DATE NULL,
  effective_to DATE NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_return_template_version(province_id,order_key,return_type,version_no),
  KEY idx_return_template_lookup(province_id,order_key,return_type,active,effective_from),
  CONSTRAINT fk_return_template_province FOREIGN KEY(province_id) REFERENCES provinces(id),
  CONSTRAINT fk_return_template_actor FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE unit_returns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id BIGINT UNSIGNED NOT NULL,
  province_id BIGINT UNSIGNED NOT NULL,
  template_version_id BIGINT UNSIGNED NOT NULL,
  parent_return_id BIGINT UNSIGNED NULL,
  amendment_no INT UNSIGNED NOT NULL DEFAULT 0,
  return_type ENUM('annual','installation') NOT NULL,
  order_key VARCHAR(40) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  installation_date DATE NULL,
  status ENUM('draft','review','signed','locked','superseded') NOT NULL DEFAULT 'draft',
  revision_no INT UNSIGNED NOT NULL DEFAULT 1,
  working_payload_json JSON NOT NULL,
  source_fingerprint CHAR(64) NOT NULL,
  missing_flags_json JSON NOT NULL,
  validation_json JSON NOT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  signed_by BIGINT UNSIGNED NULL,
  signed_at DATETIME NULL,
  locked_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_unit_return_amendment(unit_id,return_type,order_key,period_start,period_end,amendment_no),
  KEY idx_unit_return_province(province_id,status,period_end),
  CONSTRAINT fk_unit_return_unit FOREIGN KEY(unit_id) REFERENCES units(id),
  CONSTRAINT fk_unit_return_province FOREIGN KEY(province_id) REFERENCES provinces(id),
  CONSTRAINT fk_unit_return_template FOREIGN KEY(template_version_id) REFERENCES return_template_versions(id),
  CONSTRAINT fk_unit_return_parent FOREIGN KEY(parent_return_id) REFERENCES unit_returns(id),
  CONSTRAINT fk_unit_return_creator FOREIGN KEY(created_by) REFERENCES users(id),
  CONSTRAINT fk_unit_return_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id),
  CONSTRAINT fk_unit_return_signer FOREIGN KEY(signed_by) REFERENCES users(id),
  CONSTRAINT chk_unit_return_period CHECK(period_end>=period_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE return_final_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  return_id BIGINT UNSIGNED NOT NULL,
  snapshot_no INT UNSIGNED NOT NULL,
  payload_json JSON NOT NULL,
  source_fingerprint CHAR(64) NOT NULL,
  template_version_id BIGINT UNSIGNED NOT NULL,
  signed_by BIGINT UNSIGNED NOT NULL,
  signed_at DATETIME NOT NULL,
  locked_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_return_snapshot(return_id,snapshot_no),
  CONSTRAINT fk_return_snapshot_return FOREIGN KEY(return_id) REFERENCES unit_returns(id),
  CONSTRAINT fk_return_snapshot_template FOREIGN KEY(template_version_id) REFERENCES return_template_versions(id),
  CONSTRAINT fk_return_snapshot_signer FOREIGN KEY(signed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE return_submissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  snapshot_id BIGINT UNSIGNED NOT NULL,
  status ENUM('not_submitted','submitted','acknowledged','rejected','withdrawn') NOT NULL DEFAULT 'not_submitted',
  submitted_at DATETIME NULL,
  acknowledged_at DATETIME NULL,
  acknowledgement_reference VARCHAR(190) NULL,
  note VARCHAR(1000) NULL,
  updated_by BIGINT UNSIGNED NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_return_submission(snapshot_id),
  CONSTRAINT fk_return_submission_snapshot FOREIGN KEY(snapshot_id) REFERENCES return_final_snapshots(id),
  CONSTRAINT fk_return_submission_actor FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE return_export_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  snapshot_id BIGINT UNSIGNED NOT NULL,
  format ENUM('pdf','csv','xlsx') NOT NULL,
  exported_by BIGINT UNSIGNED NOT NULL,
  exported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_return_export_snapshot(snapshot_id,exported_at),
  CONSTRAINT fk_return_export_snapshot FOREIGN KEY(snapshot_id) REFERENCES return_final_snapshots(id),
  CONSTRAINT fk_return_export_actor FOREIGN KEY(exported_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER return_final_snapshot_no_update BEFORE UPDATE ON return_final_snapshots FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Final return snapshots are immutable';
CREATE TRIGGER return_final_snapshot_no_delete BEFORE DELETE ON return_final_snapshots FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Final return snapshots are immutable';
CREATE TRIGGER return_export_audit_no_update BEFORE UPDATE ON return_export_audit FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Return export audit is append-only';
CREATE TRIGGER return_export_audit_no_delete BEFORE DELETE ON return_export_audit FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Return export audit is append-only';

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609280034','Versioned annual and installation returns',SHA2('202609280034_statutory_returns_v1',256),0);
COMMIT;