-- =============================================================================
-- Audit trail (سجل التدقيق) — every database change made by the 2026-08-07 work
-- =============================================================================
--
-- SCOPE: this file contains ONLY the changes belonging to the audit-trail
-- feature. It does NOT include the 17 migrations that were already pending in
-- this repo before that work started (section_staff, section_student, the
-- attendance/memorization/grade re-keying, …) — those are unrelated, were
-- deliberately left un-run, and remain yours to decide on.
--
-- PREFERRED WAY TO APPLY THIS: run `php artisan migrate`. It performs exactly
-- what is below, in the same order, and records itself. This file exists for
-- environments where you apply schema changes by hand (a managed MySQL console,
-- a DBA-gated deploy) — in that case run EVERY section, including section 4,
-- or Laravel will try to run these migrations again on the next deploy.
--
-- Safe to run on a database that already holds audit rows: section 1 only ADDS
-- nullable columns, and sections 2–3 only fill values that are currently NULL.
-- Target: MySQL 8, InnoDB, utf8mb4.
--
-- Source of truth for each statement:
--   §1  database/migrations/2026_08_07_100000_add_context_columns_to_audit_logs_table.php
--   §2  database/migrations/2026_08_07_100001_backfill_action_on_legacy_audit_logs.php
--   §3  database/migrations/2026_08_07_100002_backfill_target_on_legacy_auth_audit_logs.php
-- =============================================================================

START TRANSACTION;

-- -----------------------------------------------------------------------------
-- §1  Schema — widen `audit_logs` from a free-text event line into a
--     structured, queryable record (which entity, what action, which fields).
--
--     The original columns keep their meaning, so every existing row stays
--     valid; the new columns are nullable and simply stay empty on those rows.
-- -----------------------------------------------------------------------------

ALTER TABLE `audit_logs`
  -- Model basename + primary key of the row that was written. Denormalised on
  -- purpose and deliberately NOT a foreign key: the trail must survive the
  -- entity's own deletion.
  ADD COLUMN `entity_type` VARCHAR(100) NULL AFTER `type`,
  ADD COLUMN `entity_id`   BIGINT UNSIGNED NULL AFTER `entity_type`,

  -- The verb alone (`create` / `update` / `delete` / `bulk-delete` / `login` …),
  -- so the UI can filter "everything deleted" across every entity at once.
  ADD COLUMN `action` VARCHAR(40) NULL AFTER `entity_id`,

  -- The actor's roles AT THE TIME, denormalised for the same reason as
  -- `actor_name`: a later role change must not rewrite history.
  ADD COLUMN `actor_role` VARCHAR(191) NULL AFTER `actor_name`,

  -- `{"field": {"old": …, "new": …}}` for an update, a redacted snapshot for a
  -- create/delete, `{"ids": [...], "count": n}` for a bulk-operation summary.
  ADD COLUMN `changes` JSON NULL AFTER `details`,

  ADD COLUMN `user_agent` TEXT NULL AFTER `ip`,

  ADD INDEX `audit_logs_entity_index` (`entity_type`, `entity_id`),
  ADD INDEX `audit_logs_action_index` (`action`);

-- -----------------------------------------------------------------------------
-- §2  Backfill — legacy rows written before the trail became structured.
--
--     Those rows only ever stored type/actor/target/details/ip, so on the audit
--     page they render with an empty العملية / العنصر. Both are recoverable from
--     the `type` literal they DO carry:
--       • `action`      — the suffix of `<domain>.<action>`, so the page's
--                         action filter covers the whole history, not just new rows
--       • `entity_type` — the model the legacy domain referred to
--
--     `actor_role`, `entity_id`, `changes` and `user_agent` are NOT recoverable:
--     they were never captured, and they stay NULL. That is the honest answer —
--     an audit trail must not invent history it did not witness.
--
--     The four domains below are the ONLY ones the pre-observer logger ever
--     emitted (auth / student / level / section); `auth` has no model behind it.
-- -----------------------------------------------------------------------------

-- `SUBSTRING(... LOCATE('.') + 1)`, not `SUBSTRING_INDEX(..., -1)`: the action is
-- everything after the FIRST dot, matching the PHP migration exactly. `LEFT(…,40)`
-- mirrors its truncation — the column is VARCHAR(40) and MySQL in strict mode
-- errors on an overflow rather than trimming it.
UPDATE `audit_logs`
SET `action` = LEFT(SUBSTRING(`type`, LOCATE('.', `type`) + 1), 40)
WHERE `action` IS NULL
  AND `type` LIKE '%.%';

