-- =====================================================================
-- AURA LIVE — Migration 005: Frames, VIP, games, notifications
-- =====================================================================

CREATE TABLE IF NOT EXISTS profile_frames (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  rarity      ENUM('basic','premium','gold','purple','animated') NOT NULL DEFAULT 'basic',
  price_coins INT UNSIGNED NOT NULL DEFAULT 0,
  image_url   VARCHAR(500) NULL,
  is_vip_only TINYINT(1) NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  sort_order  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_owned_frames (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  frame_id   BIGINT UNSIGNED NOT NULL,
  acquired_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_owned_frames (user_id, frame_id),
  CONSTRAINT fk_owned_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_owned_frame FOREIGN KEY (frame_id) REFERENCES profile_frames(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vip_plans (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(80) NOT NULL,
  duration_days INT UNSIGNED NOT NULL,
  price_inr     DECIMAL(10,2) NOT NULL,
  perks         JSON NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_subscriptions (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  plan_id    BIGINT UNSIGNED NOT NULL,
  starts_at  DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  status     ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  order_id   BIGINT UNSIGNED NULL,
  KEY idx_sub_user (user_id, status),
  CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES vip_plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS achievements (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(60) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  icon_url    VARCHAR(500) NULL,
  UNIQUE KEY uq_achievements_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_achievements (
  user_id       BIGINT UNSIGNED NOT NULL,
  achievement_id BIGINT UNSIGNED NOT NULL,
  unlocked_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, achievement_id),
  CONSTRAINT fk_ua_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ua_ach FOREIGN KEY (achievement_id) REFERENCES achievements(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- Games
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS game_lobbies (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id     BIGINT UNSIGNED NULL,
  type        ENUM('quiz','ludo','carrom','puzzle','truth_or_dare') NOT NULL,
  host_user_id BIGINT UNSIGNED NOT NULL,
  max_players TINYINT UNSIGNED NOT NULL DEFAULT 4,
  status      ENUM('lobby','starting','running','finished','aborted') NOT NULL DEFAULT 'lobby',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lobbies_room (room_id),
  CONSTRAINT fk_lobbies_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS game_players (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lobby_id   BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  seat       TINYINT UNSIGNED NOT NULL DEFAULT 0,
  ready      TINYINT(1) NOT NULL DEFAULT 0,
  score      INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_game_players (lobby_id, user_id),
  CONSTRAINT fk_gp_lobby FOREIGN KEY (lobby_id) REFERENCES game_lobbies(id) ON DELETE CASCADE,
  CONSTRAINT fk_gp_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 game_sessions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lobby_id     BIGINT UNSIGNED NOT NULL,
  room_id      BIGINT UNSIGNED NULL,
  type         ENUM('quiz','ludo','carrom','puzzle','truth_or_dare') NOT NULL,
  status       ENUM('lobby','starting','running','finished','aborted') NOT NULL DEFAULT 'running',
  current_turn_user_id BIGINT UNSIGNED NULL,
  state        JSON NULL,
  winner_user_id BIGINT UNSIGNED NULL,
  started_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at     DATETIME NULL,
  KEY idx_sessions_room (room_id),
  CONSTRAINT fk_sessions_lobby FOREIGN KEY (lobby_id) REFERENCES game_lobbies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS game_events (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NULL,
  action     VARCHAR(60) NOT NULL,
  payload    JSON NULL,
  valid      TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_game_events_session (session_id, created_at),
  CONSTRAINT fk_ge_session FOREIGN KEY (session_id) REFERENCES game_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- Notifications
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  type       ENUM('new_follower','new_message','room_invite','gift_received','event_reminder','account_verification','wallet_transaction','moderation_notice','system_announcement') NOT NULL,
  title      VARCHAR(150) NOT NULL,
  body       VARCHAR(500) NULL,
  data       JSON NULL,
  is_read    TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notifications_user (user_id, is_read, created_at),
  CONSTRAINT fk_notifications_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 notification_preferences (
  user_id     BIGINT UNSIGNED PRIMARY KEY,
  push_enabled TINYINT(1) NOT NULL DEFAULT 1,
  email_enabled TINYINT(1) NOT NULL DEFAULT 0,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_np_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;