<?php
require_once __DIR__ . '/../includes/config.php';

requireAuth();

$method = $_SERVER['REQUEST_METHOD'];

if ($method !== 'GET') {
    jsonResponse(['error' => 'Método não permitido'], 405);
}

$periodType = $_GET['periodType'] ?? 'monthly';

// Get multiplier based on period type
switch ($periodType) {
    case 'weekly':
        $multiplier = 7 / 30;
        $periodLabel = 'Semanal';
        $futureDays = 7;
        break;
    case 'biweekly':
        $multiplier = 15 / 30;
        $periodLabel = 'Quinzenal';
        $futureDays = 15;
        break;
    case 'monthly':
    default:
        $multiplier = 1;
        $periodLabel = 'Mensal';
        $futureDays = 30;
        break;
}

// Get averages from last 3 months
$threeMonthsAgo = date('Y-m-d', strtotime('-3 months'));
$today = date('Y-m-d');
$futureDate = date('Y-m-d', strtotime("+{$futureDays} days"));

// Count months with data for confidence calculation (MySQL version)
$stmt = $pdo->prepare("
    SELECT COUNT(DISTINCT DATE_FORMAT(transaction_date, '%Y-%m')) as months_count
    FROM transactions
    WHERE transaction_date >= ?
");
$stmt->execute([$threeMonthsAgo]);
$monthsWithData = (int) $stmt->fetch()['months_count'];

// Calculate confidence score (0-100%)
$confidenceScore = 0;
if ($monthsWithData > 0) {
    $confidenceScore = 30 + min(60, $monthsWithData * 20);
    
    // Check if we have both income and expense data
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(DISTINCT CASE WHEN type = 'income' THEN id END) as income_count,
            COUNT(DISTINCT CASE WHEN type = 'expense' THEN id END) as expense_count
        FROM transactions
        WHERE transaction_date >= ?
    ");
    $stmt->execute([$threeMonthsAgo]);
    $typeCounts = $stmt->fetch();
    if ($typeCounts['income_count'] > 0 && $typeCounts['expense_count'] > 0) {
        $confidenceScore = min(100, $confidenceScore + 10);
    }
}

// Average income (MySQL version using DATE_FORMAT instead of DATE_TRUNC)
$stmt = $pdo->prepare("
    SELECT COALESCE(AVG(monthly_total), 0) as avg_income
    FROM (
        SELECT DATE_FORMAT(transaction_date, '%Y-%m') as month, SUM(CAST(amount AS DECIMAL(12,2))) as monthly_total
        FROM transactions
        WHERE type = 'income' AND transaction_date >= ?
        GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
    ) monthly_totals
");
$stmt->execute([$threeMonthsAgo]);
$avgIncome = (float) $stmt->fetch()['avg_income'] * $multiplier;

// Average expenses (MySQL version)
$stmt = $pdo->prepare("
    SELECT COALESCE(AVG(monthly_total), 0) as avg_expense
    FROM (
        SELECT DATE_FORMAT(transaction_date, '%Y-%m') as month, SUM(CAST(amount AS DECIMAL(12,2))) as monthly_total
        FROM transactions
        WHERE type = 'expense' AND transaction_date >= ?
        GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
    ) monthly_totals
");
$stmt->execute([$threeMonthsAgo]);
$avgExpenses = (float) $stmt->fetch()['avg_expense'] * $multiplier;

// Pending bills (future expenses)
$stmt = $pdo->prepare("
    SELECT COALESCE(SUM(CAST(amount AS DECIMAL(12,2))), 0) as total
    FROM bills
    WHERE status = 'pending' AND due_date <= ?
");
$stmt->execute([$futureDate]);
$pendingBills = (float) $stmt->fetch()['total'];

// Recurring bills total
$stmt = $pdo->prepare("
    SELECT COALESCE(SUM(CAST(amount AS DECIMAL(12,2))), 0) as total
    FROM bills
    WHERE recurrence IS NOT NULL AND recurrence != 'none' AND status != 'cancelled'
");
$stmt->execute();
$recurringBillsBase = (float) $stmt->fetch()['total'];
$recurringBills = $recurringBillsBase * $multiplier;

// Projected balance
$projectedBalance = $avgIncome - $avgExpenses;

// Projections by category (MySQL version)
$stmt = $pdo->prepare("
    SELECT 
        COALESCE(c.name, 'Sem categoria') as name, 
        'expense' as type,
        COALESCE(AVG(monthly_total), 0) * ? as projected_amount
    FROM (
        SELECT 
            t.category_id,
            DATE_FORMAT(transaction_date, '%Y-%m') as month, 
            SUM(CAST(t.amount AS DECIMAL(12,2))) as monthly_total
        FROM transactions t
        WHERE t.type = 'expense' AND t.transaction_date >= ?
        GROUP BY t.category_id, DATE_FORMAT(transaction_date, '%Y-%m')
    ) monthly_data
    LEFT JOIN categories c ON monthly_data.category_id = c.id
    GROUP BY monthly_data.category_id, c.name
    HAVING AVG(monthly_total) > 0
    ORDER BY projected_amount DESC
    LIMIT 10
");
$stmt->execute([$multiplier, $threeMonthsAgo]);
$projectionsByCategory = $stmt->fetchAll();

// Cash flow data for chart (last 6 months + 3 months projection) - MySQL version
$sixMonthsAgo = date('Y-m-d', strtotime('-6 months'));
$stmt = $pdo->prepare("
    SELECT 
        DATE_FORMAT(transaction_date, '%Y-%m') as month,
        SUM(CASE WHEN type = 'income' THEN CAST(amount AS DECIMAL(12,2)) ELSE 0 END) as income,
        SUM(CASE WHEN type = 'expense' THEN CAST(amount AS DECIMAL(12,2)) ELSE 0 END) as expense
    FROM transactions
    WHERE transaction_date >= ?
    GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
    ORDER BY month ASC
");
$stmt->execute([$sixMonthsAgo]);
$historicalCashFlow = $stmt->fetchAll();

// Build cash flow with projections
$cashFlowData = [];
$monthNames = [
    '01' => 'Jan', '02' => 'Fev', '03' => 'Mar', '04' => 'Abr',
    '05' => 'Mai', '06' => 'Jun', '07' => 'Jul', '08' => 'Ago',
    '09' => 'Set', '10' => 'Out', '11' => 'Nov', '12' => 'Dez'
];

foreach ($historicalCashFlow as $row) {
    $parts = explode('-', $row['month']);
    $label = $monthNames[$parts[1]] . '/' . substr($parts[0], 2);
    $cashFlowData[] = [
        'month' => $label,
        'income' => (float) $row['income'],
        'expense' => (float) $row['expense'],
        'balance' => (float) $row['income'] - (float) $row['expense'],
        'type' => 'historical'
    ];
}

// Add 3 months of projections
$avgMonthlyIncome = $avgIncome / $multiplier;
$avgMonthlyExpense = $avgExpenses / $multiplier;

for ($i = 1; $i <= 3; $i++) {
    $futureMonth = date('Y-m', strtotime("+{$i} months"));
    $parts = explode('-', $futureMonth);
    $label = $monthNames[$parts[1]] . '/' . substr($parts[0], 2) . '*';
    
    // Get pending bills for this month
    $monthStart = date('Y-m-01', strtotime("+{$i} months"));
    $monthEnd = date('Y-m-t', strtotime("+{$i} months"));
    
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(CAST(amount AS DECIMAL(12,2))), 0) as total
        FROM bills
        WHERE status = 'pending' AND due_date >= ? AND due_date <= ?
    ");
    $stmt->execute([$monthStart, $monthEnd]);
    $monthBills = (float) $stmt->fetch()['total'];
    
    $projectedExpense = max($avgMonthlyExpense, $monthBills + ($avgMonthlyExpense * 0.5));
    
    $cashFlowData[] = [
        'month' => $label,
        'income' => round($avgMonthlyIncome, 2),
        'expense' => round($projectedExpense, 2),
        'balance' => round($avgMonthlyIncome - $projectedExpense, 2),
        'type' => 'projected'
    ];
}

// Future bills breakdown (MySQL version)
$stmt = $pdo->prepare("
    SELECT 
        DATE_FORMAT(due_date, '%Y-%m') as month,
        COALESCE(SUM(CAST(amount AS DECIMAL(12,2))), 0) as total
    FROM bills
    WHERE status = 'pending' AND due_date >= ? AND due_date <= ?
    GROUP BY DATE_FORMAT(due_date, '%Y-%m')
    ORDER BY month ASC
");
$threeMonthsFuture = date('Y-m-d', strtotime('+3 months'));
$stmt->execute([$today, $threeMonthsFuture]);
$futureBillsByMonth = $stmt->fetchAll();

$futureBillsData = [];
foreach ($futureBillsByMonth as $row) {
    $parts = explode('-', $row['month']);
    $label = $monthNames[$parts[1]] . '/' . substr($parts[0], 2);
    $futureBillsData[] = [
        'month' => $label,
        'total' => (float) $row['total']
    ];
}

jsonResponse([
    'periodType' => $periodType,
    'periodLabel' => $periodLabel,
    'projectedIncome' => round($avgIncome, 2),
    'projectedExpenses' => round($avgExpenses, 2),
    'projectedBalance' => round($projectedBalance, 2),
    'pendingBillsTotal' => round($pendingBills, 2),
    'recurringBills' => round($recurringBills, 2),
    'confidenceScore' => $confidenceScore,
    'projectionsByCategory' => $projectionsByCategory,
    'cashFlowData' => $cashFlowData,
    'futureBillsData' => $futureBillsData
]);
