import { Router } from 'express';
import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const router = Router();

// Initialize PostgreSQL connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Get leaderboard (top 100)
router.get('/leaderboard', async (req, res) => {
  try {
    const query = `
      SELECT 
        p.id, 
        p.username, 
        ps.total_wins, 
        ps.total_losses, 
        ps.total_kills, 
        ps.total_deaths,
        ps.win_rate,
        ROW_NUMBER() OVER (ORDER BY ps.total_wins DESC, ps.win_rate DESC) as rank
      FROM players p
      JOIN player_stats ps ON p.id = ps.player_id
      ORDER BY ps.total_wins DESC, ps.win_rate DESC
      LIMIT 100
    `;
    
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to fetch leaderboard' });
  }
});

// Get player stats
router.get('/player/:username/stats', async (req, res) => {
  try {
    const { username } = req.params;
    const query = `
      SELECT 
        p.id,
        p.username,
        ps.total_wins,
        ps.total_losses,
        ps.total_kills,
        ps.total_deaths,
        ps.total_matches,
        ps.win_rate
      FROM players p
      LEFT JOIN player_stats ps ON p.id = ps.player_id
      WHERE p.username = $1
    `;
    
    const result = await pool.query(query, [username]);
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Player not found' });
    }
    
    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to fetch player stats' });
  }
});

// Record match result
router.post('/match-end', async (req, res) => {
  const client = await pool.connect();
  try {
    const { player1Username, player2Username, winnerId, player1Score, player2Score, mapName } = req.body;

    await client.query('BEGIN');

    // Ensure players exist
    await client.query(
      `INSERT INTO players (username) VALUES ($1) ON CONFLICT (username) DO NOTHING`,
      [player1Username]
    );
    await client.query(
      `INSERT INTO players (username) VALUES ($1) ON CONFLICT (username) DO NOTHING`,
      [player2Username]
    );

    // Get player IDs
    const player1Result = await client.query(`SELECT id FROM players WHERE username = $1`, [player1Username]);
    const player2Result = await client.query(`SELECT id FROM players WHERE username = $1`, [player2Username]);
    const player1Id = player1Result.rows[0].id;
    const player2Id = player2Result.rows[0].id;

    // Determine winner
    const actualWinnerId = winnerId === player1Username ? player1Id : player2Id;
    const loserScore = winnerId === player1Username ? player2Score : player1Score;
    const winnerScore = winnerId === player1Username ? player1Score : player2Score;

    // Record match
    await client.query(
      `INSERT INTO matches (player1_id, player2_id, winner_id, winner_score, loser_score, map_name)
       VALUES ($1, $2, $3, $4, $5, $6)`,
      [player1Id, player2Id, actualWinnerId, winnerScore, loserScore, mapName]
    );

    // Update player stats
    for (const playerId of [player1Id, player2Id]) {
      const isWinner = playerId === actualWinnerId;
      
      await client.query(
        `INSERT INTO player_stats (player_id, total_wins, total_losses, total_matches)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (player_id) DO UPDATE SET
         total_wins = total_wins + $2,
         total_losses = total_losses + $5,
         total_matches = total_matches + 1,
         win_rate = ROUND((total_wins + $2)::decimal / (total_matches + 1) * 100, 2)`,
        [playerId, isWinner ? 1 : 0, 0, 1, isWinner ? 0 : 1]
      );
    }

    await client.query('COMMIT');
    res.json({ success: true, message: 'Match recorded' });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error(error);
    res.status(500).json({ error: 'Failed to record match' });
  } finally {
    client.release();
  }
});

export default router;