-- migrate:up

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  telegram_id BIGINT UNSIGNED NOT NULL,
  username VARCHAR(64) NULL,
  first_name VARCHAR(128) NOT NULL,
  last_name VARCHAR(128) NULL,
  language_code VARCHAR(16) CHARACTER SET ascii COLLATE ascii_general_ci NULL,
  status ENUM('pending', 'active', 'limited', 'banned', 'deleted') NOT NULL DEFAULT 'pending',
  country_code CHAR(2) CHARACTER SET ascii COLLATE ascii_general_ci NULL,
  profile_data JSON NULL,
  telegram_synced_at DATETIME(6) NOT NULL,
  last_seen_at DATETIME(6) NULL,
  activated_at DATETIME(6) 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),
  deleted_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_public_id (public_id),
  UNIQUE KEY uq_users_telegram_id (telegram_id),
  KEY idx_users_status_created (status, created_at),
  KEY idx_users_username (username),
  CONSTRAINT chk_users_telegram_id CHECK (telegram_id > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE user_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash BINARY(32) NOT NULL,
  telegram_auth_date DATETIME(6) NOT NULL,
  ip_address VARBINARY(16) NULL,
  user_agent_hash BINARY(32) NULL,
  idle_expires_at DATETIME(6) NOT NULL,
  absolute_expires_at DATETIME(6) NOT NULL,
  last_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  revoked_at DATETIME(6) NULL,
  revoke_reason VARCHAR(255) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_sessions_public_id (public_id),
  UNIQUE KEY uq_user_sessions_token_hash (token_hash),
  KEY idx_user_sessions_user_active (user_id, revoked_at, absolute_expires_at),
  CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT chk_user_sessions_expiry CHECK (idle_expires_at <= absolute_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE user_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  device_hash BINARY(32) NOT NULL,
  platform VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci NULL,
  telegram_platform VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci NULL,
  risk_state ENUM('normal', 'observed', 'limited', 'blocked') NOT NULL DEFAULT 'normal',
  first_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  last_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_devices_public_id (public_id),
  UNIQUE KEY uq_user_devices_user_hash (user_id, device_hash),
  KEY idx_user_devices_hash (device_hash),
  CONSTRAINT fk_user_devices_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE user_bans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  scope ENUM('account', 'earning', 'withdrawal', 'referral') NOT NULL,
  reason_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reason_text VARCHAR(1000) NOT NULL,
  starts_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  expires_at DATETIME(6) NULL,
  revoked_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  revoked_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_bans_public_id (public_id),
  KEY idx_user_bans_user_active (user_id, scope, revoked_at, expires_at),
  CONSTRAINT fk_user_bans_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_user_bans_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT fk_user_bans_revoker FOREIGN KEY (revoked_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_user_bans_expiry CHECK (expires_at IS NULL OR expires_at > starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE user_activity_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  request_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  ip_address VARBINARY(16) NULL,
  metadata JSON NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  KEY idx_user_activity_user_time (user_id, occurred_at),
  KEY idx_user_activity_event_time (event_type, occurred_at),
  CONSTRAINT fk_user_activity_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS user_activity_logs;
DROP TABLE IF EXISTS user_bans;
DROP TABLE IF EXISTS user_devices;
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS users;