<?php
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');
// Solo se requiere api.php para la conexión y seguridad
require_once __DIR__ . '/../core/api.php'; 
// Se asume que sendJsonResponse está disponible desde core/api.php

// Sanitize and validate month input (Expected format: YYYY-MM)
$month = $_GET['month'] ?? date('Y-m');

if (!preg_match('/^\d{4}-\d{2}$/', (string)$month)) {
    // Usar la función de respuesta JSON definida en api.php
    if (function_exists('sendJsonResponse')) {
        sendJsonResponse(['success' => false, 'error' => 'Formato de mes no válido. Use YYYY-MM.'], 400);
    } else {
        http_response_code(400);
        die(json_encode(['success' => false, 'error' => 'Formato de mes no válido. Use YYYY-MM.']));
    }
}

// Convert to string for prepared statement LIKE pattern
$month_param = $month . '%';
$user_id = $_SESSION['user_id'] ?? 1;

try {
    $response = [];
    
    // DEFINICIÓN BASE DE PARÁMETROS: Contiene los 2 parámetros requeridos por la mayoría de consultas.
    $base_params = [':uid' => $user_id, ':m' => $month_param];

    // 1. INGRESOS - Pagadas (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM invoices WHERE user_id = :uid AND status = 'pagada' AND invoice_date LIKE :m");
    $stmt->execute($base_params);
    $paid = $stmt->fetchColumn() ?: 0.00;
    
    // 2. INGRESOS - Pendientes (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM invoices WHERE user_id = :uid AND (status = 'enviado' OR status = 'pendiente' OR status = 'enviada') AND invoice_date LIKE :m");
    $stmt->execute($base_params);
    $due = $stmt->fetchColumn() ?: 0.00;

    $response['income'] = [
        'total_paid' => $paid,
        'total_due' => $due
    ];

    // 3. GASTOS GENERALES (TRANSACTIONS) (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT SUM(amount) FROM transactions WHERE user_id = :uid AND transaction_type = 'expense' AND transaction_date LIKE :m");
    $stmt->execute($base_params);
    $expenses = $stmt->fetchColumn() ?: 0.00;
    
    $response['expenses'] = [
        'general' => $expenses
    ];

    // 4. NÓMINA (WORK LOGS x HOURLY RATE) (Requires :uid, :m)
    // Se utiliza un array de parámetros temporal para evitar cualquier colisión potencial del :m
    $payroll_params = [
        ':uid' => $user_id, 
        ':m' => $month_param
    ];
    $stmt = $pdo->prepare("
        SELECT 
            SUM(w.total_hours) as hours, 
            SUM(w.total_hours * s.hourly_rate) as cost 
        FROM work_logs w 
        JOIN staff s ON w.staff_id = s.id 
        WHERE s.user_id = :uid AND w.clock_in LIKE :m
    ");
    $stmt->execute($payroll_params);
    $payroll = $stmt->fetch(PDO::FETCH_ASSOC);

    $response['payroll'] = [
        'total_hours' => $payroll['hours'] ?: 0.00,
        'total_cost' => $payroll['cost'] ?: 0.00
    ];

    // 5. PIPELINE - Total Cotizado (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM quotes WHERE user_id = :uid AND estimate_date LIKE :m");
    $stmt->execute($base_params);
    $quoted = $stmt->fetchColumn() ?: 0.00;

    // 6. PIPELINE - Total Ganado (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM quotes WHERE user_id = :uid AND status = 'converted' AND estimate_date LIKE :m");
    $stmt->execute($base_params);
    $wonAmount = $stmt->fetchColumn() ?: 0.00;

    // 7. PIPELINE - Conteo Total (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM quotes WHERE user_id = :uid AND estimate_date LIKE :m");
    $stmt->execute($base_params);
    $countTotal = $stmt->fetchColumn() ?: 0;

    // 8. PIPELINE - Conteo Ganado (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM quotes WHERE user_id = :uid AND status = 'converted' AND estimate_date LIKE :m");
    $stmt->execute($base_params);
    $countWon = $stmt->fetchColumn() ?: 0;

    // 9. PIPELINE - Conteo Perdido (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM quotes WHERE user_id = :uid AND (status = 'rejected' OR status = 'lost') AND estimate_date LIKE :m");
    $stmt->execute($base_params);
    $countLost = $stmt->fetchColumn() ?: 0;

    $response['pipeline'] = [
        'total_quoted' => $quoted,
        'total_won' => $wonAmount,
        'count_total' => $countTotal,
        'count_won' => $countWon,
        'count_lost' => $countLost
    ];

    // 10. OPERACIONES (CITAS) (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM unique_appointments WHERE user_id = :uid AND appointment_date LIKE :m");
    $stmt->execute($base_params);
    $appCount = $stmt->fetchColumn() ?: 0;

    $response['operations'] = [
        'appointments_count' => $appCount
    ];

    // 11. LISTAS - Ingresos Recientes (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT external_id, total_amount FROM invoices WHERE user_id = :uid AND status='pagada' AND invoice_date LIKE :m ORDER BY total_amount DESC LIMIT 5");
    $stmt->execute($base_params);
    $response['lists']['invoices'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 12. LISTAS - Gastos Recientes (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT description, amount FROM transactions WHERE user_id = :uid AND transaction_type='expense' AND transaction_date LIKE :m ORDER BY amount DESC LIMIT 5");
    $stmt->execute($base_params);
    $response['lists']['expenses'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 13. LISTAS - Cotizaciones Recientes (Requires :uid, :m)
    $stmt = $pdo->prepare("SELECT external_id, total_amount, status FROM quotes WHERE user_id = :uid AND estimate_date LIKE :m ORDER BY estimate_date DESC LIMIT 5");
    $stmt->execute($base_params);
    $response['lists']['estimates'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Éxito
    echo json_encode($response);

} catch (PDOException $e) {
    // Manejo de errores que genera PDO
    error_log("Financial Report PDO Error: " . $e->getMessage() . " | SQLSTATE: " . $e->getCode());
    if (function_exists('sendJsonResponse')) {
        sendJsonResponse(['success' => false, 'error' => 'Error calculando reporte: SQLSTATE[' . $e->getCode() . ']: ' . $e->getMessage()], 500);
    } else {
        http_response_code(500);
        die(json_encode(['success' => false, 'error' => 'Error calculando reporte: SQLSTATE[' . $e->getCode() . ']: ' . $e->getMessage()]));
    }
} catch (Exception $e) {
    // Manejo de otros errores
    error_log("Financial Report Error: " . $e->getMessage());
     if (function_exists('sendJsonResponse')) {
        sendJsonResponse(['success' => false, 'error' => 'Error interno: ' . $e->getMessage()], 500);
    } else {
        http_response_code(500);
        die(json_encode(['success' => false, 'error' => 'Error interno: ' . $e->getMessage()]));
    }
}
?>