<?php
require_once __DIR__ . '/includes/cors.php';
configureCors();

/* ================= OTHER INCLUDES ================= */
require_once __DIR__ . '/includes/config_loader.php';
require_once __DIR__ . '/includes/config.php';

// Set content type first
header('Content-Type: application/json');

// member-statement-api.php
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', './php_errors.log');
ini_set('memory_limit', '512M');
ini_set('max_execution_time', 300);

/* ================= SESSION INITIALIZATION ================= */
$isProduction = (ENVIRONMENT === 'production');

$cookieParams = [
    'cookie_httponly' => true,
    'cookie_secure'   => $isProduction,
    'cookie_samesite' => $isProduction ? 'None' : 'Lax'
];

if (session_status() === PHP_SESSION_NONE) {
    session_start($cookieParams);
}

/* ================= CORS CONFIGURATION ================= */


function sendJsonResponse($success, $data = null, $error = null, $meta = null) {
    $response = ['success' => $success];
    if ($data !== null) $response['data'] = $data;
    if ($error !== null) $response['error'] = $error;
    if ($meta !== null) $response['meta'] = $meta;
    echo json_encode($response);
    exit;
}

/* ================= DEBUG (SAFE VERSION) ================= */
if (ENVIRONMENT === 'development') {
    error_log("Member Statement API accessed: " . date('Y-m-d H:i:s'));
    error_log("Session member present: " . (!empty($_SESSION['member_id']) ? 'Yes' : 'No'));
}

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

/* ================= MAIN LOGIC ================= */
$method = $_SERVER['REQUEST_METHOD'];
$loggedInMemberId = $_SESSION['member_id'];

try {
    if (!$pdo) {
        throw new Exception('Database connection failed');
    }

    // Handle different request types
    if (isset($_GET['get_account_types']) && $_GET['get_account_types'] === 'true') {
        handleGetAccountTypes($loggedInMemberId);
    } else {
        switch ($method) {
            case 'GET':
                handleGetRequest($loggedInMemberId);
                break;
            case 'POST':
                handleExportRequest($loggedInMemberId);
                break;
            default:
                http_response_code(405);
                sendJsonResponse(false, null, 'Method not allowed');
        }
    }
} catch (Exception $e) {
    error_log("MemberStatementAPI Error: " . $e->getMessage());
    http_response_code(500);
    sendJsonResponse(false, null, 'Internal Server Error: ' . $e->getMessage());
}

/* ================= HANDLER FUNCTIONS ================= */

// Handle account types fetch for logged-in member
function handleGetAccountTypes($memberId) {
    global $pdo;
    
    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 = ?
            ORDER BY at.account_name
        ");
        $stmt->execute([$memberId]);
        $accountTypes = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo json_encode($accountTypes);
        exit;
    } catch (PDOException $e) {
        error_log("Account Types Error: " . $e->getMessage());
        echo json_encode([]);
        exit;
    }
}

