<?php
declare(strict_types=1);

namespace App\Modules\Accounting\Controllers;

use App\Core\Controller;
use App\Core\App;
use App\Core\Response;
use App\Modules\Accounting\Models\FiscalYear;
use App\Modules\Accounting\Models\CostCenter;
use App\Modules\Accounting\Models\AccountPayable;
use App\Modules\Accounting\Models\AccountReceivable;
use App\Modules\Accounting\Services\LedgerService;
use App\Modules\Accounting\Services\FinancialReportService;
use App\Modules\Accounting\Services\GLSyncService;
use App\Modules\Payments\Services\PaymentService;

class ReportController extends Controller
{
    public function dashboard(): Response
    {
        $this->authorize('accounting.reports.view');

        $db = App::getInstance()->db();
        $currentFY = FiscalYear::current();

        // Quick stats
        $today = date('Y-m-d');
        $monthStart = date('Y-m-01');

        $monthlyTotals = $db->selectOne(
            "SELECT COALESCE(SUM(total_debit), 0) as total_debit, COUNT(*) as count
             FROM journal_entries
             WHERE status = 'posted' AND entry_date >= ? AND entry_date <= ? AND is_archived = 0",
            [$monthStart, $today]
        );

        $arSummary = AccountReceivable::getAgingSummary();
        $apSummary = AccountPayable::getAgingSummary();

        $draftCount = $db->selectOne(
            "SELECT COUNT(*) as cnt FROM journal_entries WHERE status = 'draft' AND is_archived = 0"
        );

        // ── Payment stats from the payments table ──
        $paymentStats = [];

        // Today totals
        $todayRow = $db->selectOne(
            "SELECT COALESCE(SUM(amount), 0) as total, COUNT(*) as cnt
             FROM payments WHERE payment_date = ? AND is_voided = 0",
            [$today]
        );
        $paymentStats['today_total'] = $todayRow['total'] ?? '0.00';
        $paymentStats['today_count'] = (int) ($todayRow['cnt'] ?? 0);

        // Month totals
        $monthRow = $db->selectOne(
            "SELECT COALESCE(SUM(amount), 0) as total, COUNT(*) as cnt
             FROM payments WHERE payment_date >= ? AND payment_date <= ? AND is_voided = 0",
            [$monthStart, $today]
        );
        $paymentStats['month_total'] = $monthRow['total'] ?? '0.00';
        $paymentStats['month_count'] = (int) ($monthRow['cnt'] ?? 0);

        // By payment method (this month)
        $methodLabels = [
            'cash'          => 'نقدي',
            'check'         => 'شيك',
            'visa'          => 'فيزا',
            'bank_transfer' => 'تحويل بنكي',
        ];
        $byMethod = $db->select(
            "SELECT payment_method, COALESCE(SUM(amount), 0) as total, COUNT(*) as cnt
             FROM payments WHERE payment_date >= ? AND payment_date <= ? AND is_voided = 0
             GROUP BY payment_method ORDER BY total DESC",
            [$monthStart, $today]
        );
        $paymentStats['by_method'] = [];
        foreach ($byMethod as $m) {
            $paymentStats['by_method'][] = [
                'method' => $m['payment_method'],
                'label'  => $methodLabels[$m['payment_method']] ?? $m['payment_method'],
                'total'  => $m['total'],
                'count'  => $m['cnt'],
            ];
        }

        // By payment type (this month)
        $byType = $db->select(
            "SELECT payment_type, COALESCE(SUM(amount), 0) as total, COUNT(*) as cnt
             FROM payments WHERE payment_date >= ? AND payment_date <= ? AND is_voided = 0
             GROUP BY payment_type ORDER BY total DESC",
            [$monthStart, $today]
        );
        $paymentStats['by_type'] = [];
        foreach ($byType as $t) {
            $paymentStats['by_type'][] = [
                'type'  => $t['payment_type'],
                'label' => \App\Modules\Payments\Services\PaymentService::getPaymentTypeLabel($t['payment_type']),
                'total' => $t['total'],
                'count' => $t['cnt'],
            ];
        }

        // Recent payments (last 15)
        $recentPayments = $db->select(
            "SELECT p.*, r.receipt_number, m.full_name_ar as member_name
             FROM payments p
             LEFT JOIN receipts r ON r.id = p.receipt_id
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.is_voided = 0
             ORDER BY p.id DESC LIMIT 15"
        );
        $formattedPayments = [];
        foreach ($recentPayments as $rp) {
            $guestLabel = 'زائر';
            if (!$rp['member_id'] && $rp['notes']) {
                // Extract guest name from notes (format: "description — guestName")
                $parts = explode(' — ', $rp['notes']);
                if (count($parts) > 1) {
                    $guestLabel = end($parts);
                }
            }
            $formattedPayments[] = [
                'receipt_number' => $rp['receipt_number'],
                'payment_date'   => $rp['payment_date'],
                'member_id'      => $rp['member_id'] ? (int) $rp['member_id'] : null,
                'member_name'    => $rp['member_name'],
                'guest_label'    => $guestLabel,
                'type_label'     => \App\Modules\Payments\Services\PaymentService::getPaymentTypeLabel($rp['payment_type']),
                'method_label'   => $methodLabels[$rp['payment_method']] ?? $rp['payment_method'],
                'amount'         => $rp['amount'],
            ];
        }

        // Super admin check for sync button
        $isSuperAdmin = false;
        $employee = App::getInstance()->currentEmployee();
        if ($employee) {
            $isSuperAdmin = (bool) $db->selectOne(
                "SELECT 1 FROM employee_roles er JOIN roles r ON r.id = er.role_id
                 WHERE er.employee_id = ? AND r.role_code = 'super_admin' AND er.is_active = 1 LIMIT 1",
                [(int) $employee->id]
            );
        }

        // Count unsynced payments (payments without journal entries)
        $unsyncedCount = 0;
        if ($isSuperAdmin) {
            $unsyncedRow = $db->selectOne(
                "SELECT COUNT(*) as cnt FROM payments p
                 WHERE p.is_voided = 0 AND p.amount > 0
                   AND NOT EXISTS (
                       SELECT 1 FROM journal_entries je
                       WHERE je.reference_type = 'payment' AND je.reference_id = p.id
                         AND je.is_archived = 0 AND je.status != 'reversed'
                   )"
            );
            $unsyncedCount = (int) ($unsyncedRow['cnt'] ?? 0);
        }

        return $this->view('Accounting/Views/dashboard/index', [
            'fiscal_year'     => $currentFY ? $currentFY->toArray() : null,
            'monthly_totals'  => $monthlyTotals,
            'ar_summary'      => $arSummary,
            'ap_summary'      => $apSummary,
            'draft_count'     => (int) ($draftCount['cnt'] ?? 0),
            'payment_stats'   => $paymentStats,
            'recent_payments' => $formattedPayments,
            'is_super_admin'  => $isSuperAdmin,
            'unsynced_count'  => $unsyncedCount,
        ]);
    }

