<?php
session_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 4) {
    exit('Unauthorized');
}

include('../../includes/config.php');

$acct_id = intval($_GET['acct_id'] ?? 0);
$from = $_GET['from_date'] ?? date('Y-m-01');
$to   = $_GET['to_date'] ?? date('Y-m-d');
$store_id = intval($_GET['store_id'] ?? 0);
$export = $_GET['export'] ?? 'csv'; // csv or pdf

// Fetch account name
$stmtAcc = $conn->prepare("SELECT account_name FROM accounts WHERE id = ?");
$stmtAcc->bind_param("i", $acct_id);
$stmtAcc->execute();
$accountResult = $stmtAcc->get_result();
$account = $accountResult->fetch_assoc();
$stmtAcc->close();

if (!$account) {
    exit("Invalid Account ID.");
}

$where = ["ft.account_id = ?", "ft.transaction_date BETWEEN ? AND ?"];
$params = [$acct_id, $from, $to];
$types = "iss";

if ($store_id > 0) {
    $where[] = "ft.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}
$whereSql = implode(" AND ", $where);

// Fetch transactions
$sql = "
    SELECT ft.transaction_date, ft.type, ft.debit, ft.credit, ft.description, s.store_name
    FROM financial_transactions ft
    LEFT JOIN stores s ON ft.store_id = s.id
    WHERE $whereSql
    ORDER BY ft.transaction_date, ft.id
";

$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

$rows = [];
while ($row = $result->fetch_assoc()) {
    $rows[] = $row;
}

if ($export === 'csv') {
    // Export CSV
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="trial_balance_detail_' . date('Ymd_His') . '.csv"');

    $output = fopen('php://output', 'w');
    // Header row
    fputcsv($output, ['Account', $account['account_name']]);
    fputcsv($output, ['From Date', $from]);
    fputcsv($output, ['To Date', $to]);
    if ($store_id > 0) {
        // Fetch store name
        $storeRes = $conn->query("SELECT store_name FROM stores WHERE id = $store_id LIMIT 1");
        $storeName = $storeRes->fetch_assoc()['store_name'] ?? 'N/A';
        fputcsv($output, ['Store', $storeName]);
    }
    fputcsv($output, []);
    fputcsv($output, ['Date', 'Type', 'Debit', 'Credit', 'Description', 'Store']);

    foreach ($rows as $r) {
        fputcsv($output, [
            $r['transaction_date'],
            ucwords(str_replace('_', ' ', $r['type'])),
            number_format($r['debit'], 2),
            number_format($r['credit'], 2),
            $r['description'],
            $r['store_name']
        ]);
    }
    fclose($output);
    exit();
} elseif ($export === 'pdf') {
    // Export PDF using TCPDF
    require_once('../../includes/tcpdf/tcpdf.php');

    $pdf = new TCPDF('P', 'mm', 'A4');
    $pdf->SetCreator('POS System');
    $pdf->SetAuthor('POS System');
    $pdf->SetTitle('Trial Balance Detail');
    $pdf->SetHeaderData('', 0, 'Trial Balance Detail', $account['account_name']);
    $pdf->setHeaderFont(['helvetica', '', 12]);
    $pdf->setFooterFont(['helvetica', '', 8]);
    $pdf->SetMargins(15, 27, 15);
    $pdf->SetHeaderMargin(5);
    $pdf->SetFooterMargin(10);
    $pdf->SetAutoPageBreak(TRUE, 25);
    $pdf->AddPage();

    $html = '<h2>Trial Balance Detail</h2>';
    $html .= '<p><strong>Account:</strong> ' . htmlspecialchars($account['account_name']) . '</p>';
    $html .= '<p><strong>From:</strong> ' . htmlspecialchars($from) . ' <strong>To:</strong> ' . htmlspecialchars($to) . '</p>';
    if ($store_id > 0) {
        $storeRes = $conn->query("SELECT store_name FROM stores WHERE id = $store_id LIMIT 1");
        $storeName = $storeRes->fetch_assoc()['store_name'] ?? 'N/A';
        $html .= '<p><strong>Store:</strong> ' . htmlspecialchars($storeName) . '</p>';
    }

    $html .= '<table border="1" cellpadding="4" cellspacing="0" style="font-size:10pt; width:100%;">';
    $html .= '<thead style="background-color:#FFA500; color:#fff;"><tr>
                <th>Date</th>
                <th>Type</th>
                <th>Debit</th>
                <th>Credit</th>
                <th>Description</th>
                <th>Store</th>
              </tr></thead><tbody>';

    foreach ($rows as $r) {
        $html .= '<tr>
                    <td>' . htmlspecialchars($r['transaction_date']) . '</td>
                    <td>' . ucwords(str_replace('_', ' ', $r['type'])) . '</td>
                    <td align="right">' . number_format($r['debit'], 2) . '</td>
                    <td align="right">' . number_format($r['credit'], 2) . '</td>
                    <td>' . htmlspecialchars($r['description']) . '</td>
                    <td>' . htmlspecialchars($r['store_name']) . '</td>
                  </tr>';
    }

    $html .= '</tbody></table>';

    $pdf->writeHTML($html);
    $pdf->Output('trial_balance_detail_' . date('Ymd_His') . '.pdf', 'D');
    exit();
} else {
    exit('Invalid export format.');
}