// Handle GET request for member statement
function handleGetRequest($memberId) {
    global $pdo;
    
    // Get date parameters
    $startDate = $_GET['start_date'] ?? '';
    $endDate = $_GET['end_date'] ?? '';
    $accountTypeId = $_GET['account_type_id'] ?? 'all';

    // Set default dates if not provided
    if (empty($startDate) || empty($endDate)) {
        $startDate = date('Y-m-d', strtotime('-1 month'));
        $endDate = date('Y-m-d');
    }

    // Validate dates
    if (!strtotime($startDate) || !strtotime($endDate)) {
        sendJsonResponse(false, null, 'Invalid date format');
    }

    if ($startDate > $endDate) {
        sendJsonResponse(false, null, 'Start date cannot be after end date');
    }

    // Fetch member details (verify it's the logged-in member)
    $memberStmt = $pdo->prepare("
        SELECT m.id, m.first_name, m.middle_name, m.last_name, m.personal_number, 
               m.member_number, m.id_number, m.address, m.email_address, m.phone_number
        FROM members m
        WHERE m.id = ?
    ");
    $memberStmt->execute([$memberId]);
    $memberDetails = $memberStmt->fetch(PDO::FETCH_ASSOC);

    if (!$memberDetails) {
        sendJsonResponse(false, null, 'Member not found');
    }

    // Fetch available accounts for this member
    $accountsStmt = $pdo->prepare("
        SELECT DISTINCT at.id as account_type_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 = ?
        " . ($accountTypeId === 'all' ? "" : "AND at.id = ?") . "
        GROUP BY at.id
    ");
    
    if ($accountTypeId === 'all') {
        $accountsStmt->execute([$memberId]);
    } else {
        $accountsStmt->execute([$memberId, $accountTypeId]);
    }
    $accountList = $accountsStmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($accountList)) {
        sendJsonResponse(false, null, 'No accounts found for your membership');
    }

    // Function to get opening balance
    function getOpeningBalance($pdo, $accountTypeId, $subAccountCode, $startDate, $memberId) {
        // Get base opening balance from member_accounts
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(opening_balance), 0) as total_opening_balance
            FROM member_accounts 
            WHERE account_type_id = ?
            AND sub_account_code = ?
            AND member_id = ?
        ");
        $stmt->execute([$accountTypeId, $subAccountCode, $memberId]);
        $baseBalance = (float)$stmt->fetchColumn();
        
        // Calculate net effect from prior transactions
        $stmt = $pdo->prepare("
            SELECT 
                COALESCE(SUM(CASE WHEN transaction_type = 'cr' AND amount != 0 THEN amount ELSE 0 END), 0) AS total_credits,
                COALESCE(SUM(CASE WHEN transaction_type = 'dr' AND amount != 0 THEN amount ELSE 0 END), 0) AS total_debits
            FROM transactions 
            WHERE account_type_id = ? 
            AND sub_account_code = ?
            AND transaction_date < ?
            AND member_id = ?
            AND amount != 0
        ");
        $stmt->execute([$accountTypeId, $subAccountCode, $startDate, $memberId]);
        $priorTransactions = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Determine if this is a loan account
        $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        $isLoanAccount = ($accountType && strtolower($accountType['type_code']) === 'loan');
        
        $netEffect = ($priorTransactions['total_credits'] ?? 0) - ($priorTransactions['total_debits'] ?? 0);
        
        return $isLoanAccount 
            ? $baseBalance + $netEffect
            : $baseBalance + $netEffect;
    }

    // Function to process sub-account transactions
    function processSubAccountTransactions($pdo, $memberId, $accountTypeId, $subAccountCode, $startDate, $endDate) {
        $openingBalance = getOpeningBalance($pdo, $accountTypeId, $subAccountCode, $startDate, $memberId);
        
        $stmt = $pdo->prepare("
            SELECT 
                t.id,
                t.transaction_number,
                t.transaction_date,
                t.description,
                t.transaction_type,
                t.amount,
                at.type_code, 
                u.username as created_by_name,
                t.created_at
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            LEFT JOIN users u ON t.created_by = u.id
            WHERE t.member_id = ? 
            AND t.account_type_id = ?
            AND t.sub_account_code = ?
            AND t.transaction_date BETWEEN ? AND ?
            AND t.amount != 0
            ORDER BY t.transaction_date, t.created_at
        ");
        $stmt->execute([$memberId, $accountTypeId, $subAccountCode, $startDate, $endDate]);
        $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $currentBalance = $openingBalance;
        $periodCredits = 0;
        $periodDebits = 0;
        
        // Determine account type
        $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        $isLoanAccount = ($accountType && strtolower($accountType['type_code']) === 'loan');
        
        foreach ($transactions as &$transaction) {
            $transaction['opening_balance'] = $currentBalance;
            
            if ($transaction['transaction_type'] === 'cr') {
                $periodCredits += $transaction['amount'];
            } else {
                $periodDebits += $transaction['amount'];
            }
            
            if ($isLoanAccount) {
                $currentBalance += ($transaction['transaction_type'] === 'dr') 
                    ? $transaction['amount'] 
                    : -$transaction['amount'];
            } else {
                $currentBalance += ($transaction['transaction_type'] === 'cr') 
                    ? $transaction['amount'] 
                    : -$transaction['amount'];
            }
            
            $transaction['running_balance'] = $currentBalance;
        }
        
        return [
            'transactions' => $transactions,
            'opening_balance' => $openingBalance,
            'closing_balance' => $currentBalance,
            'period_credits' => $periodCredits,
            'period_debits' => $periodDebits,
            'period_net' => $periodCredits - $periodDebits,
            'is_loan_account' => $isLoanAccount
        ];
    }

    // Function to process account transactions
    function processAccountTransactions($pdo, $memberId, $accountTypeId, $accountName, $startDate, $endDate) {
        $subAccounts = [
            '1' => ['label' => 'Principal', 'transactions' => [], 'opening_balance' => 0, 'closing_balance' => 0, 'period_credits' => 0, 'period_debits' => 0, 'period_net' => 0],
            '2' => ['label' => 'Interest', 'transactions' => [], 'opening_balance' => 0, 'closing_balance' => 0, 'period_credits' => 0, 'period_debits' => 0, 'period_net' => 0]
        ];
        
        // Get account type code
        $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        $typeCode = $accountType['type_code'] ?? '';
        
        $processedTransactionIds = [];
        
        // Process each sub-account
        foreach ($subAccounts as $code => &$subAccount) {
            $result = processSubAccountTransactions(
                $pdo, 
                $memberId, 
                $accountTypeId,
                $code,
                $startDate, 
                $endDate
            );
            
            // Merge transactions and balances
            $subAccount['transactions'] = $result['transactions'];
            $subAccount['opening_balance'] = $result['opening_balance'];
            $subAccount['closing_balance'] = $result['closing_balance'];
            $subAccount['period_credits'] = $result['period_credits'];
            $subAccount['period_debits'] = $result['period_debits'];
            $subAccount['period_net'] = $result['period_net'];
            $subAccount['is_loan_account'] = $result['is_loan_account'];
            
            // Track processed transaction IDs to avoid duplicates
            foreach ($result['transactions'] as $transaction) {
                $processedTransactionIds[] = $transaction['id'];
            }
        }
        
        // Sort transactions by date for each sub-account
        foreach ($subAccounts as $code => &$subAccount) {
            usort($subAccount['transactions'], function($a, $b) {
                $dateCompare = strtotime($a['transaction_date']) - strtotime($b['transaction_date']);
                if ($dateCompare === 0) {
                    return strtotime($a['created_at']) - strtotime($b['created_at']);
                }
                return $dateCompare;
            });
        }
        
        return [
            'account_name' => $accountName,
            'account_type_id' => $accountTypeId,
            'type_code' => $typeCode,
            'sub_accounts' => $subAccounts
        ];
    }

    // Process all accounts
    $accountsData = [];
    foreach ($accountList as $account) {
        $accountData = processAccountTransactions(
            $pdo, 
            $memberId, 
            $account['account_type_id'],
            $account['account_name'],
            $startDate, 
            $endDate
        );
        
        if ($accountData) {
            $accountsData[] = $accountData;
        }
    }

    // Fetch company details
    $companyStmt = $pdo->prepare("
        SELECT company_name, address, phone_number, email, website, country, city, company_logo, CS_Number
        FROM company_settings LIMIT 1
    ");
    $companyStmt->execute();
    $companyDetails = $companyStmt->fetch(PDO::FETCH_ASSOC);

    sendJsonResponse(true, [
        'company' => $companyDetails,
        'member' => $memberDetails,
        'accounts' => $accountsData,
        'summary' => [
            'total_accounts' => count($accountsData),
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate
            ]
        ]
    ], null, [
        'filters' => [
            'start_date' => $startDate,
            'end_date' => $endDate,
            'account_type_id' => $accountTypeId
        ]
    ]);
}

function handleExportRequest($memberId) {
    global $pdo;
    
    $input = json_decode(file_get_contents('php://input'), true);
    $startDate = $input['start_date'] ?? '';
    $endDate = $input['end_date'] ?? '';
    $accountTypeId = $input['account_type_id'] ?? 'all';
    $exportType = $input['export_type'] ?? 'pdf';

    if (empty($startDate) || empty($endDate)) {
        sendJsonResponse(false, null, 'Start date and end date are required for export');
    }

    // Verify the member exists and it's the logged-in member
    $memberStmt = $pdo->prepare("
        SELECT m.id, m.first_name, m.middle_name, m.last_name, m.personal_number, 
               m.member_number, m.id_number, m.address, m.email_address, m.phone_number
        FROM members m
        WHERE m.id = ?
    ");
    $memberStmt->execute([$memberId]);
    $memberDetails = $memberStmt->fetch(PDO::FETCH_ASSOC);

    if (!$memberDetails) {
        sendJsonResponse(false, null, 'Member not found');
    }

    // Fetch accounts for this member
    $accountsStmt = $pdo->prepare("
        SELECT DISTINCT at.id as account_type_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 = ?
        " . ($accountTypeId === 'all' ? "" : "AND at.id = ?") . "
        GROUP BY at.id
    ");
    
    if ($accountTypeId === 'all') {
        $accountsStmt->execute([$memberId]);
    } else {
        $accountsStmt->execute([$memberId, $accountTypeId]);
    }
    $accountList = $accountsStmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($accountList)) {
        sendJsonResponse(false, null, 'No accounts found for your membership');
    }

    // Function to get opening balance for export
    function getOpeningBalanceExport($pdo, $accountTypeId, $subAccountCode, $startDate, $memberId) {
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(opening_balance), 0) as total_opening_balance
            FROM member_accounts 
            WHERE account_type_id = ?
            AND sub_account_code = ?
            AND member_id = ?
        ");
        $stmt->execute([$accountTypeId, $subAccountCode, $memberId]);
        $baseBalance = (float)$stmt->fetchColumn();
        
        $stmt = $pdo->prepare("
            SELECT 
                COALESCE(SUM(CASE WHEN transaction_type = 'cr' AND amount != 0 THEN amount ELSE 0 END), 0) AS total_credits,
                COALESCE(SUM(CASE WHEN transaction_type = 'dr' AND amount != 0 THEN amount ELSE 0 END), 0) AS total_debits
            FROM transactions 
            WHERE account_type_id = ? 
            AND sub_account_code = ?
            AND transaction_date < ?
            AND member_id = ?
            AND amount != 0
        ");
        $stmt->execute([$accountTypeId, $subAccountCode, $startDate, $memberId]);
        $priorTransactions = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        $isLoanAccount = ($accountType && strtolower($accountType['type_code']) === 'loan');
        
        $netEffect = ($priorTransactions['total_credits'] ?? 0) - ($priorTransactions['total_debits'] ?? 0);
        
        return $isLoanAccount 
            ? $baseBalance + $netEffect
            : $baseBalance + $netEffect;
    }

    // Function to process account transactions for export
    function processAccountTransactionsExport($pdo, $memberId, $accountTypeId, $accountName, $startDate, $endDate) {
        $subAccounts = [
            '1' => ['label' => 'Principal', 'transactions' => [], 'opening_balance' => 0, 'closing_balance' => 0, 'period_credits' => 0, 'period_debits' => 0],
            '2' => ['label' => 'Interest', 'transactions' => [], 'opening_balance' => 0, 'closing_balance' => 0, 'period_credits' => 0, 'period_debits' => 0]
        ];
        
        foreach ($subAccounts as $code => &$subAccount) {
            $openingBalance = getOpeningBalanceExport($pdo, $accountTypeId, $code, $startDate, $memberId);
            
            $stmt = $pdo->prepare("
                SELECT 
                    t.id,
                    t.transaction_number,
                    t.transaction_date,
                    t.description,
                    t.transaction_type,
                    t.amount,
                    at.type_code,
                    t.created_at
                FROM transactions t
                JOIN account_types at ON t.account_type_id = at.id
                WHERE t.member_id = ? 
                AND t.account_type_id = ?
                AND t.sub_account_code = ?
                AND t.transaction_date BETWEEN ? AND ?
                AND t.amount != 0
                ORDER BY t.transaction_date, t.created_at
            ");
            $stmt->execute([$memberId, $accountTypeId, $code, $startDate, $endDate]);
            $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $currentBalance = $openingBalance;
            $periodCredits = 0;
            $periodDebits = 0;
            
            // Determine account type
            $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
            $stmt->execute([$accountTypeId]);
            $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
            $isLoanAccount = ($accountType && strtolower($accountType['type_code']) === 'loan');
            
            foreach ($transactions as $transaction) {
                $subAccount['transactions'][] = $transaction;
                
                if ($transaction['transaction_type'] === 'cr') {
                    $periodCredits += $transaction['amount'];
                } else {
                    $periodDebits += $transaction['amount'];
                }
                
                if ($isLoanAccount) {
                    $currentBalance += ($transaction['transaction_type'] === 'dr') 
                        ? $transaction['amount'] 
                        : -$transaction['amount'];
                } else {
                    $currentBalance += ($transaction['transaction_type'] === 'cr') 
                        ? $transaction['amount'] 
                        : -$transaction['amount'];
                }
            }
            
            $subAccount['opening_balance'] = $openingBalance;
            $subAccount['closing_balance'] = $currentBalance;
            $subAccount['period_credits'] = $periodCredits;
            $subAccount['period_debits'] = $periodDebits;
            $subAccount['is_loan_account'] = $isLoanAccount;
        }
        
        $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return [
            'account_name' => $accountName,
            'type_code' => $accountType['type_code'] ?? '',
            'sub_accounts' => $subAccounts
        ];
    }

    $accountsData = [];
    foreach ($accountList as $account) {
        $accountData = processAccountTransactionsExport(
            $pdo, 
            $memberId, 
            $account['account_type_id'],
            $account['account_name'],
            $startDate, 
            $endDate
        );
        
        if ($accountData) {
            $accountsData[] = $accountData;
        }
    }

    // Fetch company details
    $companyStmt = $pdo->prepare("SELECT * FROM company_settings LIMIT 1");
    $companyStmt->execute();
    $companyDetails = $companyStmt->fetch(PDO::FETCH_ASSOC);

    if ($exportType === 'pdf') {
        exportToPDF($companyDetails, $memberDetails, $accountsData, $startDate, $endDate);
    } else {
        exportToExcel($companyDetails, $memberDetails, $accountsData, $startDate, $endDate);
    }
}

function exportToPDF($companyDetails, $memberDetails, $accountsData, $startDate, $endDate) {
    // Check dependencies
    $tcpdfPath = __DIR__ . '/tcpdf/tcpdf.php';
    if (!file_exists($tcpdfPath)) {
        sendJsonResponse(false, null, 'TCPDF library not found. Please install TCPDF.');
    }
    require_once $tcpdfPath;
    
    if (!class_exists('TCPDF')) {
        sendJsonResponse(false, null, 'TCPDF class not loaded properly.');
    }

    $pdf = new TCPDF('P', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
    
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor($companyDetails['company_name']);
    $pdf->SetTitle('Member Statement - ' . $memberDetails['first_name'] . ' ' . $memberDetails['last_name']);
    $pdf->SetSubject('Member Account Statement');
    
    $pdf->SetMargins(15, 25, 15);
    $pdf->SetHeaderMargin(10);
    $pdf->SetFooterMargin(10);
    $pdf->SetAutoPageBreak(TRUE, 25);
    
    $pdf->AddPage();
    
    // Company header
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->Cell(0, 10, $companyDetails['company_name'], 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 10);
    $pdf->Cell(0, 6, 'P.O. BOX ' . ($companyDetails['address'] ?? '236 - 01000'), 0, 1, 'C');
    $pdf->Cell(0, 6, 'TEL: ' . ($companyDetails['phone_number'] ?? ''), 0, 1, 'C');
    
    $pdf->Ln(10);
    
    // Title
    $pdf->SetFont('helvetica', 'B', 14);
    $pdf->Cell(0, 10, 'MEMBER ACCOUNT STATEMENT', 0, 1, 'C');
    $pdf->Ln(5);
    
    // Member info
    $pdf->SetFont('helvetica', '', 10);
    $pdf->Cell(0, 6, 'Member: ' . $memberDetails['first_name'] . ' ' . $memberDetails['last_name'], 0, 1);
    $pdf->Cell(0, 6, 'Member No: ' . $memberDetails['personal_number'], 0, 1);
    $pdf->Cell(0, 6, 'Phone: ' . $memberDetails['phone_number'], 0, 1);
    $pdf->Cell(0, 6, 'Period: ' . date('F d, Y', strtotime($startDate)) . ' to ' . date('F d, Y', strtotime($endDate)), 0, 1);
    $pdf->Cell(0, 6, 'Date: ' . date('F j, Y'), 0, 1);
    
    $pdf->Ln(10);
    
    // Function to format balance
    function formatBalancePDF($amount, $isLoanAccount = false) {
        if ($amount == 0) return number_format($amount, 2);
        
        $formatted = number_format(abs($amount), 2);
        if (($isLoanAccount && $amount > 0) || (!$isLoanAccount && $amount < 0)) {
            return $formatted . ' Dr';
        } else {
            return $formatted . ' Cr';
        }
    }
    
    // Process accounts data
    foreach ($accountsData as $accountData) {
        $pdf->SetFont('helvetica', 'B', 12);
        $pdf->Cell(0, 8, strtoupper($accountData['account_name']) . ' (' . $accountData['type_code'] . ')', 0, 1);
        $pdf->Ln(2);
        
        foreach ($accountData['sub_accounts'] as $subAccount) {
            if (count($subAccount['transactions']) > 0 || $subAccount['opening_balance'] != 0 || $subAccount['closing_balance'] != 0) {
                $pdf->SetFont('helvetica', 'B', 10);
                $pdf->Cell(0, 6, $subAccount['label'], 0, 1);
                
                // Table header
                $pdf->SetFont('helvetica', 'B', 8);
                $pdf->Cell(25, 6, 'Date', 1, 0, 'L');
                $pdf->Cell(30, 6, 'Ref No', 1, 0, 'L');
                $pdf->Cell(50, 6, 'Description', 1, 0, 'L');
                $pdf->Cell(25, 6, 'Debit', 1, 0, 'R');
                $pdf->Cell(25, 6, 'Credit', 1, 0, 'R');
                $pdf->Cell(35, 6, 'Balance', 1, 1, 'R');
                
                $pdf->SetFont('helvetica', '', 8);
                
                // Opening balance
                $pdf->Cell(25, 6, '', 1, 0, 'L');
                $pdf->Cell(30, 6, '', 1, 0, 'L');
                $pdf->Cell(50, 6, 'Balance b/f', 1, 0, 'L');
                $pdf->Cell(25, 6, '', 1, 0, 'R');
                $pdf->Cell(25, 6, '', 1, 0, 'R');
                $pdf->Cell(35, 6, formatBalancePDF($subAccount['opening_balance'], $subAccount['is_loan_account']), 1, 1, 'R');
                
                // Transactions
                $currentBalance = $subAccount['opening_balance'];
                foreach ($subAccount['transactions'] as $transaction) {
                    $debit = $transaction['transaction_type'] === 'dr' ? number_format($transaction['amount'], 2) : '';
                    $credit = $transaction['transaction_type'] === 'cr' ? number_format($transaction['amount'], 2) : '';
                    
                    // Calculate running balance for this transaction
                    if ($subAccount['is_loan_account']) {
                        $currentBalance += ($transaction['transaction_type'] === 'dr') 
                            ? $transaction['amount'] 
                            : -$transaction['amount'];
                    } else {
                        $currentBalance += ($transaction['transaction_type'] === 'cr') 
                            ? $transaction['amount'] 
                            : -$transaction['amount'];
                    }
                    
                    $pdf->Cell(25, 6, date('d/m/Y', strtotime($transaction['transaction_date'])), 1, 0, 'L');
                    $pdf->Cell(30, 6, $transaction['transaction_number'], 1, 0, 'L');
                    $pdf->Cell(50, 6, substr($transaction['description'], 0, 30), 1, 0, 'L');
                    $pdf->Cell(25, 6, $debit, 1, 0, 'R');
                    $pdf->Cell(25, 6, $credit, 1, 0, 'R');
                    $pdf->Cell(35, 6, formatBalancePDF($currentBalance, $subAccount['is_loan_account']), 1, 1, 'R');
                }
                
                // Period totals if there are transactions
                if (count($subAccount['transactions']) > 0) {
                    $pdf->SetFont('helvetica', 'B', 8);
                    $pdf->Cell(25, 6, '', 1, 0, 'L');
                    $pdf->Cell(30, 6, '', 1, 0, 'L');
                    $pdf->Cell(50, 6, 'Period Totals', 1, 0, 'L');
                    $pdf->Cell(25, 6, number_format($subAccount['period_debits'], 2), 1, 0, 'R');
                    $pdf->Cell(25, 6, number_format($subAccount['period_credits'], 2), 1, 0, 'R');
                    $pdf->Cell(35, 6, '', 1, 1, 'R');
                }
                
                // Closing balance
                $pdf->SetFont('helvetica', 'B', 8);
                $pdf->Cell(25, 6, '', 1, 0, 'L');
                $pdf->Cell(30, 6, '', 1, 0, 'L');
                $pdf->Cell(50, 6, 'Balance c/f', 1, 0, 'L');
                $pdf->Cell(25, 6, '', 1, 0, 'R');
                $pdf->Cell(25, 6, '', 1, 0, 'R');
                $pdf->Cell(35, 6, formatBalancePDF($subAccount['closing_balance'], $subAccount['is_loan_account']), 1, 1, 'R');
                
                $pdf->Ln(5);
            }
        }
        $pdf->Ln(5);
    }
    
    // Footer
    $pdf->SetY(-30);
    $pdf->SetFont('helvetica', 'I', 8);
    $pdf->Cell(0, 6, 'Please address any queries to the General Manager within 30 days', 0, 1, 'C');
    $pdf->Cell(0, 6, 'Generated on ' . date('d/m/Y H:i:s'), 0, 1, 'C');
    $pdf->Cell(0, 6, $companyDetails['company_name'] . ' - Member Statement', 0, 1, 'C');
    
    $pdf->Output("statement_{$memberDetails['personal_number']}.pdf", 'D');
    exit;
}

function exportToExcel($companyDetails, $memberDetails, $accountsData, $startDate, $endDate) {
    // Check dependencies
    $vendorPath = __DIR__ . '/vendor/autoload.php';
    if (!file_exists($vendorPath)) {
        sendJsonResponse(false, null, 'PhpSpreadsheet library not found. Please install via Composer.');
    }
    require_once $vendorPath;
    
    if (!class_exists('PhpOffice\PhpSpreadsheet\Spreadsheet')) {
        sendJsonResponse(false, null, 'PhpSpreadsheet classes not loaded properly.');
    }

    $spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    $spreadsheet->getProperties()
        ->setCreator($companyDetails['company_name'])
        ->setTitle('Member Statement - ' . $memberDetails['first_name'] . ' ' . $memberDetails['last_name']);
    
    // Company header
    $sheet->setCellValue('A1', $companyDetails['company_name']);
    $sheet->mergeCells('A1:F1');
    $sheet->getStyle('A1')->getFont()->setSize(14)->setBold(true);
    
    $sheet->setCellValue('A2', 'P.O. BOX ' . ($companyDetails['address'] ?? '236 - 01000'));
    $sheet->mergeCells('A2:F2');
    
    $sheet->setCellValue('A3', 'TEL: ' . ($companyDetails['phone_number'] ?? ''));
    $sheet->mergeCells('A3:F3');
    
    $sheet->setCellValue('A4', 'MEMBER ACCOUNT STATEMENT');
    $sheet->mergeCells('A4:F4');
    $sheet->getStyle('A4')->getFont()->setSize(12)->setBold(true);
    
    // Member info
    $sheet->setCellValue('A6', 'Member: ' . $memberDetails['first_name'] . ' ' . $memberDetails['last_name']);
    $sheet->setCellValue('A7', 'Member No: ' . $memberDetails['personal_number']);
    $sheet->setCellValue('A8', 'Phone: ' . $memberDetails['phone_number']);
    $sheet->setCellValue('A9', 'Period: ' . date('F d, Y', strtotime($startDate)) . ' to ' . date('F d, Y', strtotime($endDate)));
    $sheet->setCellValue('A10', 'Date: ' . date('F j, Y'));
    
    $currentRow = 12;
    
    // Function to format balance for Excel
    function formatBalanceExcel($amount, $isLoanAccount = false) {
        if ($amount == 0) return number_format($amount, 2);
        
        $formatted = number_format(abs($amount), 2);
        if (($isLoanAccount && $amount > 0) || (!$isLoanAccount && $amount < 0)) {
            return $formatted . ' Dr';
        } else {
            return $formatted . ' Cr';
        }
    }
    
    // Process accounts data
    foreach ($accountsData as $accountData) {
        $sheet->setCellValue("A$currentRow", strtoupper($accountData['account_name']) . ' (' . $accountData['type_code'] . ')');
        $sheet->mergeCells("A$currentRow:F$currentRow");
        $sheet->getStyle("A$currentRow")->getFont()->setBold(true);
        $currentRow++;
        
        foreach ($accountData['sub_accounts'] as $subAccount) {
            if (count($subAccount['transactions']) > 0 || $subAccount['opening_balance'] != 0 || $subAccount['closing_balance'] != 0) {
                $sheet->setCellValue("A$currentRow", $subAccount['label']);
                $sheet->mergeCells("A$currentRow:F$currentRow");
                $sheet->getStyle("A$currentRow")->getFont()->setBold(true);
                $currentRow++;
                
                // Table header
                $sheet->setCellValue("A$currentRow", 'Date');
                $sheet->setCellValue("B$currentRow", 'Ref No');
                $sheet->setCellValue("C$currentRow", 'Description');
                $sheet->setCellValue("D$currentRow", 'Debit');
                $sheet->setCellValue("E$currentRow", 'Credit');
                $sheet->setCellValue("F$currentRow", 'Balance');
                $sheet->getStyle("A$currentRow:F$currentRow")->getFont()->setBold(true);
                $currentRow++;
                
                // Opening balance
                $sheet->setCellValue("C$currentRow", 'Balance b/f');
                $sheet->setCellValue("F$currentRow", formatBalanceExcel($subAccount['opening_balance'], $subAccount['is_loan_account']));
                $currentRow++;
                
                // Transactions
                $currentBalance = $subAccount['opening_balance'];
                foreach ($subAccount['transactions'] as $transaction) {
                    $debit = $transaction['transaction_type'] === 'dr' ? number_format($transaction['amount'], 2) : '';
                    $credit = $transaction['transaction_type'] === 'cr' ? number_format($transaction['amount'], 2) : '';
                    
                    // Calculate running balance
                    if ($subAccount['is_loan_account']) {
                        $currentBalance += ($transaction['transaction_type'] === 'dr') 
                            ? $transaction['amount'] 
                            : -$transaction['amount'];
                    } else {
                        $currentBalance += ($transaction['transaction_type'] === 'cr') 
                            ? $transaction['amount'] 
                            : -$transaction['amount'];
                    }
                    
                    $sheet->setCellValue("A$currentRow", date('d/m/Y', strtotime($transaction['transaction_date'])));
                    $sheet->setCellValue("B$currentRow", $transaction['transaction_number']);
                    $sheet->setCellValue("C$currentRow", $transaction['description']);
                    $sheet->setCellValue("D$currentRow", $debit);
                    $sheet->setCellValue("E$currentRow", $credit);
                    $sheet->setCellValue("F$currentRow", formatBalanceExcel($currentBalance, $subAccount['is_loan_account']));
                    $currentRow++;
                }
                
                // Period totals if there are transactions
                if (count($subAccount['transactions']) > 0) {
                    $sheet->setCellValue("C$currentRow", 'Period Totals');
                    $sheet->setCellValue("D$currentRow", number_format($subAccount['period_debits'], 2));
                    $sheet->setCellValue("E$currentRow", number_format($subAccount['period_credits'], 2));
                    $sheet->getStyle("C$currentRow:E$currentRow")->getFont()->setBold(true);
                    $currentRow++;
                }
                
                // Closing balance
                $sheet->setCellValue("C$currentRow", 'Balance c/f');
                $sheet->setCellValue("F$currentRow", formatBalanceExcel($subAccount['closing_balance'], $subAccount['is_loan_account']));
                $sheet->getStyle("C$currentRow:F$currentRow")->getFont()->setBold(true);
                $currentRow += 2;
            }
        }
        $currentRow++;
    }
    
    // Auto-size columns
    foreach(range('A','F') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    
    // Create Excel file
    $writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="statement_' . $memberDetails['personal_number'] . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer->save('php://output');
    exit;
}
?>