-- =====================================================================
-- AURA LIVE — Migration 003: Party rooms, seats, chat
-- =====================================================================

CREATE TABLE IF NOT EXISTS rooms (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_code    VARCHAR(12) NOT NULL,
  host_id      BIGINT UNSIGNED NOT NULL,
  name         VARCHAR(100) NOT NULL,
  description  VARCHAR(500) NULL,
  category     ENUM('related','explore','events','discussions','games','music','friendship','casual','language') NOT NULL DEFAULT 'casual',
  language     VARCHAR(30) NOT NULL DEFAULT 'Hindi',
  cover_url    VARCHAR(500) NULL,
  bg_url       VARCHAR(500) NULL,
  announcement VARCHAR(500) NULL,
  tags         VARCHAR(255) NULL,
  seat_count   TINYINT UNSIGNED NOT NULL DEFAULT 8,
  max_participants INT UNSIGNED NOT NULL DEFAULT 50,
  status       ENUM('live','ended','scheduled') NOT NULL DEFAULT 'live',
  is_private   TINYINT(1) NOT NULL DEFAULT 0,
  password_hash VARCHAR(255) NULL,
  participant_count INT UNSIGNED NOT NULL DEFAULT 0,
  mic_active_count  INT UNSIGNED NOT NULL DEFAULT 0,
  total_messages INT UNSIGNED NOT NULL DEFAULT 0,
  gifts_sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ended_at    DATETIME NULL,
  UNIQUE KEY uq_rooms_code (room_code),
  KEY idx_rooms_host (host_id),
  KEY idx_rooms_discover (status, category, participant_count),
  CONSTRAINT fk_rooms_host FOREIGN KEY (host_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_members (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id   BIGINT UNSIGNED NOT NULL,
  user_id   BIGINT UNSIGNED NOT NULL,
  role      ENUM('host','co_host','moderator','speaker','listener') NOT NULL DEFAULT 'listener',
  is_online TINYINT(1) NOT NULL DEFAULT 1,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  left_at   DATETIME NULL,
  UNIQUE KEY uq_room_members (room_id, user_id),
  KEY idx_members_user (user_id),
  CONSTRAINT fk_members_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_members_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 room_seats (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id    BIGINT UNSIGNED NOT NULL,
  seat_index TINYINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NULL,
  locked     TINYINT(1) NOT NULL DEFAULT 0,
  muted      TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_seats (room_id, seat_index),
  KEY idx_seats_user (user_id),
  CONSTRAINT fk_seats_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_seats_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_moderators (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id    BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  granted_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_room_mods (room_id, user_id),
  CONSTRAINT fk_mods_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_mods_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 room_invitations (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id     BIGINT UNSIGNED NOT NULL,
  inviter_id  BIGINT UNSIGNED NOT NULL,
  invitee_id  BIGINT UNSIGNED NOT NULL,
  status      ENUM('pending','accepted','rejected','expired') NOT NULL DEFAULT 'pending',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NULL,
  KEY idx_invites_invitee (invitee_id, status),
  CONSTRAINT fk_invites_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 room_events (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id    BIGINT UNSIGNED NOT NULL,
  actor_id   BIGINT UNSIGNED NULL,
  type       VARCHAR(40) NOT NULL,
  details    JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_room_events (room_id, created_at),
  CONSTRAINT fk_room_events_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 room_messages (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id    BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NULL,
  type       ENUM('text','gift','system','welcome') NOT NULL DEFAULT 'text',
  body       VARCHAR(1000) NOT NULL,
  metadata   JSON NULL,
  status     ENUM('visible','removed') NOT NULL DEFAULT 'visible',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_room_messages (room_id, created_at),
  CONSTRAINT fk_room_msg_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_room_msg_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------
-- Private messaging
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS conversations (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_one_id  BIGINT UNSIGNED NOT NULL,
  user_two_id  BIGINT UNSIGNED NOT NULL,
  last_message_at DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_conversations (user_one_id, user_two_id),
  KEY idx_conv_one (user_one_id, last_message_at),
  KEY idx_conv_two (user_two_id, last_message_at),
  CONSTRAINT fk_conv_one FOREIGN KEY (user_one_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_two FOREIGN KEY (user_two_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_members (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NOT NULL,
  last_read_message_id BIGINT UNSIGNED NULL,
  unread_count    INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (conversation_id, user_id),
  CONSTRAINT fk_conv_members_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_members_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 direct_messages (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_id       BIGINT UNSIGNED NOT NULL,
  recipient_id    BIGINT UNSIGNED NOT NULL,
  body            VARCHAR(2000) NOT NULL,
  attachment_url  VARCHAR(500) NULL,
  status          ENUM('sent','delivered','read') NOT NULL DEFAULT 'sent',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME NULL,
  KEY idx_dm_conv (conversation_id, created_at),
  CONSTRAINT fk_dm_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;