const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST || '127.0.0.1',
  user: process.env.DB_USER || 'arbiter',
  password: process.env.DB_PASSWORD || 'FireFrost2026!Arbiter',
  database: process.env.DB_NAME || 'arbiter_db',
  port: process.env.DB_PORT || 5432
});

async function getFinancialMetrics() {
  try {
    // 1. Get Active Subscribers
    const subsQuery = await pool.query(
      `SELECT COUNT(*) as count FROM subscriptions WHERE status = 'active' OR status = 'lifetime'`
    );
    const activeSubs = parseInt(subsQuery.rows[0].count) || 0;

    // 2. Calculate Recognized MRR
    const mrrQuery = await pool.query(
      `SELECT SUM(mrr_value) as total FROM subscriptions WHERE status = 'active'`
    );
    const recognizedMrr = parseFloat(mrrQuery.rows[0].total) || 0;

    // 3. At Risk Subscribers (grace period)
    const atRiskQuery = await pool.query(
      `SELECT COUNT(*) as count, SUM(mrr_value) as mrr 
       FROM subscriptions WHERE status = 'grace_period'`
    );
    const atRiskSubs = parseInt(atRiskQuery.rows[0].count) || 0;
    const atRiskMrr = parseFloat(atRiskQuery.rows[0].mrr) || 0;

    // 4. Lifetime Subscribers (Awakened + Sovereign)
    const lifetimeQuery = await pool.query(
      `SELECT COUNT(*) as count, SUM(CASE WHEN tier_level = 10 THEN 499 WHEN tier_level = 1 THEN 1 ELSE 0 END) as revenue
       FROM subscriptions WHERE is_lifetime = TRUE`
    );
    const lifetimeSubs = parseInt(lifetimeQuery.rows[0].count) || 0;
    const lifetimeRevenue = parseFloat(lifetimeQuery.rows[0].revenue) || 0;

    // 5. Fire vs Frost Paths
    const pathQuery = await pool.query(
      `SELECT p.fire_or_frost, COUNT(s.*) as subs, SUM(s.mrr_value) as mrr
       FROM subscriptions s
       JOIN stripe_products p ON s.tier_level = p.tier_level
       WHERE s.status = 'active'
       GROUP BY p.fire_or_frost`
    );

    const paths = {
      fire: { subs: 0, mrr: 0 },
      frost: { subs: 0, mrr: 0 }
    };

    pathQuery.rows.forEach(row => {
      if (row.fire_or_frost === 'fire') {
        paths.fire.subs = parseInt(row.subs) || 0;
        paths.fire.mrr = parseFloat(row.mrr) || 0;
      } else if (row.fire_or_frost === 'frost') {
        paths.frost.subs = parseInt(row.subs) || 0;
        paths.frost.mrr = parseFloat(row.mrr) || 0;
      }
    });

    // 6. Tier Breakdown
    const tierQuery = await pool.query(
      `SELECT 
        s.tier_level,
        p.tier_name as name,
        p.fire_or_frost as path,
        COUNT(CASE WHEN s.status = 'active' THEN 1 END) as active_count,
        COUNT(CASE WHEN s.status = 'grace_period' THEN 1 END) as grace_count,
        SUM(CASE WHEN s.status = 'active' THEN s.mrr_value ELSE 0 END) as total_mrr
       FROM subscriptions s
       JOIN stripe_products p ON s.tier_level = p.tier_level
       GROUP BY s.tier_level, p.tier_name, p.fire_or_frost`
    );

    const tierBreakdown = {};
    tierQuery.rows.forEach(row => {
      tierBreakdown[row.tier_level] = {
        name: row.name,
        path: row.path || 'both',
        activeCount: parseInt(row.active_count) || 0,
        graceCount: parseInt(row.grace_count) || 0,
        totalMrr: parseFloat(row.total_mrr) || 0
      };
    });

    return {
      metrics: {
        activeSubs,
        recognizedMrr,
        atRiskSubs,
        atRiskMrr,
        lifetimeSubs,
        lifetimeRevenue
      },
      paths,
      tierBreakdown
    };
  } catch (error) {
    console.error('FinancialsService error:', error);
    throw error;
  }
}

module.exports = { getFinancialMetrics };
