-- ============================================================
-- TTM_AUDIT_LOG — Safe migration (idempotent, run any time).
--
-- Handles two cases:
--   1. Table does not exist yet       → creates it from scratch.
--   2. Table exists with old schema   → adds any missing columns
--      (ENTITY_REF_ID, RETENTION_UNTIL, ACTOR_CLIENT_ID) and
--      backfills RETENTION_UNTIL for existing rows.
--
-- Usage (MySQL CLI):
--   mysql -u <user> -p <database_name> < db/TTM/migrate_audit_log.sql
-- ============================================================


-- ── STEP 1: Create table (skipped if it already exists) ─────────────
CREATE TABLE IF NOT EXISTS `TTM_AUDIT_LOG` (
  `ID`               bigint       NOT NULL AUTO_INCREMENT,
  `TS_MS`            bigint       NOT NULL            COMMENT 'Unix timestamp ms — precise event sequencing',
  `ACTOR_EMP_REF_ID` bigint       DEFAULT NULL        COMMENT 'FK → users.ID; NULL = system / cron action',
  `ACTOR_CLIENT_ID`  bigint       DEFAULT NULL        COMMENT 'FK → clients.ID; set for portal-client actions',
  `ACTION`           varchar(100) NOT NULL            COMMENT 'e.g. CREATE_TASK | APPROVE_TRANSFER | DELETE_CLIENT_FL_MAPPING',
  `ENTITY_TYPE`      varchar(60)  NOT NULL            COMMENT 'TASK | TASK_TRANSFER | DEACTIVATION_REQUEST | CLIENT_FL_MAPPING | TASK_COMMENT',
  `ENTITY_REF_ID`    bigint       DEFAULT NULL        COMMENT 'PK of the affected record',
  `BEFORE_JSON`      json         DEFAULT NULL        COMMENT 'Full state snapshot before change',
  `AFTER_JSON`       json         DEFAULT NULL        COMMENT 'Full state snapshot after change',
  `REASON`           text         DEFAULT NULL        COMMENT 'Optional human-readable reason',
  `IP_ADDRESS`       varchar(64)  DEFAULT NULL,
  `USER_AGENT`       varchar(255) DEFAULT NULL,
  `RETENTION_UNTIL`  date         DEFAULT NULL        COMMENT 'CREATED_AT + 7 years (SOC 2 / legal minimum)',
  `CREATED_AT`       datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_AUDIT_ACTOR`     (`ACTOR_EMP_REF_ID`),
  KEY `IDX_AUDIT_ENTITY`    (`ENTITY_TYPE`, `ENTITY_REF_ID`),
  KEY `IDX_AUDIT_ACTION`    (`ACTION`),
  KEY `IDX_AUDIT_RETENTION` (`RETENTION_UNTIL`),
  KEY `IDX_AUDIT_CREATED`   (`CREATED_AT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ── STEP 2: Patch existing table — add missing columns ───────────────
-- (ADD COLUMN IF NOT EXISTS is safe: silently skipped if already present)

ALTER TABLE `TTM_AUDIT_LOG`
  ADD COLUMN IF NOT EXISTS `ACTOR_CLIENT_ID`  bigint       DEFAULT NULL        COMMENT 'FK → clients.ID; set for portal-client actions',
  ADD COLUMN IF NOT EXISTS `ENTITY_REF_ID`    bigint       DEFAULT NULL        COMMENT 'PK of the affected record',
  ADD COLUMN IF NOT EXISTS `RETENTION_UNTIL`  date         DEFAULT NULL        COMMENT 'CREATED_AT + 7 years (SOC 2 / legal minimum)';


-- ── STEP 3: Backfill RETENTION_UNTIL for any existing rows ───────────
UPDATE `TTM_AUDIT_LOG`
   SET `RETENTION_UNTIL` = DATE_ADD(`CREATED_AT`, INTERVAL 7 YEAR)
 WHERE `RETENTION_UNTIL` IS NULL;


-- ── STEP 4: Add missing indexes (safe — skipped if already present) ──
CREATE INDEX IF NOT EXISTS `IDX_AUDIT_ACTOR`     ON `TTM_AUDIT_LOG` (`ACTOR_EMP_REF_ID`);
CREATE INDEX IF NOT EXISTS `IDX_AUDIT_ENTITY`    ON `TTM_AUDIT_LOG` (`ENTITY_TYPE`, `ENTITY_REF_ID`);
CREATE INDEX IF NOT EXISTS `IDX_AUDIT_ACTION`    ON `TTM_AUDIT_LOG` (`ACTION`);
CREATE INDEX IF NOT EXISTS `IDX_AUDIT_RETENTION` ON `TTM_AUDIT_LOG` (`RETENTION_UNTIL`);
CREATE INDEX IF NOT EXISTS `IDX_AUDIT_CREATED`   ON `TTM_AUDIT_LOG` (`CREATED_AT`);


-- ── Done ─────────────────────────────────────────────────────────────
-- After running this, every TTM action (create/update/delete task,
-- approve transfer, create mapping, etc.) will be logged automatically.
-- Check the Audit Logs page — records will start appearing immediately.