UPDATE `audit_logs`
SET `entity_type` = CASE SUBSTRING_INDEX(`type`, '.', 1)
    WHEN 'student' THEN 'Student'
    WHEN 'level'   THEN 'Level'
    WHEN 'section' THEN 'Section'
    ELSE NULL
  END
WHERE `entity_type` IS NULL
  AND SUBSTRING_INDEX(`type`, '.', 1) IN ('student', 'level', 'section');

-- -----------------------------------------------------------------------------
-- §3  Backfill — the empty الهدف on legacy session rows.
--
--     The old logger recorded login/logout/password-reset with no `target`, so
--     those rows (the great majority of an existing trail) show a dash in the
--     audit page's target column. The current logger passes the account's name
--     as the target for exactly these events, so history is brought in line.
--
--     This derives nothing new: for a session event the subject IS the actor,
--     and the actor's name was already stored on the same row. Rows outside
--     `auth.*` are untouched — for those, an absent target genuinely means
--     "this record has no natural title".
-- -----------------------------------------------------------------------------

UPDATE `audit_logs`
SET `target` = `actor_name`
WHERE `target` IS NULL
  AND `type` LIKE 'auth.%'
  AND `actor_name` IS NOT NULL;

-- -----------------------------------------------------------------------------
-- §4  Register the migrations as applied.
--
--     REQUIRED when you ran §1–§3 by hand: without these rows the next
--     `php artisan migrate` re-runs them and §1 fails with "Duplicate column".
--     SKIP this section if you applied the change via `php artisan migrate`.
--
--     `batch` is set to one past the current maximum so the three land together
--     and `migrate:rollback` treats them as one step.
-- -----------------------------------------------------------------------------

INSERT INTO `migrations` (`migration`, `batch`)
SELECT v.`migration`, (SELECT COALESCE(MAX(`batch`), 0) + 1 FROM `migrations` m)
FROM (
  SELECT '2026_08_07_100000_add_context_columns_to_audit_logs_table' AS `migration`
  UNION ALL SELECT '2026_08_07_100001_backfill_action_on_legacy_audit_logs'
  UNION ALL SELECT '2026_08_07_100002_backfill_target_on_legacy_auth_audit_logs'
) AS v
WHERE NOT EXISTS (
  SELECT 1 FROM `migrations` m2 WHERE m2.`migration` = v.`migration`
);

COMMIT;

-- =============================================================================
-- §5  OPTIONAL — LOCAL DEV ONLY: remove the rows my verification runs created.
-- =============================================================================
--
-- While building this I exercised the trail end-to-end against the local `bilal`
-- database. Those calls were real, so they produced real rows: a throwaway
-- nationality ("Audit Probe AR") and bus ("AUDIT-BUS-1"), each created then
-- deleted, plus one deliberately failed login and one password-reset request
-- for addresses that do not exist.
--
-- They are accurate history, just history of MY testing rather than your
-- centre's operations. There is no delete endpoint for the trail by design, so
-- removing them is a manual decision — yours, not the application's.
--
-- Run this ONLY against your local database. It does not belong in production,
-- where these rows never existed. Review the SELECT first; it should return
-- exactly 9 rows, all obviously test data.
--
--   SELECT id, type, target, details, created_at FROM audit_logs
--   WHERE (entity_type = 'Nationality' AND target = 'Audit Probe AR')
--      OR (entity_type = 'Bus'         AND target = 'AUDIT-BUS-1')
--      OR (type = 'auth.login-failed'          AND target = 'no-such-user@example.com')
--      OR (type = 'auth.password-reset-request' AND target = 'nobody@example.com')
--      OR (type = 'nationality.bulk-delete'    AND JSON_CONTAINS(`changes`->'$.ids', '60'))
--   ORDER BY id;
--
-- Then, once the list looks right:
--
--   DELETE FROM audit_logs
--   WHERE (entity_type = 'Nationality' AND target = 'Audit Probe AR')
--      OR (entity_type = 'Bus'         AND target = 'AUDIT-BUS-1')
--      OR (type = 'auth.login-failed'          AND target = 'no-such-user@example.com')
--      OR (type = 'auth.password-reset-request' AND target = 'nobody@example.com')
--      OR (type = 'nationality.bulk-delete'    AND JSON_CONTAINS(`changes`->'$.ids', '60'));
--
-- NOTE: your own real sign-in from the same day (auth.login — المدير العام) is
-- NOT matched by these conditions and stays where it is.
-- =============================================================================