ibiza_sistema/models/Report.php

<?php

class Report {
    public static function getGeneralBalance($startDate = null, $endDate = null) {
        $db = Database::getInstance();

        $whereClause = '';
        $params = [];

        if ($startDate && $endDate) {
            $whereClause = " WHERE YEAR(cp.payment_date) BETWEEN ? AND ?";
            $params = [$startDate, $endDate];
        }

        $accessibleHouseIds = Auth::getAccessibleHouseIds();

        if (!empty($accessibleHouseIds) && !Auth::isAdmin()) {
            $placeholders = str_repeat('?,', count($accessibleHouseIds) - 1) . '?';

            if ($whereClause) {
                $whereClause .= " AND cp.house_id IN ($placeholders)";
            } else {
                $whereClause = " WHERE cp.house_id IN ($placeholders)";
            }

            $params = array_merge($params, $accessibleHouseIds);
        }

        $totalConceptPayments = $db->fetchOne(
            "SELECT COALESCE(SUM(cp.amount), 0) as total
             FROM finance_collection_payments cp
             LEFT JOIN finance_collection_concepts c ON cp.concept_id = c.id
             $whereClause",
            $params
        );
        $totalConceptPayments = $totalConceptPayments['total'] ?? 0;

        $totalExpenses = 0;
        $balance = $totalConceptPayments;

        if (Auth::isAdmin() || Auth::isCapturist()) {
            $expenseParams = [];
            $expenseWhere = '';

            if ($startDate && $endDate) {
                $expenseWhere = " WHERE YEAR(expense_date) BETWEEN ? AND ?";
                $expenseParams = [$startDate, $endDate];
            }

            $totalExpenses = $db->fetchOne(
                "SELECT COALESCE(SUM(amount), 0) as total
                 FROM expenses $expenseWhere",
                $expenseParams
            );
            $totalExpenses = $totalExpenses['total'] ?? 0;

            $balance = $totalConceptPayments - $totalExpenses;
        }

        return [
            'total_incomes' => $totalConceptPayments,
            'water_incomes' => 0,
            'concept_incomes' => $totalConceptPayments,
            'total_expenses' => $totalExpenses,
            'balance' => $balance
        ];
    }

    public static function getConceptDetailsByYear($year = null) {
        $db = Database::getInstance();
        $concepts = $db->fetchAll(
            "SELECT c.id, c.name, c.amount_per_house, c.concept_date, c.description
             FROM finance_collection_concepts c
             WHERE c.is_active = 1
             ORDER BY c.concept_date DESC"
        );

        $conceptsData = [];
        $totalExpected = 0;
        $totalCollected = 0;
        $totalExpenses = 0;

        foreach ($concepts as $concept) {
            $conceptYear = date('Y', strtotime($concept['concept_date']));

            if ($year && $conceptYear != $year) {
                continue;
            }

            $activeHousesCount = $db->fetchOne(
                "SELECT COUNT(*) as count FROM houses WHERE status = 'activa'"
            );
            $activeHousesCount = $activeHousesCount['count'] ?? 0;

            $expected = $concept['amount_per_house'] * $activeHousesCount;

            $whereClause = "WHERE cp.concept_id = ?";
            $params = [$concept['id']];

            if ($year) {
                $whereClause .= " AND YEAR(cp.payment_date) = ?";
                $params[] = $year;
            }

            $collected = $db->fetchOne(
                "SELECT COALESCE(SUM(cp.amount), 0) as total
                 FROM finance_collection_payments cp
                 $whereClause",
                $params
            );
            $collectedAmount = $collected['total'] ?? 0;

            $expenses = $db->fetchOne(
                "SELECT COALESCE(SUM(ec.amount), 0) as total
                 FROM expense_concept_allocations ec
                 JOIN expenses e ON ec.expense_id = e.id
                 WHERE ec.concept_id = ?
                 " . ($year ? "AND YEAR(e.expense_date) = ?" : ""),
                $year ? [$concept['id'], $year] : [$concept['id']]
            );
            $expensesAmount = $expenses['total'] ?? 0;

            $balance = $collectedAmount - $expensesAmount;

            $conceptsData[] = [
                'concept' => $concept,
                'year' => $conceptYear,
                'expected' => $expected,
                'collected' => $collectedAmount,
                'expenses' => $expensesAmount,
                'balance' => $balance,
                'pending' => max(0, $expected - $collectedAmount)
            ];

            $totalExpected += $expected;
            $totalCollected += $collectedAmount;
            $totalExpenses += $expensesAmount;
        }

        $totalBalance = $totalCollected - $totalExpenses;

        return [
            'concepts' => $conceptsData,
            'totals' => [
                'expected' => $totalExpected,
                'collected' => $totalCollected,
                'expenses' => $totalExpenses,
                'balance' => $totalBalance,
                'pending' => max(0, $totalExpected - $totalCollected)
            ],
            'year' => $year
        ];
    }

