-- LiveCam tables for the DirectCam system
-- Run this against your Supabase/PostgreSQL database

CREATE TABLE IF NOT EXISTS live_cam_settings (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  free_max_minutes INTEGER NOT NULL DEFAULT 5,
  group_rate INTEGER NOT NULL DEFAULT 10,
  private_rate INTEGER NOT NULL DEFAULT 20,
  audio_enabled BOOLEAN NOT NULL DEFAULT true,
  category VARCHAR(50) NOT NULL DEFAULT 'Adult',
  strapline VARCHAR(200) DEFAULT '',
  is_live BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_live_cam_settings_user_id ON live_cam_settings(user_id);
CREATE INDEX IF NOT EXISTS idx_live_cam_settings_is_live ON live_cam_settings(is_live);

CREATE TABLE IF NOT EXISTS live_sessions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  mode VARCHAR(20) NOT NULL DEFAULT 'free',
  viewer_count INTEGER NOT NULL DEFAULT 0,
  credits_earned INTEGER NOT NULL DEFAULT 0,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  ended_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_live_sessions_user_id ON live_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_live_sessions_ended_at ON live_sessions(ended_at);

CREATE TABLE IF NOT EXISTS live_session_viewers (
  id SERIAL PRIMARY KEY,
  session_id INTEGER NOT NULL REFERENCES live_sessions(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  mode VARCHAR(20) NOT NULL DEFAULT 'free',
  is_kicked BOOLEAN NOT NULL DEFAULT false,
  joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  left_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_live_session_viewers_session_id ON live_session_viewers(session_id);
CREATE INDEX IF NOT EXISTS idx_live_session_viewers_user_id ON live_session_viewers(user_id);
CREATE INDEX IF NOT EXISTS idx_live_session_viewers_active ON live_session_viewers(session_id, user_id, left_at);