<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Models\Client;
use App\Models\ForexTrade;
use App\Models\RentPayment;
use App\Core\Model;
use PDO;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Dompdf\Dompdf;
use Dompdf\Options;

class ReportController extends Controller
{
    /**
     * Global Reports Dashboard
     */
    public function index()
    {
        $this->requireAuth();
        $this->render('reports/index', ['pageTitle' => 'Reports']);
    }

    /**
     * Client Transaction Report with filters
     */
    public function clientTransactions()
    {
        $this->requireAuth();
        $clientModel = new Client();
        $clients = $clientModel->getAll();

        $fromDate = $_GET['from'] ?? date('Y-m-01');
        $toDate = $_GET['to'] ?? date('Y-m-t');
        $clientId = isset($_GET['client_id']) ? (int)$_GET['client_id'] : null;
        $type = $_GET['type'] ?? 'all';

        $results = $this->fetchClientTransactions($fromDate, $toDate, $clientId, $type, 500);

        $this->render('reports/client_transactions', [
            'clients' => $clients,
            'fromDate' => $fromDate,
            'toDate' => $toDate,
            'selectedClient' => $clientId,
            'selectedType' => $type,
            'results' => $results,
            'pageTitle' => 'Client Transaction Report'
        ]);
    }

    private function fetchClientTransactions(string $from, string $to, ?int $clientId, string $type, ?int $limit = null): array
{
    $pdo = Model::getPDO();

    $baseParams = [
        ':date_from' => $from . ' 00:00:00',
        ':date_to'   => $to . ' 23:59:59',
    ];

    // Client filter: apply to both parts of the UNION
    $clientCondition = '';
    if ($clientId) {
        $clientCondition = ' AND client_id = :client_id';
        $baseParams[':client_id'] = $clientId;
    }

    // Type filter for deposits (deposits are always credit)
    $depositTypeFilter = ($type === 'debit') ? ' AND 1=0' : '';

    // Type filter for client_transactions
    $ctTypeFilter = '';
    if ($type === 'credit') {
        $ctTypeFilter = " AND transaction_type = 'credit'";
    } elseif ($type === 'debit') {
        $ctTypeFilter = " AND transaction_type = 'debit'";
    }

    $sql = "
        SELECT 'Deposit' as source, d.id, d.client_id, c.full_name, c.client_code,
               d.amount, 'credit' as trx_type, d.deposit_date as trx_date,
               d.reference, d.notes
        FROM deposits d
        JOIN clients c ON d.client_id = c.id
        WHERE d.deposit_date BETWEEN :date_from AND :date_to
        $clientCondition
        $depositTypeFilter
        UNION ALL
        SELECT 'Transaction', ct.id, ct.client_id, c.full_name, c.client_code,
               ct.amount, ct.transaction_type, ct.transaction_date,
               ct.reference, ct.description as notes
        FROM client_transactions ct
        JOIN clients c ON ct.client_id = c.id
        WHERE ct.transaction_date BETWEEN :date_from AND :date_to
        $clientCondition
        $ctTypeFilter
        ORDER BY trx_date DESC
    ";

    if ($limit) {
        $sql .= " LIMIT :limit";
        $baseParams[':limit'] = $limit;
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($baseParams);
    return $stmt->fetchAll();
}

    /**
     * Export client transactions as PDF
     */
    public function exportPdf()
    {
        $this->requireAuth();
        $data = $this->filterDataFromRequest();
        $results = $this->fetchClientTransactions($data['from'], $data['to'], $data['clientId'], $data['type']);

        $html = $this->generateTransactionsPdfHtml($results, $data);

        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', true);
        $dompdf = new Dompdf($options);
        $dompdf->loadHtml($html);
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        $dompdf->stream('client_transactions_' . date('Ymd') . '.pdf', ['Attachment' => true]);
        exit;
    }

    /**
     * Export client transactions as Excel
     */
    public function exportExcel()
    {
        $this->requireAuth();
        $data = $this->filterDataFromRequest();
        $results = $this->fetchClientTransactions($data['from'], $data['to'], $data['clientId'], $data['type']);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A1', 'CLIENT TRANSACTIONS REPORT');
        $sheet->mergeCells('A1:F1');
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A2', 'Period: ' . $data['from'] . ' to ' . $data['to']);
        $sheet->mergeCells('A2:F2');

        $headers = ['Date', 'Client', 'Type', 'Amount (KES)', 'Reference', 'Notes'];
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '4', $header);
            $col++;
        }

        $headerStyle = $sheet->getStyle('A4:F4');
        $headerStyle->getFont()->setBold(true);
        $headerStyle->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FFCCCCCC');
        $headerStyle->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

        $row = 5;
        foreach ($results as $r) {
            $sheet->setCellValue('A' . $row, date('Y-m-d H:i', strtotime($r['trx_date'])));
            $sheet->setCellValue('B' . $row, $r['full_name'] . ' (' . $r['client_code'] . ')');
            $sheet->setCellValue('C' . $row, ucfirst($r['trx_type']));
            $sheet->setCellValue('D' . $row, $r['amount']);
            $sheet->setCellValue('E' . $row, $r['reference'] ?? '');
            $sheet->setCellValue('F' . $row, $r['notes'] ?? '');
            $row++;
        }

