-- Apply once after verified 001-021 on a backed-up installation.
-- Existing accounts remain usable. TOTP becomes mandatory for an account only
-- after that account confirms enrolment. No passwords or legacy PIN flags change.
ALTER TABLE user_memberships
 ADD COLUMN expires_at DATETIME NULL,
 ADD COLUMN revoked_at DATETIME NULL,
 ADD COLUMN last_used_at DATETIME NULL,
 ADD COLUMN reviewed_at DATETIME NULL,
 ADD COLUMN review_due_at DATETIME NULL,
 ADD KEY idx_membership_expiry(user_id,active,expires_at),
 ADD KEY idx_membership_review(scope_type,province_id,unit_id,review_due_at);

CREATE SQL SECURITY INVOKER VIEW active_user_memberships AS
 SELECT m.* FROM user_memberships m
 WHERE m.active=1 AND m.revoked_at IS NULL
 AND (m.expires_at IS NULL OR m.expires_at>NOW())
 AND EXISTS(SELECT 1 FROM users u WHERE u.id=m.user_id AND u.active=1);

ALTER TABLE administrator_sessions
 ADD COLUMN auth_strength ENUM('password','email','totp','recovery') NOT NULL DEFAULT 'password',
 ADD COLUMN reauthenticated_at DATETIME NULL;

CREATE TABLE account_mfa (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 secret_cipher TEXT NULL,
 enabled_at DATETIME NULL,
 last_counter BIGINT NOT NULL DEFAULT -1,
 pending_cipher TEXT NULL,
 pending_expires_at DATETIME NULL,
 pending_session_hash CHAR(64) NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE account_recovery_codes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 code_hash CHAR(64) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 used_at DATETIME NULL,
 UNIQUE KEY uq_recovery_code(user_id,code_hash),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE security_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 actor_id BIGINT UNSIGNED NULL,
 event_key VARCHAR(60) NOT NULL,
 occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 notification_status ENUM('none','queued','failed') NOT NULL DEFAULT 'none',
 KEY idx_security_user(user_id,occurred_at,id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE access_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 membership_id BIGINT UNSIGNED NOT NULL,
 reviewer_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('platform','province','unit') NOT NULL,
 scope_id BIGINT UNSIGNED NOT NULL,
 role_key VARCHAR(50) NOT NULL,
 decision ENUM('retain','revoke') NOT NULL,
 previous_expires_at DATETIME NULL,
 expires_at DATETIME NULL,
 next_review_at DATETIME NULL,
 reviewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_review_scope(scope_type,scope_id,reviewed_at),
 FOREIGN KEY(membership_id) REFERENCES user_memberships(id),
 FOREIGN KEY(reviewer_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Queue authority is checked again at delivery, not only at enqueue.
ALTER TABLE outbound_mail_queue
 ADD COLUMN authorising_user_id BIGINT UNSIGNED NULL,
 ADD COLUMN required_capability VARCHAR(80) NULL,
 ADD KEY idx_mail_authority(authorising_user_id,status);

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609270022','Account MFA, session assurance and periodic scoped access reviews',
 SHA2('202609270022_account_security_v1',256),0);