-- =====================================================================
-- AURA LIVE — Migration 001: Users, Auth & Accounts
-- =====================================================================

CREATE TABLE IF NOT EXISTS users (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username        VARCHAR(32)  NOT NULL,
  email           VARCHAR(191) NOT NULL,
  mobile          VARCHAR(20)  NULL,
  password_hash   VARCHAR(255) NOT NULL,
  full_name       VARCHAR(80)  NOT NULL,
  avatar_url      VARCHAR(500) NULL,
  cover_url       VARCHAR(500) NULL,
  bio             VARCHAR(500) NULL,
  country         VARCHAR(60)  NULL,
  date_of_birth   DATE         NULL,
  unique_id       VARCHAR(16)  NOT NULL,
  role            ENUM('user','host','moderator','admin') NOT NULL DEFAULT 'user',
  status          ENUM('active','suspended','banned','deleted') NOT NULL DEFAULT 'active',
  is_verified     TINYINT(1)   NOT NULL DEFAULT 0,
  email_verified  TINYINT(1)   NOT NULL DEFAULT 0,
  mobile_verified TINYINT(1)   NOT NULL DEFAULT 0,
  level           INT UNSIGNED NOT NULL DEFAULT 1,
  experience      INT UNSIGNED NOT NULL DEFAULT 0,
  vip_badge       VARCHAR(40)  NULL,
  frame_id        BIGINT UNSIGNED NULL,
  is_online       TINYINT(1)   NOT NULL DEFAULT 0,
  last_active_at  DATETIME     NULL,
  deletion_requested_at DATETIME NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME     NULL,
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_unique_id (unique_id),
  KEY idx_users_status (status),
  KEY idx_users_mobile (mobile),
  KEY idx_users_online (is_online)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_profiles (
  user_id       BIGINT UNSIGNED PRIMARY KEY,
  interests     VARCHAR(255) NULL,
  voice_intro_url VARCHAR(500) NULL,
  followers_count INT UNSIGNED NOT NULL DEFAULT 0,
  following_count INT UNSIGNED NOT NULL DEFAULT 0,
  friends_count   INT UNSIGNED NOT NULL DEFAULT 0,
  rooms_hosted    INT UNSIGNED NOT NULL DEFAULT 0,
  gifts_received_count INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_settings (
  user_id          BIGINT UNSIGNED PRIMARY KEY,
  who_can_message  ENUM('everyone','friends','nobody') NOT NULL DEFAULT 'everyone',
  who_can_invite   ENUM('everyone','friends','nobody') NOT NULL DEFAULT 'everyone',
  who_can_view_posts ENUM('everyone','friends','private') NOT NULL DEFAULT 'everyone',
  show_online_status TINYINT(1) NOT NULL DEFAULT 1,
  notify_follow    TINYINT(1) NOT NULL DEFAULT 1,
  notify_message   TINYINT(1) NOT NULL DEFAULT 1,
  notify_gift      TINYINT(1) NOT NULL DEFAULT 1,
  notify_event     TINYINT(1) NOT NULL DEFAULT 1,
  notify_marketing TINYINT(1) NOT NULL DEFAULT 0,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_settings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_sessions (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT UNSIGNED NOT NULL,
  refresh_token_hash CHAR(64) NOT NULL,
  device_name   VARCHAR(120) NULL,
  ip_address    VARCHAR(45)  NULL,
  user_agent    VARCHAR(300) NULL,
  expires_at    DATETIME NOT NULL,
  revoked_at    DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sessions_user (user_id),
  UNIQUE KEY uq_sessions_token (refresh_token_hash),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_devices (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT UNSIGNED NOT NULL,
  device_uid   VARCHAR(128) NOT NULL,
  platform     ENUM('android','ios','web') NOT NULL DEFAULT 'android',
  push_token   VARCHAR(500) NULL,
  last_seen_at DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_devices (user_id, device_uid),
  CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_verifications (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  channel     ENUM('email','mobile') NOT NULL,
  destination VARCHAR(191) NOT NULL,
  code_hash   VARCHAR(255) NOT NULL,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  verified_at DATETIME NULL,
  expires_at  DATETIME NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_verifications_user (user_id, channel),
  CONSTRAINT fk_verifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS wallets (
  user_id         BIGINT UNSIGNED PRIMARY KEY,
  coins_balance   BIGINT NOT NULL DEFAULT 0,
  diamonds_balance BIGINT NOT NULL DEFAULT 0,
  version         INT UNSIGNED NOT NULL DEFAULT 1,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wallet_ledger (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT UNSIGNED NOT NULL,
  type          ENUM('gift_send','gift_receive','coin_purchase','admin_adjust','frame_purchase','game_reward','event_reward','refund','signup_bonus') NOT NULL,
  coins_delta   BIGINT NOT NULL,
  balance_after BIGINT NOT NULL,
  reference_type VARCHAR(40) NULL,
  reference_id  VARCHAR(64) NULL,
  note          VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ledger_user (user_id, created_at),
  CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;