    public static function getHouseStatement($houseId, $year = null) {
        $db = Database::getInstance();
        $house = House::findById($houseId);

        $whereClause = $year ? " AND year = ?" : "";
        $params = $year ? [$houseId, $year] : [$houseId];

        $waterPayments = $db->fetchAll(
            "SELECT 'Agua' as type, month, amount, payment_date, notes 
             FROM payments 
             WHERE house_id = ? $whereClause
             ORDER BY year DESC, FIELD(month, 'Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre') DESC",
            $params
        );

        $conceptPayments = $db->fetchAll(
            "SELECT 'Concepto' as type, c.name as description, cp.amount, cp.payment_date, cp.notes 
             FROM finance_collection_payments cp 
             JOIN finance_collection_concepts c ON cp.concept_id = c.id 
             WHERE cp.house_id = ?" . 
            ($year ? " AND YEAR(cp.payment_date) = ?" : "") . "
             ORDER BY cp.payment_date DESC",
            $year ? [$houseId, $year] : [$houseId]
        );

        return [
            'house' => $house,
            'water_payments' => $waterPayments,
            'concept_payments' => $conceptPayments
        ];
    }

    public static function getPaymentsByYear($year) {
        $db = Database::getInstance();
        $months = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 
                   'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'];

        $data = [];
        foreach ($months as $month) {
            $result = $db->fetchOne(
                "SELECT COALESCE(SUM(amount), 0) as total, COUNT(*) as count 
                 FROM payments 
                 WHERE year = ? AND month = ?",
                [$year, $month]
            );
            $data[$month] = [
                'total' => $result['total'] ?? 0,
                'count' => $result['count'] ?? 0
            ];
        }

