CREATE TABLE IF NOT EXISTS CoreTokenRevocations (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  RevocationId VARCHAR(120) NOT NULL UNIQUE,
  LicenseId VARCHAR(80) NULL,
  InstallId VARCHAR(120) NULL,
  TokenHash VARCHAR(128) NULL,
  Reason VARCHAR(255) NULL,
  RevokedByAdminId INT NULL,
  RevokedByServer VARCHAR(80) NULL,
  RevokedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ExpiresAtUtc DATETIME NULL,
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  SyncVersion BIGINT NOT NULL DEFAULT 0,
  ChangeId VARCHAR(120) NULL,
  UpdatedAtUtc DATETIME NULL,
  UpdatedByServer VARCHAR(80) NULL,
  IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
  DeletedAtUtc DATETIME NULL,
  SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced',
  KEY idx_revocation_license (LicenseId),
  KEY idx_revocation_install (InstallId),
  KEY idx_revocation_token (TokenHash),
  KEY idx_revocation_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreLoginAttempts (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  Email VARCHAR(190) NULL,
  IpAddress VARCHAR(64) NULL,
  UserAgent VARCHAR(255) NULL,
  WasSuccessful TINYINT(1) NOT NULL DEFAULT 0,
  FailureReason VARCHAR(255) NULL,
  AttemptedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  AdminId INT NULL,
  SessionId VARCHAR(128) NULL,
  KEY idx_login_attempt_email (Email),
  KEY idx_login_attempt_ip (IpAddress),
  KEY idx_login_attempt_time (AttemptedAtUtc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreIpAllowlist (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  IpCidr VARCHAR(80) NOT NULL,
  Label VARCHAR(150) NULL,
  Scope VARCHAR(50) NOT NULL DEFAULT 'admin',
  IsActive TINYINT(1) NOT NULL DEFAULT 1,
  CreatedByAdminId INT NULL,
  CreatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UpdatedAtUtc DATETIME NULL,
  Notes TEXT NULL,
  UNIQUE KEY uq_ip_allowlist_scope (IpCidr, Scope),
  KEY idx_ip_allowlist_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS CoreKeyRotationHistory (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  RotationId VARCHAR(120) NOT NULL UNIQUE,
  OldKeyId VARCHAR(120) NULL,
  NewKeyId VARCHAR(120) NOT NULL,
  Algorithm VARCHAR(50) NULL,
  RotatedByAdminId INT NULL,
  RotatedByServer VARCHAR(80) NULL,
  RotatedAtUtc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  Reason VARCHAR(255) NULL,
  PublicKeyBase64 LONGTEXT NULL,
  SyncVersion BIGINT NOT NULL DEFAULT 0,
  ChangeId VARCHAR(120) NULL,
  UpdatedAtUtc DATETIME NULL,
  UpdatedByServer VARCHAR(80) NULL,
  IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
  DeletedAtUtc DATETIME NULL,
  SyncStatus VARCHAR(50) NOT NULL DEFAULT 'synced',
  KEY idx_key_rotation_old (OldKeyId),
  KEY idx_key_rotation_new (NewKeyId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;