        $sheet->getStyle('D5:D' . ($row - 1))->getNumberFormat()->setFormatCode('#,##0.00');
        $sheet->getStyle('A4:F' . ($row - 1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

        foreach (range('A', 'F') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'client_transactions_' . date('Ymd') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    private function filterDataFromRequest(): array
    {
        return [
            'from'     => $_GET['from'] ?? date('Y-m-01'),
            'to'       => $_GET['to'] ?? date('Y-m-t'),
            'clientId' => isset($_GET['client_id']) ? (int)$_GET['client_id'] : null,
            'type'     => $_GET['type'] ?? 'all',
        ];
    }

    private function generateTransactionsPdfHtml(array $results, array $data): string
    {
        ob_start();
        ?>
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="UTF-8">
            <title>Client Transactions Report</title>
            <style>
                body { font-family: DejaVu Sans, sans-serif; font-size: 12px; }
                table { width: 100%; border-collapse: collapse; }
                th { background-color: #f2f2f2; }
                th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                .text-end { text-align: right; }
            </style>
        </head>
        <body>
            <h2 style="text-align:center">Client Transactions Report</h2>
            <p>Period: <?= htmlspecialchars($data['from']) ?> to <?= htmlspecialchars($data['to']) ?></p>
            <table>
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Client</th>
                        <th>Type</th>
                        <th>Amount (KES)</th>
                        <th>Reference</th>
                        <th>Notes</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($results as $r): ?>
                    <tr>
                        <td><?= date('Y-m-d H:i', strtotime($r['trx_date'])) ?></td>
                        <td><?= htmlspecialchars($r['full_name']) ?> (<?= $r['client_code'] ?>)</td>
                        <td><?= ucfirst($r['trx_type']) ?></td>
                        <td class="text-end"><?= number_format($r['amount'], 2) ?></td>
                        <td><?= htmlspecialchars($r['reference'] ?? '') ?></td>
                        <td><?= htmlspecialchars($r['notes'] ?? '') ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </body>
        </html>
        <?php
        return ob_get_clean();
    }

    /**
     * Forex Summary Report
     */
    public function forexSummary()
    {
        $this->requireAuth();
        $from = $_GET['from'] ?? date('Y-01-01');
        $to = $_GET['to'] ?? date('Y-m-t');

        $pdo = Model::getPDO();
        $stmt = $pdo->prepare("
            SELECT c.code, 
                   SUM(CASE WHEN ft.trade_type = 'buy' THEN ft.amount ELSE 0 END) as total_bought,
                   SUM(CASE WHEN ft.trade_type = 'sell' THEN ft.amount ELSE 0 END) as total_sold,
                   AVG(CASE WHEN ft.trade_type = 'buy' THEN ft.rate ELSE NULL END) as avg_buy_rate,
                   AVG(CASE WHEN ft.trade_type = 'sell' THEN ft.rate ELSE NULL END) as avg_sell_rate,
                   SUM(CASE WHEN ft.trade_type = 'sell' THEN ft.kes_amount ELSE 0 END) - 
                   SUM(CASE WHEN ft.trade_type = 'buy' THEN ft.kes_amount ELSE 0 END) as net_kes_flow
            FROM forex_trades ft
            JOIN currencies c ON ft.currency_id = c.id
            WHERE ft.trade_date BETWEEN ? AND ?
            GROUP BY c.code
        ");
        $stmt->execute([$from . ' 00:00:00', $to . ' 23:59:59']);
        $summary = $stmt->fetchAll();

        $this->render('reports/forex_summary', [
            'summary' => $summary,
            'fromDate' => $from,
            'toDate' => $to,
            'pageTitle' => 'Forex Summary'
        ]);
    }

    /**
     * Rent Collection Report
     */
    public function rentReport()
    {
        $this->requireAuth();
        $from = $_GET['from'] ?? date('Y-01-01');
        $to = $_GET['to'] ?? date('Y-m-t');

        $pdo = Model::getPDO();
        $stmt = $pdo->prepare("
            SELECT p.name as property, p.tenant_name, 
                   SUM(rp.amount) as total_collected, COUNT(*) as payments
            FROM rent_payments rp
            JOIN properties p ON rp.property_id = p.id
            WHERE rp.payment_date BETWEEN ? AND ?
            GROUP BY p.id
            ORDER BY total_collected DESC
        ");
        $stmt->execute([$from . ' 00:00:00', $to . ' 23:59:59']);
        $rentData = $stmt->fetchAll();

        $total = array_sum(array_column($rentData, 'total_collected'));

        $this->render('reports/rent_report', [
            'rentData' => $rentData,
            'total' => $total,
            'fromDate' => $from,
            'toDate' => $to,
            'pageTitle' => 'Rent Report'
        ]);
    }
}