        return $data;
    }

    public static function getExpensesByCategory($startDate = null, $endDate = null) {
        $db = Database::getInstance();

        $sql = "SELECT category, COALESCE(SUM(amount), 0) as total
                FROM expenses";

        $params = [];

        if ($startDate && $endDate) {
            $sql .= " WHERE YEAR(expense_date) BETWEEN ? AND ?";
            $params = [$startDate, $endDate];
        }

        $sql .= " GROUP BY category ORDER BY total DESC";

        return $db->fetchAll($sql, $params);
    }

    public static function getCollectionReport($conceptId) {
        $concept = CollectionConcept::findById($conceptId);
        $status = CollectionConcept::getCollectionStatus($conceptId);
        $payments = CollectionConcept::getPaymentsByConcept($conceptId);

        return [
            'concept' => $concept,
            'status' => $status,
            'payments' => $payments
        ];
    }

    public static function getDashboardStats($year = null, $accessibleHouseIds = []) {
        $year = $year ?? date('Y');
        $db = Database::getInstance();

        if (!empty($accessibleHouseIds) && !Auth::isAdmin()) {
            $placeholders = str_repeat('?,', count($accessibleHouseIds) - 1) . '?';

            $totalHouses = count($accessibleHouseIds);

            $activeHousesResult = $db->fetchOne(
                "SELECT COUNT(*) as count
                 FROM houses
                 WHERE id IN ($placeholders) AND status = 'activa'",
                $accessibleHouseIds
            );
            $activeHouses = $activeHousesResult['count'] ?? 0;

            $conceptPayments = $db->fetchOne(
                "SELECT COALESCE(SUM(cp.amount), 0) as total
                 FROM finance_collection_payments cp
                 WHERE YEAR(cp.payment_date) = ? AND cp.house_id IN ($placeholders)",
                array_merge([$year], $accessibleHouseIds)
            );
            $conceptPayments = $conceptPayments['total'] ?? 0;

            $totalExpenses = 0;
            $balance = $conceptPayments;
        } else {
            $totalHouses = House::countAll();
            $activeHouses = House::countActive();

            $conceptPayments = $db->fetchOne(
                "SELECT COALESCE(SUM(cp.amount), 0) as total
                 FROM finance_collection_payments cp
                 WHERE YEAR(cp.payment_date) = ?",
                [$year]
            );
            $conceptPayments = $conceptPayments['total'] ?? 0;

            $totalExpenses = $db->fetchOne(
                "SELECT COALESCE(SUM(amount), 0) as total
                 FROM expenses
                 WHERE YEAR(expense_date) = ?",
                [$year]
            );
            $totalExpenses = $totalExpenses['total'] ?? 0;

            $balance = $conceptPayments - $totalExpenses;
        }

        $totalConcepts = $db->fetchOne(
            "SELECT COUNT(*) as count
             FROM finance_collection_concepts
             WHERE is_active = 1"
        );
        $totalConcepts = $totalConcepts['count'] ?? 0;

        return [
            'total_houses' => $totalHouses,
            'active_houses' => $activeHouses,
            'total_payments' => $conceptPayments,
            'total_expenses' => $totalExpenses,
            'balance' => $balance,
            'active_concepts' => $totalConcepts,
            'year' => $year
        ];
    }

    public static function getWaterDebtors($filters = []) {
        $db = Database::getInstance();
        $allMonths = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio',
                       'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'];

        $year = $filters['year'] ?? null;
        $months = $filters['months'] ?? $allMonths;
        $houseId = $filters['house_id'] ?? null;
        $accessibleHouseIds = $filters['accessible_house_ids'] ?? [];

        $whereHouse = '';
        $houseParams = [];

        if ($houseId) {
            $whereHouse = "AND h.id = ?";
            $houseParams = [$houseId];
        }

        $sql = "SELECT h.id, h.number, h.owner_name, h.status, h.consumption_only
                FROM houses h
                WHERE h.status = 'activa' $whereHouse";

        if (!empty($accessibleHouseIds) && !Auth::isAdmin()) {
            $placeholders = str_repeat('?,', count($accessibleHouseIds) - 1) . '?';
            $sql .= " AND h.id IN ($placeholders)";
            $houseParams = array_merge($houseParams, $accessibleHouseIds);
        }

        $sql .= " ORDER BY CAST(h.number AS UNSIGNED)";

        $houses = $db->fetchAll($sql, $houseParams);

        if ($year) {
            $yearsToCheck = [$year];
        } else {
            $years = $db->fetchAll("SELECT DISTINCT year FROM payments ORDER BY year");
            $yearsToCheck = array_column($years, 'year');
        }

        $debtors = [];
        $grandTotalExpected = 0;
        $grandTotalPaid = 0;

        foreach ($houses as $house) {
            $totalExpected = 0;
            $totalPaid = 0;
            $monthDetails = [];

            foreach ($yearsToCheck as $yr) {
                foreach ($months as $month) {
                    $expected = Payment::getExpectedAmount($house, $yr, $month);
                    $payment = $db->fetchOne(
                        "SELECT amount FROM payments WHERE house_id = ? AND year = ? AND month = ?",
                        [$house['id'], $yr, $month]
                    );
                    $paid = $payment['amount'] ?? 0;
                    $due = $expected - $paid;

                    $totalExpected += $expected;
                    $totalPaid += $paid;

                    if ($due > 0) {
                        $monthDetails[] = [
                            'year' => $yr,
                            'month' => $month,
                            'expected' => $expected,
                            'paid' => $paid,
                            'due' => $due
                        ];
                    }
                }
            }

            $houseTotalDue = $totalExpected - $totalPaid;

            if ($houseTotalDue > 0) {
                $debtors[] = [
                    'house_id' => $house['id'],
                    'house_number' => $house['number'],
                    'owner_name' => $house['owner_name'],
                    'months_due' => $monthDetails,
                    'total_due' => $houseTotalDue
                ];
            }

            $grandTotalExpected += $totalExpected;
            $grandTotalPaid += $totalPaid;
        }

        $grandTotalDue = $grandTotalExpected - $grandTotalPaid;

        return [
            'debtors' => $debtors,
            'total_due' => $grandTotalDue,
            'total_expected' => $grandTotalExpected,
            'total_paid' => $grandTotalPaid,
            'filters' => $filters
        ];
    }

    public static function getConceptDebtors($accessibleHouseIds = []) {
        $db = Database::getInstance();
        $concepts = $db->fetchAll(
            "SELECT c.id, c.name, c.amount_per_house
             FROM finance_collection_concepts c
             WHERE c.is_active = 1
             ORDER BY c.created_at DESC"
        );

        $debtors = [];
        $grandTotal = 0;

        foreach ($concepts as $concept) {
            $sql = "SELECT h.id, h.number, h.owner_name, h.status
                    FROM houses h
                    WHERE h.status = 'activa'";

            $params = [];

            if (!empty($accessibleHouseIds) && !Auth::isAdmin()) {
                $placeholders = str_repeat('?,', count($accessibleHouseIds) - 1) . '?';
                $sql .= " AND h.id IN ($placeholders)";
                $params = $accessibleHouseIds;
            }

            $sql .= " ORDER BY CAST(h.number AS UNSIGNED)";

            $houses = $db->fetchAll($sql, $params);

            $houseDebtors = [];
            $totalCollected = 0;
            $totalExpected = $concept['amount_per_house'] * count($houses);

            foreach ($houses as $house) {
                $payment = $db->fetchOne(
                    "SELECT amount FROM finance_collection_payments
                     WHERE concept_id = ? AND house_id = ?",
                    [$concept['id'], $house['id']]
                );
                $paid = $payment['amount'] ?? 0;

                if ($paid < $concept['amount_per_house']) {
                    $due = $concept['amount_per_house'] - $paid;
                    if ($due > 0) {
                        $houseDebtors[] = [
                            'house_id' => $house['id'],
                            'house_number' => $house['number'],
                            'owner_name' => $house['owner_name'],
                            'expected' => $concept['amount_per_house'],
                            'paid' => $paid,
                            'due' => $due
                        ];
                    }
                }
                $totalCollected += $paid;
            }

            $conceptTotalDue = $totalExpected - $totalCollected;

            if ($conceptTotalDue > 0) {
                $debtors[] = [
                    'concept_id' => $concept['id'],
                    'concept_name' => $concept['name'],
                    'amount_per_house' => $concept['amount_per_house'],
                    'total_expected' => $totalExpected,
                    'total_collected' => $totalCollected,
                    'total_due' => $conceptTotalDue,
                    'house_debtors' => $houseDebtors
                ];
                $grandTotal += $conceptTotalDue;
            }
        }

        return [
            'debtors' => $debtors,
            'total_due' => $grandTotal
        ];
    }

    public static function getConceptDebtorsFiltered($houseIds, $conceptIds = null) {
        $db = Database::getInstance();

        $whereConditions = [];
        $params = [];

        // Base conditions
        $whereConditions[] = "h.status = 'activa'";
        $whereConditions[] = "(cc.amount_per_house - COALESCE(cp.amount, 0)) > 0";

        // House filter
        if (!empty($houseIds)) {
            $placeholders = str_repeat('?,', count($houseIds) - 1) . '?';
            $whereConditions[] = "h.id IN ({$placeholders})";
            $params = array_merge($params, $houseIds);
        }

        // Concept filter
        if (!empty($conceptIds)) {
            $placeholders = str_repeat('?,', count($conceptIds) - 1) . '?';
            $whereConditions[] = "cc.id IN ({$placeholders})";
            $params = array_merge($params, $conceptIds);
        }

        $whereClause = implode(' AND ', $whereConditions);

        $query = "
            SELECT
                h.number as house_number,
                h.owner_name,
                cc.name as concept_name,
                cc.id as concept_id,
                cc.amount_per_house,
                COALESCE(cp.amount, 0) as paid_amount,
                (cc.amount_per_house - COALESCE(cp.amount, 0)) as debt_amount,
                cc.concept_date,
                cp.payment_date
            FROM houses h
            CROSS JOIN finance_collection_concepts cc
            LEFT JOIN finance_collection_payments cp ON cp.house_id = h.id AND cp.concept_id = cc.id
            WHERE {$whereClause}
            ORDER BY cc.name, h.number, cc.concept_date DESC
        ";

        $results = $db->fetchAll($query, $params);

        // Group by concept like the original method
        $debtors = [];
        $grandTotal = 0;

        $groupedResults = [];
        foreach ($results as $row) {
            $key = $row['concept_name'];
            if (!isset($groupedResults[$key])) {
                $groupedResults[$key] = [
                    'concept_name' => $row['concept_name'],
                    'concept_id' => $row['concept_id'],
                    'amount_per_house' => $row['amount_per_house'],
                    'house_debtors' => []
                ];
            }
            $groupedResults[$key]['house_debtors'][] = [
                'house_number' => $row['house_number'],
                'owner_name' => $row['owner_name'],
                'expected' => $row['amount_per_house'],
                'paid' => $row['paid_amount'],
                'due' => $row['debt_amount']
            ];
        }

        // Calculate totals for each concept
        foreach ($groupedResults as $concept) {
            $totalExpected = count($concept['house_debtors']) * $concept['amount_per_house'];
            $totalCollected = array_sum(array_column($concept['house_debtors'], 'paid'));
            $conceptTotalDue = $totalExpected - $totalCollected;

            if ($conceptTotalDue > 0) {
                $debtors[] = [
                    'concept_name' => $concept['concept_name'],
                    'concept_id' => $concept['concept_id'],
                    'amount_per_house' => $concept['amount_per_house'],
                    'total_expected' => $totalExpected,
                    'total_collected' => $totalCollected,
                    'total_due' => $conceptTotalDue,
                    'house_debtors' => $concept['house_debtors']
                ];
                $grandTotal += $conceptTotalDue;
            }
        }

        return [
            'debtors' => $debtors,
            'total_due' => $grandTotal
        ];
    }
}