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

// Shell Route
router.get('/', (req, res) => {
    res.render('admin/grace/index', { title: 'Grace Period Dashboard' });
});

// HTMX Polling Endpoint (Stats + Table)
router.get('/list', async (req, res) => {
    try {
        // 1. Fetch At-Risk Subscribers
        const { rows: atRisk } = await db.query(`
            SELECT 
                u.minecraft_username,
                u.discord_id,
                s.tier_level,
                s.mrr_value,
                s.grace_period_started_at,
                s.grace_period_ends_at,
                s.payment_failure_reason,
                EXTRACT(EPOCH FROM (s.grace_period_ends_at - NOW())) as seconds_remaining
            FROM subscriptions s
            JOIN users u ON s.discord_id = u.discord_id
            WHERE s.status = 'grace_period'
            ORDER BY s.grace_period_ends_at ASC;
        `);

        // 2. Fetch High-Level Stats
        const totalAtRiskMrr = atRisk.reduce((sum, sub) => sum + parseFloat(sub.mrr_value || 0), 0);
        const atRiskCount = atRisk.length;

        // 3. Render the Partial
        res.render('admin/grace/_list', { atRisk, 
            totalAtRiskMrr, 
            atRiskCount, 
            TIER_INFO, layout: false });
    } catch (error) {
        console.error("Grace Period List Error:", error);
        res.status(500).send("<tr><td colspan='6' class='text-center text-red-500'>Error loading data.</td></tr>");
    }
});

// Action: Extend Grace Period by 24 Hours
router.post('/:discord_id/extend', async (req, res) => {
    const { discord_id } = req.params;
    const adminId = req.user.id;
    const adminUsername = req.user.username;

    const client = await db.pool.connect();

    try {
        // BEGIN TRANSACTION
        await client.query('BEGIN');
        
        await client.query(`
            UPDATE subscriptions 
            SET grace_period_ends_at = grace_period_ends_at + INTERVAL '24 hours'
            WHERE discord_id = $1 AND status = 'grace_period'
        `, [discord_id]);

        await client.query(`
            INSERT INTO admin_audit_log (admin_discord_id, admin_username, action_type, target_identifier, details)
            VALUES ($1, $2, 'extend_grace_period', $3, '{"extension": "24h"}')
        `, [adminId, adminUsername, discord_id]);

        // COMMIT TRANSACTION
        await client.query('COMMIT');
        
        res.send(`<span class="text-green-500 font-bold text-sm">✅ Extended 24h</span>`);
    } catch (error) {
        // ROLLBACK on error
        await client.query('ROLLBACK');
        console.error('Grace period extension error:', error);
        res.status(500).send(`<span class="text-red-500 font-bold text-sm">❌ Error</span>`);
    } finally {
        client.release();
    }
});

// Action: Convert to Manual Payment (Resolves Grace Period)
router.post('/:discord_id/manual', async (req, res) => {
    const { discord_id } = req.params;
    const adminId = req.user.id;
    const adminUsername = req.user.username;

    const client = await db.pool.connect();

    try {
        // BEGIN TRANSACTION
        await client.query('BEGIN');
        
        await client.query(`
            UPDATE subscriptions 
            SET status = 'active', grace_period_started_at = NULL, grace_period_ends_at = NULL
            WHERE discord_id = $1
        `, [discord_id]);

        await client.query(`
            INSERT INTO admin_audit_log (admin_discord_id, admin_username, action_type, target_identifier, details)
            VALUES ($1, $2, 'manual_payment_override', $3, '{"reason": "admin_override"}')
        `, [adminId, adminUsername, discord_id]);

        // COMMIT TRANSACTION
        await client.query('COMMIT');
        
        res.send(`<span class="text-purple-500 font-bold text-sm">✅ Activated</span>`);
    } catch (error) {
        // ROLLBACK on error
        await client.query('ROLLBACK');
        console.error('Manual payment override error:', error);
        res.status(500).send(`<span class="text-red-500 font-bold text-sm">❌ Error</span>`);
    } finally {
        client.release();
    }
});

module.exports = router;
