const express = require('express');
const router = express.Router();
const db = require('../../database');
const { TIER_INFO } = require('./constants');

router.get('/', async (req, res) => {
    // Render the shell. HTMX will fetch the table body immediately.
    res.render('admin/players/index', { title: 'Player Management', tiers: TIER_INFO });
});

// Export all players as CSV
router.get('/export', async (req, res) => {
    try {
        const { rows: players } = await db.query(`
            SELECT 
                s.discord_id,
                u.minecraft_username,
                u.minecraft_uuid,
                COALESCE(u.is_staff, false) as is_staff,
                s.tier_level,
                s.status,
                s.mrr_value,
                s.is_lifetime,
                s.stripe_customer_id,
                s.created_at,
                s.updated_at
            FROM subscriptions s
            LEFT JOIN users u ON s.discord_id = u.discord_id
            ORDER BY s.updated_at DESC
        `);

        // Build CSV
        const headers = ['discord_id', 'minecraft_username', 'minecraft_uuid', 'is_staff', 'tier_level', 'tier_name', 'status', 'mrr_value', 'is_lifetime', 'stripe_customer_id', 'created_at', 'updated_at'];
        
        const csvRows = [headers.join(',')];
        
        for (const player of players) {
            const tierName = TIER_INFO[player.tier_level]?.name || 'Unknown';
            const row = [
                player.discord_id || '',
                player.minecraft_username || '',
                player.minecraft_uuid || '',
                player.is_staff ? 'true' : 'false',
                player.tier_level || '',
                tierName,
                player.status || '',
                player.mrr_value || '0',
                player.is_lifetime ? 'true' : 'false',
                player.stripe_customer_id || '',
                player.created_at ? new Date(player.created_at).toISOString() : '',
                player.updated_at ? new Date(player.updated_at).toISOString() : ''
            ].map(val => `"${String(val).replace(/"/g, '""')}"`);
            csvRows.push(row.join(','));
        }

        const csv = csvRows.join('\n');
        const filename = `firefrost-players-${new Date().toISOString().split('T')[0]}.csv`;

        res.setHeader('Content-Type', 'text/csv');
        res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
        res.send(csv);

    } catch (error) {
        console.error('Export error:', error);
        res.status(500).send('Error exporting players');
    }
});

// HTMX Endpoint for the table body (Handles pagination, sorting, searching)
router.get('/table', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const limit = 20;
    const offset = (page - 1) * limit;
    const search = req.query.search || '';
    
    // Basic search implementation
    let query = `
        SELECT 
            COALESCE(s.discord_id, 'N/A') as discord_id,
            u.minecraft_username,
            u.minecraft_uuid,
            COALESCE(u.is_staff, false) as is_staff,
            s.tier_level,
            s.status,
            s.created_at,
            s.id as subscription_id
        FROM subscriptions s
        LEFT JOIN users u ON s.discord_id = u.discord_id
        WHERE s.discord_id ILIKE $1 OR u.minecraft_username ILIKE $1
            OR CAST(s.id AS TEXT) ILIKE $1
        ORDER BY s.updated_at DESC
        LIMIT $2 OFFSET $3
    `;
    
    const { rows: players } = await db.query(query, [`%${search}%`, limit, offset]);
    
    // Render just the partial table rows
    res.render('admin/players/_table_body', { players, TIER_INFO, page, search, layout: false });
});

// POST endpoint for tier changes
router.post('/:discord_id/tier', async (req, res) => {
    const { discord_id } = req.params;
    const { tier_level } = req.body;
    
    const client = await db.pool.connect();
    
    try {
        // Validate tier exists
        if (!TIER_INFO[tier_level]) {
            return res.status(400).send('Invalid tier');
        }
        
        // BEGIN TRANSACTION
        await client.query('BEGIN');
        
        // Update tier in database
        const tierInfo = TIER_INFO[tier_level];
        await client.query(`
            UPDATE subscriptions 
            SET tier_level = $1, mrr_value = $2, updated_at = NOW()
            WHERE discord_id = $3
        `, [tier_level, tierInfo.price, discord_id]);
        
        // TODO: Sync Discord roles via bot
        // This will be implemented when Discord bot integration is ready
        
        // Create audit log entry
        await client.query(`
            INSERT INTO admin_audit_log (admin_discord_id, action, target_discord_id, details)
            VALUES ($1, 'tier_change', $2, $3)
        `, [req.user.id, discord_id, `Changed tier to ${tierInfo.name}`]);
        
        // COMMIT TRANSACTION
        await client.query('COMMIT');
        
        // Return success (htmx will handle UI update)
        res.send('OK');
        
    } catch (error) {
        // ROLLBACK on error
        await client.query('ROLLBACK');
        console.error('Tier change error:', error);
        res.status(500).send('Error updating tier');
    } finally {
        client.release();
    }
});

// POST endpoint for staff toggle
router.post('/:discord_id/staff', async (req, res) => {
    const { discord_id } = req.params;
    
    const client = await db.pool.connect();
    
    try {
        // BEGIN TRANSACTION
        await client.query('BEGIN');
        
        // Toggle staff status
        await client.query(`
            UPDATE users 
            SET is_staff = NOT COALESCE(is_staff, FALSE)
            WHERE discord_id = $1
        `, [discord_id]);
        
        // Get new status for audit log
        const { rows } = await client.query(`SELECT is_staff FROM users WHERE discord_id = $1`, [discord_id]);
        const newStatus = rows[0]?.is_staff ? 'Staff' : 'Non-Staff';
        
        // Create audit log entry
        await client.query(`
            INSERT INTO admin_audit_log (admin_discord_id, action, target_discord_id, details)
            VALUES ($1, 'staff_toggle', $2, $3)
        `, [req.user.id, discord_id, `Changed to ${newStatus}`]);
        
        // COMMIT TRANSACTION
        await client.query('COMMIT');
        
        res.send('OK');
        
    } catch (error) {
        // ROLLBACK on error
        await client.query('ROLLBACK');
        console.error('Staff toggle error:', error);
        res.status(500).send('Error updating staff status');
    } finally {
        client.release();
    }
});

module.exports = router;