    public function trialBalance(): Response
    {
        $this->authorize('accounting.reports.trial_balance');

        $currentFY = FiscalYear::current();
        $dateFrom = $_GET['date_from'] ?? ($currentFY ? $currentFY->start_date : date('Y-01-01'));
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $result = LedgerService::getTrialBalance($dateFrom, $dateTo, null, $costCenterId, $branchId);

        $costCenters = CostCenter::getActive();
        $db = App::getInstance()->db();
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");

        return $this->view('Accounting/Views/reports/trial_balance', [
            'result'       => $result,
            'date_from'    => $dateFrom,
            'date_to'      => $dateTo,
            'cost_centers' => $costCenters,
            'branches'     => $branches,
            'filters'      => [
                'cost_center_id' => $costCenterId,
                'branch_id'      => $branchId,
            ],
        ]);
    }

    public function trialBalanceExportCsv(): Response
    {
        $this->authorize('accounting.reports.trial_balance');

        $currentFY = FiscalYear::current();
        $dateFrom = $_GET['date_from'] ?? ($currentFY ? $currentFY->start_date : date('Y-01-01'));
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $result = LedgerService::getTrialBalance($dateFrom, $dateTo, null, $costCenterId, $branchId);

        $filename = 'trial_balance_' . $dateFrom . '_to_' . $dateTo . '.csv';

        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        // BOM for Excel Arabic support
        fwrite($output, "\xEF\xBB\xBF");

        fputcsv($output, [
            'كود الحساب',
            'اسم الحساب',
            'رصيد أول - مدين',
            'رصيد أول - دائن',
            'حركة الفترة - مدين',
            'حركة الفترة - دائن',
            'رصيد آخر - مدين',
            'رصيد آخر - دائن',
        ]);

        foreach ($result['accounts'] as $acc) {
            fputcsv($output, [
                $acc['account_code'],
                $acc['name_ar'],
                $acc['opening_debit'],
                $acc['opening_credit'],
                $acc['period_debit'],
                $acc['period_credit'],
                $acc['closing_debit'],
                $acc['closing_credit'],
            ]);
        }

        fputcsv($output, [
            '',
            'الإجمالي',
            $result['total_opening_debit'],
            $result['total_opening_credit'],
            $result['total_period_debit'],
            $result['total_period_credit'],
            $result['total_closing_debit'],
            $result['total_closing_credit'],
        ]);

        fclose($output);
        exit;
    }

