<?php
$host = 'localhost';
$database = 'C:\Program Files (x86)\Common Files\Aspel\Sistemas Aspel\SAE9.00\Ejemplos\Ejemplos.fdb';
$username = 'SYSDBA'; 
$password = 'masterkey'; 

header('Content-Type: application/json');

try {
    $dbh = new PDO("firebird:host=$host;dbname=$database;charset=UTF8", $username, $password);

    $filter = isset($_GET['filter']) ? $_GET['filter'] : 'all';
    $startDate = isset($_GET['start_date']) ? $_GET['start_date'] : null;
    $endDate = isset($_GET['end_date']) ? $_GET['end_date'] : null;

    // Consultas SQL básicas
    $sqlTotal = "SELECT COUNT(*) FROM FACTV01 WHERE TIP_DOC = 'V'";
    $sqlEmitidas = "SELECT COUNT(*) FROM FACTV01 WHERE TIP_DOC = 'V' AND STATUS IN ('E', 'O')";
    $sqlCanceladas = "SELECT COUNT(*) FROM FACTV01 WHERE TIP_DOC = 'V' AND STATUS = 'C'";
    
    if ($filter === 'today') {
        $today = date('Y-m-d');
        $sqlTotal .= " AND CAST(FACTV01.FECHA_DOC AS DATE) = CAST('$today' AS DATE)";
        $sqlEmitidas .= " AND CAST(FACTV01.FECHA_DOC AS DATE) = CAST('$today' AS DATE)";
        $sqlCanceladas .= " AND CAST(FACTV01.FECHA_DOC AS DATE) = CAST('$today' AS DATE)";
    } elseif ($filter === 'this-month') {
        $currentMonth = date('Y-m');
        $sqlTotal .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = EXTRACT(YEAR FROM CURRENT_DATE) 
                       AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = EXTRACT(MONTH FROM CURRENT_DATE)";
        $sqlEmitidas .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = EXTRACT(YEAR FROM CURRENT_DATE) 
                          AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = EXTRACT(MONTH FROM CURRENT_DATE)";
        $sqlCanceladas .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = EXTRACT(YEAR FROM CURRENT_DATE) 
                            AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = EXTRACT(MONTH FROM CURRENT_DATE)";
    } elseif ($filter === 'last-month') {
        $lastMonth = date('Y-m', strtotime('first day of last month'));
        $year = date('Y', strtotime('first day of last month'));
        $month = date('m', strtotime('first day of last month'));
        $sqlTotal .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = $year 
                       AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = $month";
        $sqlEmitidas .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = $year 
                          AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = $month";
        $sqlCanceladas .= " AND EXTRACT(YEAR FROM FACTV01.FECHA_DOC) = $year 
                            AND EXTRACT(MONTH FROM FACTV01.FECHA_DOC) = $month";
    } elseif ($filter === 'custom' && $startDate && $endDate) {
        $sqlTotal .= " AND CAST(FACTV01.FECHA_DOC AS DATE) BETWEEN CAST('$startDate' AS DATE) AND CAST('$endDate' AS DATE)";
        $sqlEmitidas .= " AND CAST(FACTV01.FECHA_DOC AS DATE) BETWEEN CAST('$startDate' AS DATE) AND CAST('$endDate' AS DATE)";
        $sqlCanceladas .= " AND CAST(FACTV01.FECHA_DOC AS DATE) BETWEEN CAST('$startDate' AS DATE) AND CAST('$endDate' AS DATE)";
    }

    // Ejecutar consultas
    $stmtTotal = $dbh->prepare($sqlTotal);
    $stmtTotal->execute();
    $totalRegistros = $stmtTotal->fetchColumn();

    $stmtEmitidas = $dbh->prepare($sqlEmitidas);
    $stmtEmitidas->execute();
    $totalEmitidas = $stmtEmitidas->fetchColumn();

    $stmtCanceladas = $dbh->prepare($sqlCanceladas);
    $stmtCanceladas->execute();
    $totalCanceladas = $stmtCanceladas->fetchColumn();
    
    echo json_encode([
        'totalRegistros' => $totalRegistros,
        'totalEmitidas' => $totalEmitidas,
        'totalCanceladas' => $totalCanceladas
    ]);
    
} catch (PDOException $e) {
    echo json_encode(['error' => $e->getMessage()]);
}
?>
