-- ---------------------------------------------------------------------------
-- Application Amendments feature
-- Table: application_amendment_request
--
-- Tracks an applicant's request to amend their (already submitted, not yet
-- responded to) application. Shared with the SAT legacy application, which
-- performs manual approval, records payment, and drives the approved -> ready
-- transition by writing status changes directly to this table.
--
-- Run manually against the `satdb_sat` database. Schema in this project is
-- maintained directly in MySQL (there is no migration runner).
--
-- personnel_id is intentionally NULL-able: it is populated only when a human
-- (via SAT) acts on the request. status values are used verbatim by both
-- systems; do not rename without coordinating with the SAT team.
-- ---------------------------------------------------------------------------

CREATE TABLE `application_amendment_request` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `person_id` INT(10) UNSIGNED NOT NULL,
  `applicant_id` INT(10) UNSIGNED NOT NULL,
  `personnel_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `status` ENUM('awaiting response','approved','denied','ready','done') NOT NULL,
  `request_timestamp` DATETIME NOT NULL,
  `response_timestamp` DATETIME NULL DEFAULT NULL,
  `paid_timestamp` DATETIME NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_aar_person` (`person_id`),
  KEY `idx_aar_applicant` (`applicant_id`),
  KEY `idx_aar_personnel` (`personnel_id`),
  KEY `idx_aar_status` (`status`),
  CONSTRAINT `fk_aar_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`personid`),
  CONSTRAINT `fk_aar_applicant` FOREIGN KEY (`applicant_id`) REFERENCES `applicant` (`applicantid`),
  CONSTRAINT `fk_aar_personnel` FOREIGN KEY (`personnel_id`) REFERENCES `person` (`personid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;