-- Users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(30) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  display_name VARCHAR(50),
  is_guest BOOLEAN DEFAULT false,
  device_fingerprint VARCHAR(255),
  tokens_balance INTEGER DEFAULT 5,
  total_tokens_earned INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  last_login TIMESTAMP,
  is_banned BOOLEAN DEFAULT false,
  role VARCHAR(20) DEFAULT 'player'
);

-- Games table
CREATE TABLE IF NOT EXISTS games (
  id SERIAL PRIMARY KEY,
  creator_id INTEGER REFERENCES users(id),
  title VARCHAR(100) NOT NULL,
  description TEXT,
  game_code TEXT NOT NULL,
  thumbnail_url VARCHAR(500),
  status VARCHAR(20) DEFAULT 'draft',
  play_count INTEGER DEFAULT 0,
  total_ratings INTEGER DEFAULT 0,
  rating_sum INTEGER DEFAULT 0,
  tokens_earned INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  published_at TIMESTAMP,
  last_played TIMESTAMP,
  is_featured BOOLEAN DEFAULT false,
  rejection_reason TEXT
);

-- Play sessions
CREATE TABLE IF NOT EXISTS plays (
  id SERIAL PRIMARY KEY,
  player_id INTEGER REFERENCES users(id),
  game_id INTEGER REFERENCES games(id),
  started_at TIMESTAMP DEFAULT NOW(),
  ended_at TIMESTAMP,
  score INTEGER,
  duration_seconds INTEGER,
  token_spent BOOLEAN DEFAULT false,
  device_fingerprint VARCHAR(255),
  ip_address INET
);

-- High scores
CREATE TABLE IF NOT EXISTS high_scores (
  id SERIAL PRIMARY KEY,
  game_id INTEGER REFERENCES games(id),
  player_id INTEGER REFERENCES users(id),
  score INTEGER NOT NULL,
  achieved_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(game_id, player_id)
);

-- Developer earnings
CREATE TABLE IF NOT EXISTS developer_earnings (
  id SERIAL PRIMARY KEY,
  developer_id INTEGER REFERENCES users(id),
  game_id INTEGER REFERENCES games(id),
  tokens_earned INTEGER NOT NULL,
  play_id INTEGER REFERENCES plays(id),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Guest sessions (for fingerprint tracking)
CREATE TABLE IF NOT EXISTS guest_sessions (
  id SERIAL PRIMARY KEY,
  device_fingerprint VARCHAR(255) NOT NULL,
  user_agent TEXT,
  ip_address INET,
  tokens_granted_today INTEGER DEFAULT 5,
  last_token_grant DATE DEFAULT CURRENT_DATE,
  total_plays INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  last_seen TIMESTAMP DEFAULT NOW()
);

-- Game reports (moderation)
CREATE TABLE IF NOT EXISTS game_reports (
  id SERIAL PRIMARY KEY,
  game_id INTEGER REFERENCES games(id),
  reporter_id INTEGER REFERENCES users(id),
  reason VARCHAR(50),
  description TEXT,
  status VARCHAR(20) DEFAULT 'pending',
  reviewed_by INTEGER REFERENCES users(id),
  reviewed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Suspicious activity log
CREATE TABLE IF NOT EXISTS suspicious_activity_log (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  activity_type VARCHAR(50),
  details JSONB,
  ip_address INET,
  device_fingerprint VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Ratings table (to track one rating per user per game)
CREATE TABLE IF NOT EXISTS ratings (
  id SERIAL PRIMARY KEY,
  game_id INTEGER REFERENCES games(id),
  user_id INTEGER REFERENCES users(id),
  rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(game_id, user_id)
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_games_status ON games(status);
CREATE INDEX IF NOT EXISTS idx_games_creator ON games(creator_id);
CREATE INDEX IF NOT EXISTS idx_plays_player ON plays(player_id);
CREATE INDEX IF NOT EXISTS idx_plays_game ON plays(game_id);
CREATE INDEX IF NOT EXISTS idx_high_scores_game ON high_scores(game_id);
CREATE INDEX IF NOT EXISTS idx_guest_fingerprint ON guest_sessions(device_fingerprint);
CREATE INDEX IF NOT EXISTS idx_games_published ON games(published_at) WHERE status = 'published';
CREATE INDEX IF NOT EXISTS idx_games_play_count ON games(play_count DESC) WHERE status = 'published';