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

router.get('/', async (req, res) => {
    try {
        // Query 1: Global Health Metrics
        const { rows: healthData } = await db.query(`
            SELECT 
                COUNT(*) FILTER (WHERE status = 'active' AND tier_level != 499) as active_subs,
                SUM(mrr_value) FILTER (WHERE status = 'active') as recognized_mrr,
                COUNT(*) FILTER (WHERE status = 'grace_period') as at_risk_subs,
                SUM(mrr_value) FILTER (WHERE status = 'grace_period') as at_risk_mrr,
                COUNT(*) FILTER (WHERE tier_level = 499 AND status IN ('active', 'lifetime')) as lifetime_subs
            FROM subscriptions;
        `);

        const metrics = {
            activeSubs: parseInt(healthData[0].active_subs || 0),
            recognizedMrr: parseFloat(healthData[0].recognized_mrr || 0),
            atRiskSubs: parseInt(healthData[0].at_risk_subs || 0),
            atRiskMrr: parseFloat(healthData[0].at_risk_mrr || 0),
            lifetimeSubs: parseInt(healthData[0].lifetime_subs || 0)
        };
        
        metrics.lifetimeRevenue = metrics.lifetimeSubs * 499.00;
        metrics.arpu = metrics.activeSubs > 0 ? (metrics.recognizedMrr / metrics.activeSubs).toFixed(2) : 0;
        metrics.arr = (metrics.recognizedMrr * 12).toFixed(2);

        // Query 2: Tier Breakdown
        const { rows: tierData } = await db.query(`
            SELECT tier_level, status, COUNT(*) as count, SUM(mrr_value) as total_mrr
            FROM subscriptions
            WHERE status IN ('active', 'grace_period', 'lifetime')
            GROUP BY tier_level, status;
        `);

        // Process paths (Fire vs Frost)
        const paths = { 
            fire: { mrr: 0, subs: 0 }, 
            frost: { mrr: 0, subs: 0 }, 
            universal: { mrr: 0, subs: 0 } 
        };
        
        const tierBreakdown = {};

        tierData.forEach(row => {
            const tLevel = row.tier_level;
            const info = TIER_INFO[tLevel] || { name: 'Unknown', path: 'universal' };
            const mrr = parseFloat(row.total_mrr || 0);
            const count = parseInt(row.count || 0);

            // Tally active MRR for the Path comparison (excluding grace period from guaranteed MRR)
            if (row.status === 'active') {
                if (paths[info.path]) {
                    paths[info.path].mrr += mrr;
                    paths[info.path].subs += count;
                }
            }

            // Build detailed table data
            if (!tierBreakdown[tLevel]) {
                tierBreakdown[tLevel] = { ...info, activeCount: 0, graceCount: 0, totalMrr: 0 };
            }
            
            if (row.status === 'active' || row.status === 'lifetime') tierBreakdown[tLevel].activeCount += count;
            if (row.status === 'grace_period') tierBreakdown[tLevel].graceCount += count;
            if (row.status === 'active') tierBreakdown[tLevel].totalMrr += mrr;
        });

        res.render('admin/financials/index', { 
            title: 'Financials & Analytics', 
            metrics, 
            paths, 
            tierBreakdown,
            TIER_INFO
        });

    } catch (error) {
        console.error("Financials Error:", error);
        res.status(500).send("Error loading financial data.");
    }
});

module.exports = router;
