import { Pool } from 'pg';
import { Lobby, Player } from '../models/public';
import { createLogger } from '../utils/logger';

const logger = createLogger('db');

const pool = new Pool({
  user: 'user',
  host: 'database',
  database: 'quizdb',
  password: 'password',
  port: 5432,
});

export async function getLobbyByCode(code: string): Promise<Lobby | null> {
  const result = await pool.query<Lobby>(
    `
    SELECT       
      code,
      created_at,
      creator_id,
      numberofquestions AS "numberOfQuestions",
      secondsperround AS "secondsPerRound",
      pointstowin AS "pointsToWin",
      started,
      state,
      tags
    FROM lobby
    WHERE code = $1
    `,
    [code]
  );
  return result.rows.length > 0 ? (result.rows[0] as Lobby) : null;
}

export async function getPlayerById(id: number): Promise<Player | null> {
  const result = await pool.query<Player>('SELECT id, name FROM player WHERE id = $1', [id]);
  return result.rows.length > 0 ? (result.rows[0] as Player) : null;
}

export async function addPlayerToLobby(
  playerId: number,
  lobbyCode: string,
  initialScore: number = 0
): Promise<void> {
  await pool.query('INSERT INTO lobby_players (lobby, player, score) VALUES ($1, $2, $3)', [
    lobbyCode,
    playerId,
    initialScore,
  ]);
  logger.info(`Added player ${playerId} to lobby ${lobbyCode} with initial score ${initialScore}`);
}

export async function isPlayerInLobby(playerId: number, lobbyCode: string): Promise<boolean> {
  const result = await pool.query('SELECT 1 FROM lobby_players WHERE lobby = $1 AND player = $2', [
    lobbyCode,
    playerId,
  ]);
  return (result.rowCount ?? 0) > 0;
}

export async function createPlayer(name: string): Promise<Player> {
  const result = await pool.query<Player>(
    'INSERT INTO player (name) VALUES ($1) RETURNING id, name',
    [name]
  );
  return result.rows[0] as Player;
}

export async function getLobby(code: string): Promise<Lobby | null> {
  const lobbyResult = await pool.query<Lobby>('SELECT code FROM lobby WHERE code = $1', [code]);
  return lobbyResult.rows[0] as Lobby;
}

export { pool };
