<?php
require_once 'config.php';

/**
 * SAC 2026 - GET DEBT LIST v1.0
 * Retorna un listado de propiedades con deudas acumuladas hasta el mes/año indicado.
 */

try {
    checkAccess(['admin', 'programmer', 'board']);
} catch (Exception $e) {
    response(["error" => "No autorizado"], 401);
}

$month = isset($_GET['month']) ? (int)$_GET['month'] - 1 : (int)date('n') - 1;
$year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');

try {
    // 1. Obtener todas las propiedades con sus dueños (residentes marcados como is_owner)
    $sql = "SELECT 
                p.id, 
                p.code as propertyCode, 
                p.number as propertyNumber, 
                p.initial_debt as agreementDebt,
                u.full_name as ownerName, 
                u.email, 
                r.phone as whatsapp
            FROM properties p
            LEFT JOIN residents r ON p.id = r.property_id AND r.is_owner = 1
            LEFT JOIN users u ON r.user_id = u.id
            ORDER BY p.code ASC";
    
    $stmt = $pdo->query($sql);
    $properties = $stmt->fetchAll();

    $debtList = [];

    foreach ($properties as $prop) {
        $propId = (int)$prop['id'];
        
        // 2. Calcular la deuda del mes actual seleccionado
        $currentSql = "SELECT SUM(pd.amount_usd - pd.paid_amount) as currentDebt
                       FROM property_debts pd
                       JOIN monthly_fees mf ON pd.fee_id = mf.id
                       WHERE pd.property_id = ? 
                       AND pd.status != 'Pagado'
                       AND mf.year = ? AND mf.month = ?";
        
        $currentStmt = $pdo->prepare($currentSql);
        $currentStmt->execute([$propId, $year, $month]);
        $currentDebt = (float)($currentStmt->fetchColumn() ?: 0);

        // 3. Calcular la deuda de meses anteriores
        $previousSql = "SELECT SUM(pd.amount_usd - pd.paid_amount) as previousDebt
                        FROM property_debts pd
                        JOIN monthly_fees mf ON pd.fee_id = mf.id
                        WHERE pd.property_id = ? 
                        AND pd.status != 'Pagado'
                        AND (mf.year < ? OR (mf.year = ? AND mf.month < ?))";
        
        $previousStmt = $pdo->prepare($previousSql);
        $previousStmt->execute([$propId, $year, $year, $month]);
        $previousDebt = (float)($previousStmt->fetchColumn() ?: 0);
        
        // 4. Deuda de Convenio = Deuda Inicial (Histórica) + Deudas de meses anteriores
        $totalAgreementDebt = round((float)$prop['agreementDebt'] + $previousDebt, 2);
        $totalDebt = round($currentDebt + $totalAgreementDebt, 2);

        if ($totalDebt > 0) {
            $debtList[] = [
                "propertyId" => $propId,
                "propertyCode" => $prop['propertyCode'],
                "propertyNumber" => $prop['propertyNumber'],
                "ownerName" => $prop['ownerName'] ?? 'Sin Propietario',
                "email" => $prop['email'] ?? 'N/A',
                "whatsapp" => $prop['whatsapp'] ?? 'N/A',
                "currentDebt" => $currentDebt,
                "agreementDebt" => $totalAgreementDebt,
                "totalDebt" => $totalDebt
            ];
        }
    }

    response($debtList);

} catch (PDOException $e) {
    response(["error" => "Error de base de datos: " . $e->getMessage()], 500);
}
?>