-- migrate:up

CREATE TABLE security_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  event_type VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  severity ENUM('info', 'low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'info',
  actor_type ENUM('user', 'admin', 'provider', 'system', 'anonymous') NOT NULL,
  actor_reference VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  request_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  ip_address VARBINARY(16) NULL,
  user_agent_hash BINARY(32) NULL,
  metadata JSON NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_security_events_public_id (public_id),
  KEY idx_security_events_type_time (event_type, occurred_at, id),
  KEY idx_security_events_severity_time (severity, occurred_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE fraud_flags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  entity_type ENUM('user', 'referral', 'task_attempt', 'withdrawal', 'device', 'payout') NOT NULL,
  entity_reference VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  rule_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  risk_score SMALLINT UNSIGNED NOT NULL,
  evidence JSON NOT NULL,
  status ENUM('open', 'observing', 'held', 'cleared', 'confirmed') NOT NULL DEFAULT 'open',
  assigned_admin_id BIGINT UNSIGNED NULL,
  resolved_by_admin_id BIGINT UNSIGNED NULL,
  resolution_note VARCHAR(1000) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  resolved_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_fraud_flags_public_id (public_id),
  KEY idx_fraud_flags_queue (status, risk_score, created_at),
  KEY idx_fraud_flags_user (user_id, status, created_at),
  CONSTRAINT fk_fraud_flags_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_fraud_flags_assignee FOREIGN KEY (assigned_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT fk_fraud_flags_resolver FOREIGN KEY (resolved_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_fraud_flags_score CHECK (risk_score <= 1000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE admin_audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  admin_id BIGINT UNSIGNED NULL,
  admin_session_public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NULL,
  action VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  target_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  target_reference VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  reason VARCHAR(1000) NULL,
  before_state JSON NULL,
  after_state JSON NULL,
  request_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  ip_address VARBINARY(16) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_audit_logs_public_id (public_id),
  KEY idx_admin_audit_logs_admin_time (admin_id, created_at, id),
  KEY idx_admin_audit_logs_target (target_type, target_reference, created_at),
  KEY idx_admin_audit_logs_action_time (action, created_at),
  CONSTRAINT fk_admin_audit_logs_admin FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_type ENUM('admin', 'user') NOT NULL,
  identity_hash BINARY(32) NOT NULL,
  ip_address VARBINARY(16) NULL,
  user_agent_hash BINARY(32) NULL,
  outcome ENUM('success', 'invalid', 'locked', 'rate_limited', 'two_factor_failed') NOT NULL,
  failure_code VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  KEY idx_login_attempts_identity_time (actor_type, identity_hash, occurred_at),
  KEY idx_login_attempts_ip_time (ip_address, occurred_at),
  KEY idx_login_attempts_outcome_time (outcome, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS admin_audit_logs;
DROP TABLE IF EXISTS fraud_flags;
DROP TABLE IF EXISTS security_events;