    public function generalLedger(): Response
    {
        $this->authorize('accounting.reports.general_ledger');

        $db = App::getInstance()->db();
        $accountId = (int) ($_GET['account_id'] ?? 0);
        $accountSearch = trim($_GET['account_search'] ?? '');
        $currentFY = FiscalYear::current();
        $dateFrom = $_GET['date_from'] ?? ($currentFY ? $currentFY->start_date : date('Y-01-01'));
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        if ($accountId === 0 && $accountSearch !== '') {
            $found = $db->selectOne(
                "SELECT id FROM chart_of_accounts WHERE is_archived = 0 AND (account_code = ? OR name_ar = ? OR name_en = ?) LIMIT 1",
                [$accountSearch, $accountSearch, $accountSearch]
            );
            if (!$found) {
                $found = $db->selectOne(
                    "SELECT id FROM chart_of_accounts WHERE is_archived = 0 AND (account_code LIKE ? OR name_ar LIKE ? OR name_en LIKE ?) LIMIT 1",
                    ["%{$accountSearch}%", "%{$accountSearch}%", "%{$accountSearch}%"]
                );
            }
            if ($found) {
                $accountId = (int) $found['id'];
            }
        }

        $ledger = null;
        if ($accountId > 0) {
            $ledger = LedgerService::getAccountLedger($accountId, $dateFrom, $dateTo, $costCenterId, $branchId);
        }

        $costCenters = CostCenter::getActive();
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");

        return $this->view('Accounting/Views/reports/general_ledger', [
            'ledger'         => $ledger,
            'account_id'     => $accountId,
            'account_search' => $accountSearch,
            'date_from'      => $dateFrom,
            'date_to'        => $dateTo,
            'cost_centers'   => $costCenters,
            'branches'       => $branches,
            'filters'        => [
                'cost_center_id' => $costCenterId,
                'branch_id'      => $branchId,
            ],
        ]);
    }

    public function incomeStatement(): Response
    {
        $this->authorize('accounting.reports.income_statement');

        $currentFY = FiscalYear::current();
        $dateFrom = $_GET['date_from'] ?? ($currentFY ? $currentFY->start_date : date('Y-01-01'));
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $result = FinancialReportService::getIncomeStatement($dateFrom, $dateTo, $costCenterId, $branchId);

        $costCenters = CostCenter::getActive();
        $db = App::getInstance()->db();
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");

        return $this->view('Accounting/Views/reports/income_statement', [
            'result'       => $result,
            'date_from'    => $dateFrom,
            'date_to'      => $dateTo,
            'cost_centers' => $costCenters,
            'branches'     => $branches,
            'filters'      => [
                'cost_center_id' => $costCenterId,
                'branch_id'      => $branchId,
            ],
        ]);
    }

