<?php
require_once __DIR__ . '/includes/cors.php';
configureCors();
require_once __DIR__ . '../../backend-main/includes/config_loader.php';
require_once __DIR__ . '../../backend-main/includes/config.php';
require_once  './includes/functions.php';
require_once './vendor/autoload.php';
require_once './tcpdf/tcpdf.php';

/* ================= SESSION ================= */
if (session_status() === PHP_SESSION_NONE) {
    session_start([
        'cookie_httponly' => true,
        'cookie_secure'  => true,     // HTTPS REQUIRED
        'cookie_samesite'=> 'None'
    ]);
}

/* ================= CORS HEADERS ================= */
$origin = $_SERVER['HTTP_ORIGIN'] ?? '';
header("Access-Control-Allow-Origin: $origin");
header('Access-Control-Allow-Credentials: true');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization, X-Requested-With, X-Session-ID');
header('Content-Type: application/json');

/* ================= SECURITY HEADERS ================= */
header('X-Frame-Options: DENY');
header('X-Content-Type-Options: nosniff');
header('X-XSS-Protection: 1; mode=block');

/* ================= PREFLIGHT ================= */
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

/* ================= DEBUG (DEV ONLY) ================= */
if (ENVIRONMENT === 'development') {
    error_log("Session ID: " . session_id());
    error_log("Session data: " . print_r($_SESSION, true));
    error_log("Cookies: " . print_r($_COOKIE, true));
}

/* ================= AUTH CHECK ================= */
if (empty($_SESSION['member_id'])) {
    http_response_code(401);
    echo json_encode([
        'success' => false,
        'error'   => 'Unauthorized - Please login first',
        'code'    => 'SESSION_EXPIRED'
    ]);
    exit;
}

/* ================= LOAD DEPENDENCIES ================= */


/* ================= REQUEST DATA ================= */
$method    = $_SERVER['REQUEST_METHOD'];
$action    = $_GET['action'] ?? '';
$memberId = (int)$_SESSION['member_id'];
// ============================================
// HELPER FUNCTIONS
// ============================================

/**
 * Input validator class
 */
class StatementInputValidator {
    public static function validateSubAccountCode($code) {
        // Only allow alphanumeric, underscores, and hyphens
        return preg_match('/^[a-zA-Z0-9_-]+$/', $code) ? $code : null;
    }
    
    public static function validateDates($startDate, $endDate, $maxPeriod = 365) {
        try {
            $defaultStart = date('Y-m-01');
            $defaultEnd = date('Y-m-t');
            
            if (empty($startDate) || !strtotime($startDate)) {
                $startDate = $defaultStart;
            }
            
            if (empty($endDate) || !strtotime($endDate)) {
                $endDate = $defaultEnd;
            }
            
            $start = new DateTime($startDate);
            $end = new DateTime($endDate);
            
            // Ensure start date is not after end date
            if ($end < $start) {
                $temp = $startDate;
                $startDate = $endDate;
                $endDate = $temp;
                $start = new DateTime($startDate);
                $end = new DateTime($endDate);
            }
            
            // Limit to maximum period for performance
            $maxEndDate = clone $start;
            $maxEndDate->modify("+{$maxPeriod} days");
            
            if ($end > $maxEndDate) {
                $end = $maxEndDate;
                $endDate = $end->format('Y-m-d');
            }
            
            return [$startDate, $endDate];
            
        } catch (Exception $e) {
            return [$defaultStart, $defaultEnd];
        }
    }
    
    public static function validateAccountAccess($pdo, $memberId, $accountTypeId, $subAccountCode = null) {
        try {
            $accountTypeId = filter_var($accountTypeId, FILTER_VALIDATE_INT, [
                'options' => ['min_range' => 1]
            ]);
            
            if (!$accountTypeId) {
                throw new InvalidArgumentException('Invalid account type ID');
            }
            
            // Verify member has access to this account
            $sql = "SELECT 1 FROM member_accounts 
                    WHERE member_id = ? 
                    AND account_type_id = ? 
                    AND status = 'active'";
            
            $params = [$memberId, $accountTypeId];
            
            if ($subAccountCode) {
                $sql .= " AND sub_account_code = ?";
                $params[] = $subAccountCode;
            }
            
            $sql .= " LIMIT 1";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            
            if (!$stmt->fetch()) {
                throw new InvalidArgumentException('Account not accessible');
            }
            
            return true;
            
        } catch (PDOException $e) {
            throw new RuntimeException("Database error during account validation");
        }
    }
}

/**
 * Get member details
 */
