-- Operations Centre, email delivery tracking, privacy retention, waiting-list
-- offers, automated backups and administrator session visibility.

ALTER TABLE communication_log
  ADD COLUMN body_html MEDIUMTEXT NULL AFTER subject,
  ADD COLUMN retry_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER error_message,
  ADD COLUMN last_attempt_at DATETIME NULL AFTER retry_count;

ALTER TABLE bookings
  ADD COLUMN offer_expires_at DATETIME NULL AFTER cancelled_at,
  ADD COLUMN anonymised_at DATETIME NULL AFTER offer_expires_at;

ALTER TABLE lodge_settings
  ADD COLUMN retention_months INT UNSIGNED NOT NULL DEFAULT 18 AFTER retention_meetings,
  ADD COLUMN automatic_backup TINYINT(1) NOT NULL DEFAULT 1 AFTER retention_months,
  ADD COLUMN backup_retention_days INT UNSIGNED NOT NULL DEFAULT 30 AFTER automatic_backup,
  ADD COLUMN health_alert_email VARCHAR(254) NULL AFTER backup_retention_days;

ALTER TABLE users
  ADD COLUMN two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER active;

CREATE TABLE login_challenges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_challenge_user (user_id,expires_at),
  CONSTRAINT fk_login_challenge_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_reset_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  selector CHAR(16) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_password_reset_selector (selector),
  CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE administrator_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  session_hash CHAR(64) NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  UNIQUE KEY uq_administrator_session_hash (session_hash),
  INDEX idx_administrator_session_user (user_id,last_seen_at),
  CONSTRAINT fk_administrator_session_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schema_versions (
  version_number INT UNSIGNED PRIMARY KEY,
  version_name VARCHAR(150) NOT NULL,
  installed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO schema_versions(version_number,version_name)
VALUES(22,'Operations and resilience centre');

INSERT INTO system_jobs(job_key) VALUES ('waiting_list')
ON DUPLICATE KEY UPDATE job_key=VALUES(job_key);