    public function balanceSheet(): Response
    {
        $this->authorize('accounting.reports.balance_sheet');

        $asOfDate = $_GET['as_of_date'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $result = FinancialReportService::getBalanceSheet($asOfDate, $costCenterId, $branchId);

        $costCenters = CostCenter::getActive();
        $db = App::getInstance()->db();
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");

        return $this->view('Accounting/Views/reports/balance_sheet', [
            'result'       => $result,
            'as_of_date'   => $asOfDate,
            'cost_centers' => $costCenters,
            'branches'     => $branches,
            'filters'      => [
                'cost_center_id' => $costCenterId,
                'branch_id'      => $branchId,
            ],
        ]);
    }

    public function balanceSheetExportCsv(): Response
    {
        $this->authorize('accounting.reports.balance_sheet');

        $asOfDate = $_GET['as_of_date'] ?? date('Y-m-d');
        $costCenterId = !empty($_GET['cost_center_id']) ? (int) $_GET['cost_center_id'] : null;
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $result = FinancialReportService::getBalanceSheet($asOfDate, $costCenterId, $branchId);

        $filename = 'balance_sheet_' . $asOfDate . '.csv';

        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        fwrite($output, "\xEF\xBB\xBF");

        fputcsv($output, ['الميزانية العمومية حتى تاريخ ' . $asOfDate]);
        fputcsv($output, []);

        fputcsv($output, ['كود الحساب', 'اسم الحساب', 'الرصيد']);
        fputcsv($output, ['--- الأصول ---', '', '']);
        foreach ($result['assets'] as $acc) {
            fputcsv($output, [$acc['account_code'], $acc['name_ar'], $acc['balance']]);
        }
        fputcsv($output, ['', 'إجمالي الأصول', $result['total_assets']]);

        fputcsv($output, []);
        fputcsv($output, ['--- الخصوم ---', '', '']);
        foreach ($result['liabilities'] as $acc) {
            fputcsv($output, [$acc['account_code'], $acc['name_ar'], $acc['balance']]);
        }
        fputcsv($output, ['', 'إجمالي الخصوم', $result['total_liabilities']]);

        fputcsv($output, []);
        fputcsv($output, ['--- حقوق الملكية ---', '', '']);
        foreach ($result['equity'] as $acc) {
            fputcsv($output, [$acc['account_code'], $acc['name_ar'], $acc['balance']]);
        }
        fputcsv($output, ['', 'إجمالي حقوق الملكية', $result['total_equity']]);

        fputcsv($output, []);
        fputcsv($output, ['', 'إجمالي الخصوم + حقوق الملكية', $result['total_liabilities_and_equity']]);
        fputcsv($output, ['', 'متوازنة؟', $result['is_balanced'] ? 'نعم' : 'لا']);

        fclose($output);
        exit;
    }

    public function consolidatedBalanceSheet(): Response
    {
        $this->authorize('accounting.reports.consolidated');

        $asOfDate = $_GET['as_of_date'] ?? date('Y-m-d');
        $result = FinancialReportService::getConsolidatedBalanceSheet($asOfDate);

        return $this->view('Accounting/Views/reports/consolidated_balance_sheet', [
            'result'     => $result,
            'as_of_date' => $asOfDate,
        ]);
    }

    public function accountsReceivable(): Response
    {
        $this->authorize('accounting.reports.ar');

        $memberId = !empty($_GET['member_id']) ? (int) $_GET['member_id'] : null;
        $outstanding = AccountReceivable::getOutstanding($memberId);
        $aging = AccountReceivable::getAgingSummary();

        return $this->view('Accounting/Views/reports/accounts_receivable', [
            'outstanding' => $outstanding,
            'aging'       => $aging,
            'member_id'   => $memberId,
        ]);
    }

    public function accountsPayable(): Response
    {
        $this->authorize('accounting.reports.ap');

        $supplierId = !empty($_GET['supplier_id']) ? (int) $_GET['supplier_id'] : null;
        $outstanding = AccountPayable::getOutstanding($supplierId);
        $aging = AccountPayable::getAgingSummary();

        return $this->view('Accounting/Views/reports/accounts_payable', [
            'outstanding'  => $outstanding,
            'aging'        => $aging,
            'supplier_id'  => $supplierId,
        ]);
    }

    public function memberStatement(): Response
    {
        $this->authorize('accounting.reports.member_statement');

        $db = App::getInstance()->db();
        $memberId = (int) ($_GET['member_id'] ?? 0);
        $memberSearch = trim($_GET['member_search'] ?? '');
        $dateFrom = $_GET['date_from'] ?? date('Y-01-01');
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');

        if ($memberId === 0 && $memberSearch !== '') {
            $found = $db->selectOne(
                "SELECT id FROM members WHERE form_number = ? OR membership_number = ? LIMIT 1",
                [$memberSearch, $memberSearch]
            );
            if (!$found) {
                $found = $db->selectOne(
                    "SELECT id FROM members WHERE full_name_ar LIKE ? OR full_name_en LIKE ? OR phone LIKE ? LIMIT 1",
                    ["%{$memberSearch}%", "%{$memberSearch}%", "%{$memberSearch}%"]
                );
            }
            if ($found) {
                $memberId = (int) $found['id'];
            }
        }

        $entries = [];
        $member = null;
        if ($memberId > 0) {
            $member = $db->selectOne("SELECT id, full_name_ar, form_number FROM members WHERE id = ?", [$memberId]);
            $entries = LedgerService::getMemberStatement($memberId, $dateFrom, $dateTo);
        }

        return $this->view('Accounting/Views/reports/member_statement', [
            'member'        => $member,
            'entries'       => $entries,
            'member_id'     => $memberId,
            'member_search' => $memberSearch,
            'date_from'     => $dateFrom,
            'date_to'       => $dateTo,
        ]);
    }

    /**
     * Treasury & Payments report — all payments across all modules.
     */
    public function treasury(): Response
    {
        $this->authorize('accounting.reports.treasury');

        $db = App::getInstance()->db();

        $dateFrom = $_GET['date_from'] ?? date('Y-m-01');
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $paymentType = $_GET['payment_type'] ?? '';
        $paymentMethod = $_GET['payment_method'] ?? '';
        $search = trim($_GET['search'] ?? '');
        $voidedFilter = $_GET['voided'] ?? '0'; // 0 = active, 1 = voided, all = both

        $where = 'p.payment_date >= ? AND p.payment_date <= ?';
        $params = [$dateFrom, $dateTo];

        if ($voidedFilter === '0') {
            $where .= ' AND p.is_voided = 0';
        } elseif ($voidedFilter === '1') {
            $where .= ' AND p.is_voided = 1';
        }

        if ($paymentType !== '') {
            $where .= ' AND p.payment_type = ?';
            $params[] = $paymentType;
        }

        if ($paymentMethod !== '') {
            $where .= ' AND p.payment_method = ?';
            $params[] = $paymentMethod;
        }

        if ($search !== '') {
            $where .= ' AND (m.full_name_ar LIKE ? OR m.form_number LIKE ? OR r.receipt_number LIKE ? OR p.notes LIKE ?)';
            $searchTerm = '%' . $search . '%';
            $params = array_merge($params, [$searchTerm, $searchTerm, $searchTerm, $searchTerm]);
        }

        // Totals
        $totalsRow = $db->selectOne(
            "SELECT COALESCE(SUM(p.amount), 0) as total_amount, COUNT(*) as total_count
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             LEFT JOIN receipts r ON r.id = p.receipt_id
             WHERE {$where}",
            $params
        );

        // Paginated results
        $page = max(1, (int) ($_GET['page'] ?? 1));
        $perPage = 50;
        $offset = ($page - 1) * $perPage;
        $totalCount = (int) ($totalsRow['total_count'] ?? 0);
        $totalPages = max(1, (int) ceil($totalCount / $perPage));

        $payments = $db->select(
            "SELECT p.*, r.receipt_number, m.full_name_ar as member_name, m.form_number,
                    e.full_name_ar as received_by_name
             FROM payments p
             LEFT JOIN receipts r ON r.id = p.receipt_id
             LEFT JOIN members m ON m.id = p.member_id
             LEFT JOIN employees e ON e.id = p.received_by_employee_id
             WHERE {$where}
             ORDER BY p.id DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        // Summary by method (for the filtered period)
        $byMethod = $db->select(
            "SELECT p.payment_method, COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             LEFT JOIN receipts r ON r.id = p.receipt_id
             WHERE {$where}
             GROUP BY p.payment_method ORDER BY total DESC",
            $params
        );

        // Summary by type (for the filtered period)
        $byType = $db->select(
            "SELECT p.payment_type, COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             LEFT JOIN receipts r ON r.id = p.receipt_id
             WHERE {$where}
             GROUP BY p.payment_type ORDER BY total DESC",
            $params
        );

        $methodLabels = [
            'cash' => 'نقدي', 'check' => 'شيك', 'visa' => 'فيزا', 'bank_transfer' => 'تحويل بنكي',
        ];

        return $this->view('Accounting/Views/reports/treasury', [
            'payments'       => $payments,
            'total_amount'   => $totalsRow['total_amount'] ?? '0.00',
            'total_count'    => $totalCount,
            'by_method'      => $byMethod,
            'by_type'        => $byType,
            'method_labels'  => $methodLabels,
            'page'           => $page,
            'total_pages'    => $totalPages,
            'filters'        => [
                'date_from'      => $dateFrom,
                'date_to'        => $dateTo,
                'payment_type'   => $paymentType,
                'payment_method' => $paymentMethod,
                'search'         => $search,
                'voided'         => $voidedFilter,
            ],
        ]);
    }

    /**
     * Revenue Analysis — breakdown by type, method, period, branch.
     */
    public function revenueAnalysis(): Response
    {
        $this->authorize('accounting.reports.revenue_analysis');

        $db = App::getInstance()->db();
        $currentFY = FiscalYear::current();

        $dateFrom = $_GET['date_from'] ?? ($currentFY ? $currentFY->start_date : date('Y-01-01'));
        $dateTo = $_GET['date_to'] ?? date('Y-m-d');
        $branchId = !empty($_GET['branch_id']) ? (int) $_GET['branch_id'] : null;

        $branchWhere = '';
        $params = [$dateFrom, $dateTo];
        if ($branchId !== null) {
            $branchWhere = ' AND m.branch_id = ?';
            $params[] = $branchId;
        }

        // Revenue by payment type
        $byType = $db->select(
            "SELECT p.payment_type, COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 0 {$branchWhere}
             GROUP BY p.payment_type ORDER BY total DESC",
            $params
        );

        // Revenue by payment method
        $byMethod = $db->select(
            "SELECT p.payment_method, COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 0 {$branchWhere}
             GROUP BY p.payment_method ORDER BY total DESC",
            $params
        );

        // Monthly breakdown
        $monthly = $db->select(
            "SELECT DATE_FORMAT(p.payment_date, '%Y-%m') as month,
                    COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 0 {$branchWhere}
             GROUP BY month ORDER BY month ASC",
            $params
        );

        // Daily breakdown (current month only)
        $monthStart = date('Y-m-01');
        $dailyParams = [$monthStart, date('Y-m-d')];
        $dailyBranchWhere = '';
        if ($branchId !== null) {
            $dailyBranchWhere = ' AND m.branch_id = ?';
            $dailyParams[] = $branchId;
        }
        $daily = $db->select(
            "SELECT p.payment_date as day, COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 0 {$dailyBranchWhere}
             GROUP BY p.payment_date ORDER BY p.payment_date ASC",
            $dailyParams
        );

        // Grand total
        $grandTotal = '0.00';
        $grandCount = 0;
        foreach ($byType as $t) {
            $grandTotal = bcadd($grandTotal, (string) $t['total'], 2);
            $grandCount += (int) $t['cnt'];
        }

        // Revenue by branch (if no specific branch filter)
        $byBranch = [];
        if ($branchId === null) {
            $byBranch = $db->select(
                "SELECT COALESCE(b.name_ar, 'بدون فرع') as branch_name, b.id as branch_id,
                        COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
                 FROM payments p
                 LEFT JOIN members m ON m.id = p.member_id
                 LEFT JOIN branches b ON b.id = m.branch_id
                 WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 0
                 GROUP BY b.id, b.name_ar ORDER BY total DESC",
                [$dateFrom, $dateTo]
            );
        }

        // Voided payments summary
        $voidedParams = [$dateFrom, $dateTo];
        if ($branchId !== null) {
            $voidedParams[] = $branchId;
        }
        $voidedRow = $db->selectOne(
            "SELECT COALESCE(SUM(p.amount), 0) as total, COUNT(*) as cnt
             FROM payments p
             LEFT JOIN members m ON m.id = p.member_id
             WHERE p.payment_date >= ? AND p.payment_date <= ? AND p.is_voided = 1" .
            ($branchId !== null ? ' AND m.branch_id = ?' : ''),
            $voidedParams
        );

        $methodLabels = [
            'cash' => 'نقدي', 'check' => 'شيك', 'visa' => 'فيزا', 'bank_transfer' => 'تحويل بنكي',
        ];

        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");

        return $this->view('Accounting/Views/reports/revenue_analysis', [
            'by_type'       => $byType,
            'by_method'     => $byMethod,
            'by_branch'     => $byBranch,
            'monthly'       => $monthly,
            'daily'         => $daily,
            'grand_total'   => $grandTotal,
            'grand_count'   => $grandCount,
            'voided_total'  => $voidedRow['total'] ?? '0.00',
            'voided_count'  => (int) ($voidedRow['cnt'] ?? 0),
            'method_labels' => $methodLabels,
            'branches'      => $branches,
            'filters'       => [
                'date_from' => $dateFrom,
                'date_to'   => $dateTo,
                'branch_id' => $branchId,
            ],
        ]);
    }

    /**
     * Sync all existing financial data into the General Ledger.
     * Super-admin only. POST action from dashboard.
     */
    public function syncGL(): Response
    {
        $this->authorize('accounting.fiscal_year.manage');

        // Extra guard: super admin only
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $session = App::getInstance()->session();

        if ($employee) {
            $isSuperAdmin = $db->selectOne(
                "SELECT 1 FROM employee_roles er JOIN roles r ON r.id = er.role_id
                 WHERE er.employee_id = ? AND r.role_code = 'super_admin' AND er.is_active = 1 LIMIT 1",
                [(int) $employee->id]
            );
            if (!$isSuperAdmin) {
                $session->flash('_alerts', [['type' => 'error', 'message' => 'هذا الإجراء متاح فقط للمدير العام']]);
                return $this->redirect('/accounting');
            }
        }

        $result = GLSyncService::syncAll();

        $total = $result['payments'] + $result['fines'] + $result['installments']
               + $result['sales'] + $result['payroll'] + $result['refunds'] + $result['rentals'];

        $alerts = [];

        if ($total > 0) {
            $details = [];
            if ($result['payments'] > 0)     $details[] = $result['payments'] . ' مدفوعات';
            if ($result['fines'] > 0)        $details[] = $result['fines'] . ' غرامات';
            if ($result['installments'] > 0) $details[] = $result['installments'] . ' أقساط';
            if ($result['sales'] > 0)        $details[] = $result['sales'] . ' مبيعات';
            if ($result['payroll'] > 0)      $details[] = $result['payroll'] . ' رواتب';
            if ($result['refunds'] > 0)      $details[] = $result['refunds'] . ' مرتجعات';
            if ($result['rentals'] > 0)      $details[] = $result['rentals'] . ' تأمينات إيجار';

            $alerts[] = ['type' => 'success', 'message' => 'تم مزامنة ' . $total . ' قيد محاسبي (' . implode('، ', $details) . ')'];
        } else {
            $alerts[] = ['type' => 'success', 'message' => 'جميع البيانات المالية متزامنة بالفعل — لا توجد قيود جديدة'];
        }

        if ($result['errors'] > 0) {
            $alerts[] = ['type' => 'error', 'message' => 'فشل ' . $result['errors'] . ' عملية — راجع سجل الأخطاء'];
        }

        $session->flash('_alerts', $alerts);
        return $this->redirect('/accounting');
    }
}