function getMemberDetails($pdo, $memberId) {
    try {
        $stmt = $pdo->prepare("
            SELECT id, first_name, last_name, member_number, email_address, phone_number
            FROM members 
            WHERE id = ? AND status = 'active'
        ");
        $stmt->execute([$memberId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        throw new RuntimeException("Failed to fetch member details: " . $e->getMessage());
    }
}

/**
 * Get member account types
 */
function getMemberAccountTypes($pdo, $memberId) {
    try {
        $stmt = $pdo->prepare("
            SELECT DISTINCT at.id, at.account_name, at.type_code
            FROM account_types at
            JOIN member_accounts ma ON at.id = ma.account_type_id
            WHERE ma.member_id = ? AND ma.status = 'active'
            ORDER BY at.account_name
        ");
        $stmt->execute([$memberId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        throw new RuntimeException("Failed to fetch account types: " . $e->getMessage());
    }
}

/**
 * Get account details
 */
function getAccountDetails($pdo, $memberId, $accountTypeId, $subAccountCode) {
    try {
        $stmt = $pdo->prepare("
            SELECT ma.*, at.account_name, at.type_code
            FROM member_accounts ma
            JOIN account_types at ON ma.account_type_id = at.id
            WHERE ma.member_id = ? 
            AND ma.account_type_id = ? 
            AND ma.sub_account_code = ?
            AND ma.status = 'active'
            LIMIT 1
        ");
        $stmt->execute([$memberId, $accountTypeId, $subAccountCode]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        throw new RuntimeException("Failed to fetch account details: " . $e->getMessage());
    }
}

/**
 * Calculate opening balance
 */
function calculateOpeningBalance($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate) {
    try {
        $stmt = $pdo->prepare("
            SELECT SUM(
                CASE 
                    WHEN tt.is_credit = 1 THEN t.amount 
                    ELSE -t.amount 
                END
            ) AS balance
            FROM transactions t
            JOIN transaction_types tt ON t.transaction_type_id = tt.id
            WHERE t.member_id = ?
            AND t.account_type_id = ?
            AND t.sub_account_code = ?
            AND t.transaction_date < ?
            AND t.status = 'completed'
        ");
        $stmt->execute([
            $memberId,
            $accountTypeId,
            $subAccountCode,
            $startDate
        ]);
        return (float)($stmt->fetchColumn() ?? 0);
    } catch (PDOException $e) {
        throw new RuntimeException("Failed to calculate opening balance: " . $e->getMessage());
    }
}

/**
 * Get transactions for period
 */
function getTransactions($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate, $endDate) {
    try {
        $stmt = $pdo->prepare("
            SELECT 
                t.id,
                t.transaction_date,
                t.description,
                t.amount,
                t.reference_number,
                t.created_at,
                t.transaction_type,
                t.payment_method,
                t.transaction_status,
                u.username AS processed_by
            FROM transactions
            LEFT JOIN users u ON t.user_id = u.id
            WHERE t.member_id = ?
            AND t.account_type_id = ?
            AND t.sub_account_code = ?
            AND t.transaction_date BETWEEN ? AND ?
            AND t.status = 'completed'
            ORDER BY t.transaction_date, t.created_at, t.id
        ");
        $stmt->execute([
            $memberId,
            $accountTypeId,
            $subAccountCode,
            $startDate,
            $endDate
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        throw new RuntimeException("Failed to fetch transactions: " . $e->getMessage());
    }
}

/**
 * Get company details
 */
function getCompanyDetails($pdo) {
    try {
        $stmt = $pdo->query("SELECT * FROM company_settings LIMIT 1");
        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        // Return default company details
        return [
            'company_name' => 'Financial Institution',
            'address' => '',
            'phone' => '',
            'email' => '',
            'website' => '',
            'logo_url' => ''
        ];
    }
}

/**
 * Calculate transaction balances
 */
function calculateTransactionBalances($transactions, $openingBalance) {
    $runningBalance = $openingBalance;
    $totalCredits = 0;
    $totalDebits = 0;
    
    foreach ($transactions as &$transaction) {
        $amount = (float)$transaction['amount'];
        
        if ($transaction['is_credit']) {
            $totalCredits += $amount;
            $runningBalance += $amount;
            $transaction['amount_type'] = 'credit';
            $transaction['amount_formatted'] = '+' . number_format($amount, 2);
        } else {
            $totalDebits += $amount;
            $runningBalance -= $amount;
            $transaction['amount_type'] = 'debit';
            $transaction['amount_formatted'] = '-' . number_format($amount, 2);
        }
        
        $transaction['balance_after'] = $runningBalance;
        $transaction['balance_after_formatted'] = number_format($runningBalance, 2);
        $transaction['transaction_date_formatted'] = date('M j, Y', strtotime($transaction['transaction_date']));
        $transaction['created_at_formatted'] = date('M j, Y H:i', strtotime($transaction['created_at']));
        $transaction['amount_numeric'] = $amount;
    }
    
    return [
        'transactions' => $transactions,
        'closing_balance' => $runningBalance,
        'total_credits' => $totalCredits,
        'total_debits' => $totalDebits,
        'net_movement' => $totalCredits - $totalDebits
    ];
}

// ============================================
// EXPORT FUNCTIONS
// ============================================

/**
 * Generate Excel statement using PhpSpreadsheet
 */
function generateExcelStatement($statementData, $member, $company) {
    // Check if PhpSpreadsheet is available
    if (!EXPORT_LIBRARIES_AVAILABLE) {
        throw new RuntimeException('Excel export requires Composer libraries to be installed');
    }
    
    try {
        // Check if PhpSpreadsheet classes exist
        if (!class_exists('PhpOffice\PhpSpreadsheet\Spreadsheet')) {
            throw new RuntimeException('PhpSpreadsheet library not found');
        }
        
        // Use the fully qualified names
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Account Statement');
        
        // Current row counter
        $row = 1;
        
        // ========== HEADER SECTION ==========
        // Company Header
        $sheet->setCellValue('A' . $row, $company['company_name'] ?? 'Financial Institution');
        $sheet->mergeCells('A' . $row . ':G' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(16);
        $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'ACCOUNT STATEMENT');
        $sheet->mergeCells('A' . $row . ':G' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $row += 2;
        
        // Member Information
        $sheet->setCellValue('A' . $row, 'Member Information:');
        $sheet->getStyle('A' . $row)->getFont()->setBold(true);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Name:');
        $sheet->setCellValue('B' . $row, $member['first_name'] . ' ' . $member['last_name']);
        $sheet->setCellValue('D' . $row, 'Member #:');
        $sheet->setCellValue('E' . $row, $member['member_number']);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Account:');
        $sheet->setCellValue('B' . $row, $statementData['account']['account_name'] ?? '');
        $sheet->setCellValue('D' . $row, 'Account #:');
        $sheet->setCellValue('E' . $row, $statementData['account']['account_number'] ?? '');
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Period:');
        $sheet->setCellValue('B' . $row, $statementData['metadata']['statement_period'] ?? '');
        $row += 2;
        
        // ========== SUMMARY SECTION ==========
        $summaryStartRow = $row;
        $sheet->setCellValue('A' . $row, 'STATEMENT SUMMARY');
        $sheet->mergeCells('A' . $row . ':G' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true);
        $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A' . $row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Opening Balance:');
        $sheet->setCellValue('B' . $row, $statementData['summary']['opening_balance_formatted'] ?? '0.00');
        $sheet->getStyle('B' . $row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Total Credits:');
        $sheet->setCellValue('B' . $row, $statementData['summary']['total_credits_formatted'] ?? '0.00');
        $sheet->getStyle('B' . $row)->getFont()->getColor()->setRGB('008000');
        $sheet->getStyle('B' . $row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Total Debits:');
        $sheet->setCellValue('B' . $row, $statementData['summary']['total_debits_formatted'] ?? '0.00');
        $sheet->getStyle('B' . $row)->getFont()->getColor()->setRGB('FF0000');
        $sheet->getStyle('B' . $row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Net Movement:');
        $sheet->setCellValue('B' . $row, $statementData['summary']['net_movement_formatted'] ?? '0.00');
        $netStyle = $sheet->getStyle('B' . $row);
        $netStyle->getFont()->setBold(true);
        $netStyle->getFont()->getColor()->setRGB(
            ($statementData['summary']['net_movement'] ?? 0) >= 0 ? '008000' : 'FF0000'
        );
        $netStyle->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;
        
        $sheet->setCellValue('A' . $row, 'Closing Balance:');
        $sheet->setCellValue('B' . $row, $statementData['summary']['closing_balance_formatted'] ?? '0.00');
        $closingStyle = $sheet->getStyle('B' . $row);
        $closingStyle->getFont()->setBold(true);
        $closingStyle->getFont()->setSize(12);
        $closingStyle->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row += 2;
        
        // Apply borders to summary section
        $summaryRange = 'A' . $summaryStartRow . ':B' . ($row - 3);
        $sheet->getStyle($summaryRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        
        // ========== TRANSACTIONS SECTION ==========
        $transactions = $statementData['transactions'] ?? [];
        if (!empty($transactions)) {
            $transactionsStartRow = $row;
            
            // Transactions Header
            $headers = ['Date', 'Description', 'Reference', 'Type', 'Debit', 'Credit', 'Balance'];
            $col = 0;
            foreach ($headers as $header) {
                $cell = chr(65 + $col) . $row;
                $sheet->setCellValue($cell, $header);
                $sheet->getStyle($cell)->getFont()->setBold(true);
                $sheet->getStyle($cell)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle($cell)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
                $col++;
            }
            $row++;
            
            // Transactions Data
            foreach ($transactions as $transaction) {
                $sheet->setCellValue('A' . $row, $transaction['transaction_date_formatted'] ?? '');
                $sheet->setCellValue('B' . $row, $transaction['description'] ?? '');
                $sheet->setCellValue('C' . $row, $transaction['reference_number'] ?? '');
                $sheet->setCellValue('D' . $row, $transaction['transaction_type_name'] ?? '');
                
                // Debit/Credit columns
                if (($transaction['amount_type'] ?? '') === 'debit') {
                    $sheet->setCellValue('E' . $row, abs($transaction['amount_numeric'] ?? 0));
                    $sheet->setCellValue('F' . $row, '');
                } else {
                    $sheet->setCellValue('E' . $row, '');
                    $sheet->setCellValue('F' . $row, abs($transaction['amount_numeric'] ?? 0));
                }
                
                $sheet->setCellValue('G' . $row, $transaction['balance_after'] ?? 0);
                
                // Format number columns
                $sheet->getStyle('E' . $row . ':G' . $row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                
                // Color coding
                if (($transaction['amount_type'] ?? '') === 'credit') {
                    $sheet->getStyle('F' . $row)->getFont()->getColor()->setRGB('008000');
                } elseif (($transaction['amount_type'] ?? '') === 'debit') {
                    $sheet->getStyle('E' . $row)->getFont()->getColor()->setRGB('FF0000');
                }
                
                $row++;
            }
            
            // Apply borders to transactions
            $transactionsRange = 'A' . $transactionsStartRow . ':G' . ($row - 1);
            $sheet->getStyle($transactionsRange)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            
            // Auto-size columns
            foreach (range('A', 'G') as $column) {
                $sheet->getColumnDimension($column)->setAutoSize(true);
            }
        }
        
        // ========== FOOTER SECTION ==========
        $row += 2;
        $sheet->setCellValue('A' . $row, 'Generated: ' . ($statementData['metadata']['generated_at_formatted'] ?? date('F j, Y \a\t H:i')));
        $sheet->mergeCells('A' . $row . ':G' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setItalic(true);
        $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        
        // Generate filename
        $filename = 'statement_' . 
                    $member['member_number'] . '_' . 
                    $statementData['account']['account_number'] . '_' . 
                    date('Ymd_His') . '.xlsx';
        
        // Set headers for download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        // Create writer and output
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit();
        
    } catch (Exception $e) {
        throw new RuntimeException("Excel generation failed: " . $e->getMessage());
    }
}

/**
 * Generate PDF statement using TCPDF
 */
function generatePdfStatement($statementData, $member, $company) {
    // Check if TCPDF is available
    if (!EXPORT_LIBRARIES_AVAILABLE) {
        throw new RuntimeException('PDF export requires Composer libraries to be installed');
    }
    
    try {
        // Check if TCPDF class exists
        if (!class_exists('TCPDF')) {
            throw new RuntimeException('TCPDF library not found');
        }
        
        // Create new PDF document
        $pdf = new TCPDF('P', 'mm', 'A4', true, 'UTF-8', false);
        
        // Set document information
        $pdf->SetCreator(PDF_CREATOR);
        $pdf->SetAuthor($company['company_name'] ?? 'Financial Institution');
        $pdf->SetTitle('Account Statement - ' . $member['first_name'] . ' ' . $member['last_name']);
        $pdf->SetSubject('Account Statement');
        
        // Set margins
        $pdf->SetMargins(15, 25, 15);
        $pdf->SetHeaderMargin(10);
        $pdf->SetFooterMargin(10);
        
        // Set auto page breaks
        $pdf->SetAutoPageBreak(TRUE, 15);
        
        // Add a page
        $pdf->AddPage();
        
        // ========== HEADER SECTION ==========
        $html = '
        <style>
            .header { text-align: center; margin-bottom: 20px; }
            .company-name { font-size: 18px; font-weight: bold; color: #333; }
            .statement-title { font-size: 16px; font-weight: bold; margin: 10px 0; color: #555; }
            .member-info { margin-bottom: 15px; }
            .info-row { margin: 5px 0; }
            .label { font-weight: bold; display: inline-block; width: 120px; }
            .summary-box { border: 1px solid #ddd; padding: 15px; margin-bottom: 20px; background-color: #f9f9f9; }
            .summary-title { font-weight: bold; margin-bottom: 10px; text-align: center; }
            .summary-row { margin: 5px 0; }
            .credit { color: #008000; }
            .debit { color: #FF0000; }
            .total { font-weight: bold; }
            table { width: 100%; border-collapse: collapse; margin: 20px 0; }
            th { background-color: #f2f2f2; font-weight: bold; padding: 8px; text-align: center; border: 1px solid #ddd; }
            td { padding: 8px; border: 1px solid #ddd; }
            .date-col { width: 15%; }
            .desc-col { width: 25%; }
            .ref-col { width: 15%; }
            .type-col { width: 15%; }
            .amount-col { width: 15%; }
            .balance-col { width: 15%; }
            .footer { margin-top: 30px; font-size: 10px; color: #666; text-align: center; }
        </style>
        ';
        
        // Company Header
        $html .= '
        <div class="header">
            <div class="company-name">' . htmlspecialchars($company['company_name'] ?? 'Financial Institution') . '</div>
            <div class="statement-title">ACCOUNT STATEMENT</div>
        </div>
        ';
        
        // Member Information
        $html .= '
        <div class="member-info">
            <div class="info-row"><span class="label">Member Name:</span>' . 
            htmlspecialchars($member['first_name'] . ' ' . $member['last_name']) . '</div>
            <div class="info-row"><span class="label">Member Number:</span>' . 
            htmlspecialchars($member['member_number']) . '</div>
            <div class="info-row"><span class="label">Account:</span>' . 
            htmlspecialchars($statementData['account']['account_name'] ?? '') . '</div>
            <div class="info-row"><span class="label">Account Number:</span>' . 
            htmlspecialchars($statementData['account']['account_number'] ?? '') . '</div>
            <div class="info-row"><span class="label">Statement Period:</span>' . 
            htmlspecialchars($statementData['metadata']['statement_period'] ?? '') . '</div>
        </div>
        ';
        
        // ========== SUMMARY SECTION ==========
        $html .= '
        <div class="summary-box">
            <div class="summary-title">STATEMENT SUMMARY</div>
            <div class="summary-row"><span class="label">Opening Balance:</span> ' . 
            htmlspecialchars($statementData['summary']['opening_balance_formatted'] ?? '0.00') . '</div>
            <div class="summary-row"><span class="label">Total Credits:</span> <span class="credit">' . 
            htmlspecialchars($statementData['summary']['total_credits_formatted'] ?? '0.00') . '</span></div>
            <div class="summary-row"><span class="label">Total Debits:</span> <span class="debit">' . 
            htmlspecialchars($statementData['summary']['total_debits_formatted'] ?? '0.00') . '</span></div>
            <div class="summary-row"><span class="label">Net Movement:</span> <span class="' . 
            (($statementData['summary']['net_movement'] ?? 0) >= 0 ? 'credit' : 'debit') . ' total">' . 
            htmlspecialchars($statementData['summary']['net_movement_formatted'] ?? '0.00') . '</span></div>
            <div class="summary-row"><span class="label">Closing Balance:</span> <span class="total">' . 
            htmlspecialchars($statementData['summary']['closing_balance_formatted'] ?? '0.00') . '</span></div>
            <div class="summary-row"><span class="label">Total Transactions:</span> ' . 
            ($statementData['summary']['transaction_count'] ?? 0) . '</div>
        </div>
        ';
        
        // ========== TRANSACTIONS SECTION ==========
        $transactions = $statementData['transactions'] ?? [];
        if (!empty($transactions)) {
            $html .= '
            <table>
                <thead>
                    <tr>
                        <th class="date-col">Date</th>
                        <th class="desc-col">Description</th>
                        <th class="ref-col">Reference</th>
                        <th class="type-col">Type</th>
                        <th class="amount-col">Amount</th>
                        <th class="balance-col">Balance</th>
                    </tr>
                </thead>
                <tbody>
            ';
            
            foreach ($transactions as $transaction) {
                $amountClass = ($transaction['amount_type'] ?? '') === 'credit' ? 'credit' : 'debit';
                $amountSign = ($transaction['amount_type'] ?? '') === 'credit' ? '+' : '-';
                $amountDisplay = $amountSign . htmlspecialchars(number_format(abs($transaction['amount_numeric'] ?? 0), 2));
                
                $html .= '
                <tr>
                    <td>' . htmlspecialchars($transaction['transaction_date_formatted'] ?? '') . '</td>
                    <td>' . htmlspecialchars($transaction['description'] ?? '') . '</td>
                    <td>' . htmlspecialchars($transaction['reference_number'] ?? '') . '</td>
                    <td>' . htmlspecialchars($transaction['transaction_type_name'] ?? '') . '</td>
                    <td class="' . $amountClass . '">' . $amountDisplay . '</td>
                    <td>' . htmlspecialchars($transaction['balance_after_formatted'] ?? '0.00') . '</td>
                </tr>
                ';
            }
            
            $html .= '
                </tbody>
            </table>
            ';
        }
        
        // ========== FOOTER SECTION ==========
        $html .= '
        <div class="footer">
            Generated on ' . htmlspecialchars($statementData['metadata']['generated_at_formatted'] ?? date('F j, Y \a\t H:i')) . '<br/>
            This is an electronically generated statement. No signature required.
        </div>
        ';
        
        // Output HTML content
        $pdf->writeHTML($html, true, false, true, false, '');
        
        // Generate filename
        $filename = 'statement_' . 
                    $member['member_number'] . '_' . 
                    $statementData['account']['account_number'] . '_' . 
                    date('Ymd_His') . '.pdf';
        
        // Close and output PDF
        $pdf->Output($filename, 'D');
        exit();
        
    } catch (Exception $e) {
        throw new RuntimeException("PDF generation failed: " . $e->getMessage());
    }
}

/**
 * Generate CSV statement
 */
function generateCsvStatement($statementData, $member, $company) {
    $filename = 'statement_' . 
                $member['member_number'] . '_' . 
                $statementData['account']['account_number'] . '_' . 
                date('Ymd_His') . '.csv';
    
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $output = fopen('php://output', 'w');
    
    // Add BOM for UTF-8
    fwrite($output, "\xEF\xBB\xBF");
    
    // Header row
    fputcsv($output, [
        'Date', 'Description', 'Reference', 'Type', 'Amount', 'Balance', 
        'Payment Method', 'Status', 'Processed By'
    ]);
    
    // Transactions data
    foreach ($statementData['transactions'] as $transaction) {
        fputcsv($output, [
            $transaction['transaction_date_formatted'] ?? '',
            $transaction['description'] ?? '',
            $transaction['reference_number'] ?? '',
            $transaction['transaction_type_name'] ?? '',
            ($transaction['amount_type'] ?? '') === 'credit' ? 
                '+' . number_format($transaction['amount_numeric'] ?? 0, 2) : 
                '-' . number_format($transaction['amount_numeric'] ?? 0, 2),
            $transaction['balance_after_formatted'] ?? '0.00',
            $transaction['payment_method'] ?? '',
            $transaction['transaction_status'] ?? '',
            $transaction['processed_by'] ?? ''
        ]);
    }
    
    // Summary section
    fputcsv($output, ['']);
    fputcsv($output, ['SUMMARY']);
    fputcsv($output, ['Opening Balance:', $statementData['summary']['opening_balance_formatted'] ?? '0.00']);
    fputcsv($output, ['Total Credits:', $statementData['summary']['total_credits_formatted'] ?? '0.00']);
    fputcsv($output, ['Total Debits:', $statementData['summary']['total_debits_formatted'] ?? '0.00']);
    fputcsv($output, ['Net Movement:', $statementData['summary']['net_movement_formatted'] ?? '0.00']);
    fputcsv($output, ['Closing Balance:', $statementData['summary']['closing_balance_formatted'] ?? '0.00']);
    fputcsv($output, ['Total Transactions:', $statementData['summary']['transaction_count'] ?? 0]);
    
    fclose($output);
    exit();
}

// ============================================
// REQUEST HANDLERS
// ============================================

/**
 * Handle GET requests
 */
function handleGetRequest() {
    global $pdo, $memberId;
    
    // Parse query parameters
    $startDate = $_GET['start_date'] ?? date('Y-m-01');
    $endDate = $_GET['end_date'] ?? date('Y-m-t');
    $accountTypeId = isset($_GET['account_type_id']) ? (int)$_GET['account_type_id'] : null;
    $subAccountCode = $_GET['sub_account_code'] ?? null;
    $format = $_GET['format'] ?? 'json'; // json, pdf, excel, csv
    
    // Validate input
    list($startDate, $endDate) = StatementInputValidator::validateDates($startDate, $endDate, 365);
    $subAccountCode = StatementInputValidator::validateSubAccountCode($subAccountCode);
    
    try {
        // Get member details
        $member = getMemberDetails($pdo, $memberId);
        
        if (!$member) {
            http_response_code(404);
            echo json_encode([
                'success' => false,
                'error' => 'Member not found or inactive'
            ]);
            return;
        }
        
        // Get company details
        $company = getCompanyDetails($pdo);
        
        // Get account types for member
        $accountTypes = getMemberAccountTypes($pdo, $memberId);
        
        $response = [
            'success' => true,
            'data' => [
                'member' => $member,
                'company' => $company,
                'filters' => [
                    'start_date' => $startDate,
                    'end_date' => $endDate,
                    'account_type_id' => $accountTypeId,
                    'sub_account_code' => $subAccountCode,
                    'period' => [
                        'start_formatted' => date('F j, Y', strtotime($startDate)),
                        'end_formatted' => date('F j, Y', strtotime($endDate))
                    ]
                ],
                'account_types' => $accountTypes
            ]
        ];
        
        // If account type selected, validate access
        if ($accountTypeId) {
            try {
                StatementInputValidator::validateAccountAccess($pdo, $memberId, $accountTypeId, $subAccountCode);
            } catch (InvalidArgumentException $e) {
                http_response_code(403);
                echo json_encode([
                    'success' => false,
                    'error' => 'Access denied to requested account'
                ]);
                return;
            }
            
            // If sub-account selected, get statement
            if ($subAccountCode) {
                $accountDetails = getAccountDetails($pdo, $memberId, $accountTypeId, $subAccountCode);
                
                if (!$accountDetails) {
                    http_response_code(404);
                    echo json_encode([
                        'success' => false,
                        'error' => 'Account not found'
                    ]);
                    return;
                }
                
                $openingBalance = calculateOpeningBalance($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate);
                $transactions = getTransactions($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate, $endDate);
                
                // Calculate balances
                $calculated = calculateTransactionBalances($transactions, $openingBalance);
                $transactions = $calculated['transactions'];
                
                $statementData = [
                    'account' => $accountDetails,
                    'summary' => [
                        'opening_balance' => $openingBalance,
                        'opening_balance_formatted' => number_format($openingBalance, 2),
                        'closing_balance' => $calculated['closing_balance'],
                        'closing_balance_formatted' => number_format($calculated['closing_balance'], 2),
                        'total_credits' => $calculated['total_credits'],
                        'total_credits_formatted' => number_format($calculated['total_credits'], 2),
                        'total_debits' => $calculated['total_debits'],
                        'total_debits_formatted' => number_format($calculated['total_debits'], 2),
                        'net_movement' => $calculated['net_movement'],
                        'net_movement_formatted' => number_format($calculated['net_movement'], 2),
                        'net_movement_type' => $calculated['net_movement'] >= 0 ? 'credit' : 'debit',
                        'transaction_count' => count($transactions)
                    ],
                    'transactions' => $transactions,
                    'metadata' => [
                        'generated_at' => date('Y-m-d H:i:s'),
                        'generated_at_formatted' => date('F j, Y \a\t H:i'),
                        'statement_period' => date('F j, Y', strtotime($startDate)) . ' to ' . date('F j, Y', strtotime($endDate))
                    ]
                ];
                
                // Handle export formats - change headers for non-JSON responses
                if (in_array($format, ['pdf', 'excel', 'csv'])) {
                    try {
                        switch ($format) {
                            case 'pdf':
                                generatePdfStatement($statementData, $member, $company);
                                break;
                            case 'excel':
                                generateExcelStatement($statementData, $member, $company);
                                break;
                            case 'csv':
                                generateCsvStatement($statementData, $member, $company);
                                break;
                        }
                        return; // Export functions will exit
                        
                    } catch (Exception $e) {
                        http_response_code(500);
                        echo json_encode([
                            'success' => false,
                            'error' => 'Export failed',
                            'message' => ENVIRONMENT === 'development' ? $e->getMessage() : null,
                            'library_available' => EXPORT_LIBRARIES_AVAILABLE
                        ]);
                        return;
                    }
                }
                
                $response['data']['statement'] = $statementData;
            }
        }
        
        echo json_encode($response, JSON_NUMERIC_CHECK);
        
    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode([
            'success' => false,
            'error' => 'Failed to generate statement',
            'message' => ENVIRONMENT === 'development' ? $e->getMessage() : null
        ]);
        error_log("Statement generation error: " . $e->getMessage());
    }
}

/**
 * Handle POST requests
 */
function handlePostRequest() {
    global $pdo, $memberId;
    
    // CSRF protection
    if (!isset($_SERVER['HTTP_X_CSRF_TOKEN']) || 
        $_SERVER['HTTP_X_CSRF_TOKEN'] !== ($_SESSION['csrf_token'] ?? '')) {
        http_response_code(403);
        echo json_encode([
            'success' => false,
            'error' => 'CSRF token invalid'
        ]);
        return;
    }
    
    // Get JSON input
    $input = json_decode(file_get_contents('php://input'), true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        http_response_code(400);
        echo json_encode([
            'success' => false,
            'error' => 'Invalid JSON input',
            'json_error' => json_last_error_msg()
        ]);
        return;
    }
    
    // Validate required fields
    if (!isset($input['account_type_id']) || !isset($input['sub_account_code'])) {
        http_response_code(400);
        echo json_encode([
            'success' => false,
            'error' => 'Account type ID and sub-account code are required'
        ]);
        return;
    }
    
    $accountTypeId = (int)$input['account_type_id'];
    $subAccountCode = StatementInputValidator::validateSubAccountCode($input['sub_account_code']);
    $startDate = $input['start_date'] ?? date('Y-m-01');
    $endDate = $input['end_date'] ?? date('Y-m-t');
    $format = $input['format'] ?? 'json';
    $exportType = $input['export_type'] ?? 'detailed'; // detailed or summary
    
    // Validate dates
    list($startDate, $endDate) = StatementInputValidator::validateDates($startDate, $endDate, 365);
    
    try {
        // Verify account ownership
        try {
            StatementInputValidator::validateAccountAccess($pdo, $memberId, $accountTypeId, $subAccountCode);
        } catch (InvalidArgumentException $e) {
            http_response_code(403);
            echo json_encode([
                'success' => false,
                'error' => 'Access denied to requested account'
            ]);
            return;
        }
        
        $accountDetails = getAccountDetails($pdo, $memberId, $accountTypeId, $subAccountCode);
        
        if (!$accountDetails) {
            http_response_code(404);
            echo json_encode([
                'success' => false,
                'error' => 'Account not found'
            ]);
            return;
        }
        
        $openingBalance = calculateOpeningBalance($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate);
        $transactions = getTransactions($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate, $endDate);
        
        // Calculate summary
        $calculated = calculateTransactionBalances($transactions, $openingBalance);
        $transactions = $calculated['transactions'];
        
        // Get member and company details
        $member = getMemberDetails($pdo, $memberId);
        $company = getCompanyDetails($pdo);
        
        $statementData = [
            'account' => $accountDetails,
            'summary' => [
                'opening_balance' => $openingBalance,
                'opening_balance_formatted' => number_format($openingBalance, 2),
                'closing_balance' => $calculated['closing_balance'],
                'closing_balance_formatted' => number_format($calculated['closing_balance'], 2),
                'total_credits' => $calculated['total_credits'],
                'total_credits_formatted' => number_format($calculated['total_credits'], 2),
                'total_debits' => $calculated['total_debits'],
                'total_debits_formatted' => number_format($calculated['total_debits'], 2),
                'net_movement' => $calculated['net_movement'],
                'net_movement_formatted' => number_format($calculated['net_movement'], 2),
                'transaction_count' => count($transactions)
            ],
            'transactions' => $exportType === 'detailed' ? $transactions : [],
            'metadata' => [
                'generated_at' => date('Y-m-d H:i:s'),
                'generated_at_formatted' => date('F j, Y \a\t H:i'),
                'statement_period' => date('F j, Y', strtotime($startDate)) . ' to ' . date('F j, Y', strtotime($endDate)),
                'export_type' => $exportType
            ]
        ];
        
        // Handle export formats
        if (in_array($format, ['pdf', 'excel', 'csv'])) {
            try {
                switch ($format) {
                    case 'pdf':
                        generatePdfStatement($statementData, $member, $company);
                        break;
                    case 'excel':
                        generateExcelStatement($statementData, $member, $company);
                        break;
                    case 'csv':
                        generateCsvStatement($statementData, $member, $company);
                        break;
                }
                return; // Export functions will exit
                
            } catch (Exception $e) {
                http_response_code(500);
                echo json_encode([
                    'success' => false,
                    'error' => 'Export failed',
                    'message' => ENVIRONMENT === 'development' ? $e->getMessage() : null,
                    'library_available' => EXPORT_LIBRARIES_AVAILABLE
                ]);
                return;
            }
        }
        
        // Return JSON response
        echo json_encode([
            'success' => true,
            'message' => 'Statement generated successfully',
            'data' => $statementData,
            'download_url' => null // In a real app, this would be a download link
        ], JSON_NUMERIC_CHECK);
        
    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode([
            'success' => false,
            'error' => 'Failed to generate statement',
            'message' => ENVIRONMENT === 'development' ? $e->getMessage() : null
        ]);
        error_log("Statement generation error: " . $e->getMessage());
    }
}

// ============================================
// REQUEST ROUTING
// ============================================

$method = $_SERVER['REQUEST_METHOD'];

// Generate CSRF token if not exists
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

try {
    switch ($method) {
        case 'GET':
            handleGetRequest();
            break;
            
        case 'POST':
            handlePostRequest();
            break;
            
        default:
            http_response_code(405);
            echo json_encode([
                'success' => false,
                'error' => 'Method not allowed',
                'allowed_methods' => ['GET', 'POST']
            ]);
            break;
    }
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Internal server error',
        'message' => ENVIRONMENT === 'development' ? $e->getMessage() : 'Something went wrong'
    ]);
    error_log("Statement API Error: " . $e->getMessage());
}
?>