-- Self-service account deletion (RGPD "droit à l'effacement") + grace period.
--
-- Two-phase lifecycle:
--   1. SOFT DELETE  — `user.deleted_at` is stamped the moment the owner
--      confirms deletion (password, or an emailed token for OAuth-only
--      accounts). All sessions are revoked, the token-resume path
--      (SessionService::authenticate) treats the account as gone (401), but
--      the row is kept intact for `ACCOUNT_DELETION_GRACE_DAYS`. Logging back
--      in during the window REACTIVATES the account (SessionService::create
--      clears `deleted_at`).
--   2. PURGE        — once `deleted_at` is older than the grace window, the
--      cron worker `bin/account-purge.php` erases the owner's medias (files +
--      DB + search) and ANONYMISES the `user` row (PII scrubbed), then stamps
--      `purged_at`. Purge is irreversible — past this point the account can no
--      longer be reactivated (email/username scrubbed ⇒ login can't find it).
--
-- We anonymise rather than `DELETE` the `user` row so existing foreign-key
-- references (medias already erased, but comments/reactions authored, etc.)
-- keep their integrity behind a neutral "Compte supprimé" tombstone. Full
-- physical row deletion would require verified ON DELETE CASCADE across every
-- child table of the externally-seeded `hxa` schema — out of scope for v1.

ALTER TABLE `user`
    ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `banned_until`,
    ADD COLUMN `purged_at`  DATETIME NULL DEFAULT NULL AFTER `deleted_at`,
    -- The purge worker scans `deleted_at < threshold AND purged_at IS NULL`;
    -- this composite index keeps that scan cheap as the tombstone set grows.
    ADD KEY `idx_user_purge` (`deleted_at`, `purged_at`);

-- One-shot deletion-confirmation token for accounts WITHOUT a usable password
-- (OAuth-registered users who never set one). Same token convention as the
-- newsletter / email-confirmation flows: 32 bytes of CSPRNG entropy
-- transported as base64url, only the SHA-256 binary hash is stored.
--
-- `user_id` is UNIQUE: a fresh request overwrites any pending token (upsert),
-- so an account can have at most one outstanding deletion link at a time.
CREATE TABLE `account_deletion_token` (
    `id`         BINARY(16)  NOT NULL,
    `user_id`    BINARY(16)  NOT NULL,
    `token_hash` BINARY(32)  NOT NULL,
    `expires_at` DATETIME    NOT NULL,
    `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_account_deletion_user`  (`user_id`),
    UNIQUE KEY `uniq_account_deletion_token` (`token_hash`),
    CONSTRAINT `fk_account_deletion_token_user`
        FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
