<?php
session_start();
ob_start();

if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 4) {
    header("Location: ../../index.php");
    exit();
}

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

$filter_store = $_GET['store_id'] ?? '';
$filter_account = $_GET['account_id'] ?? '';
$filter_type = $_GET['type'] ?? '';
$search = trim($_GET['search'] ?? '');
$from_date = $_GET['from_date'] ?? date('Y-m-01');
$to_date = $_GET['to_date'] ?? date('Y-m-d');

// For filtering, extend dates to full day for accurate filtering
$from_date_start = $from_date . " 00:00:00";
$to_date_end = $to_date . " 23:59:59";

$params = [];
$paramTypes = "";

// Journal Filters
$journalWhereClauses = ["je.status = 'posted'"];
$journalParams = [];
$journalParamTypes = "";

$journalWhereClauses[] = "je.entry_date BETWEEN ? AND ?";
$journalParams[] = $from_date_start;
$journalParams[] = $to_date_end;
$journalParamTypes .= "ss";

if ($filter_store !== '' && is_numeric($filter_store)) {
    $journalWhereClauses[] = "je.store_id = ?";
    $journalParams[] = $filter_store;
    $journalParamTypes .= "i";
}

if ($filter_account !== '' && is_numeric($filter_account)) {
    $journalWhereClauses[] = "jl.account_id = ?";
    $journalParams[] = $filter_account;
    $journalParamTypes .= "i";
}

if ($filter_type !== '') {
    if ($filter_type === 'journal') {
        // no additional filter for journal lines here
    } else {
        $journalWhereClauses[] = "1=0"; // exclude all if filter is not 'journal'
    }
}

if ($search !== '') {
    $journalWhereClauses[] = "(jl.description LIKE ? OR a.account_name LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ?)";
    for ($i = 0; $i < 4; $i++) {
        $journalParams[] = "%$search%";
        $journalParamTypes .= "s";
    }
}

$journalWhereSql = implode(" AND ", $journalWhereClauses);

// Financial Filters
$financialWhereClauses = ["ft.status = 'approved'"];
$financialParams = [];
$financialParamTypes = "";

$financialWhereClauses[] = "ft.transaction_date BETWEEN ? AND ?";
$financialParams[] = $from_date_start;
$financialParams[] = $to_date_end;
$financialParamTypes .= "ss";

if ($filter_store !== '' && is_numeric($filter_store)) {
    $financialWhereClauses[] = "ft.store_id = ?";
    $financialParams[] = $filter_store;
    $financialParamTypes .= "i";
}

if ($filter_account !== '' && is_numeric($filter_account)) {
    $financialWhereClauses[] = "ft.account_id = ?";
    $financialParams[] = $filter_account;
    $financialParamTypes .= "i";
}

if ($filter_type !== '') {
    if ($filter_type === 'journal') {
        $financialWhereClauses[] = "1=0"; // no financial tx for journal filter
    } else {
        $financialWhereClauses[] = "ft.type = ?";
        $financialParams[] = $filter_type;
        $financialParamTypes .= "s";
    }
}

if ($search !== '') {
    $financialWhereClauses[] = "(ft.description LIKE ? OR a.account_name LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ?)";
    for ($i = 0; $i < 4; $i++) {
        $financialParams[] = "%$search%";
        $financialParamTypes .= "s";
    }
}

$financialWhereSql = implode(" AND ", $financialWhereClauses);

$params = array_merge($journalParams, $financialParams);
$paramTypes = $journalParamTypes . $financialParamTypes;

$limit = 20;
$page = max(1, intval($_GET['page'] ?? 1));
$offset = ($page - 1) * $limit;

// Count total records for pagination
$countSql = "
    SELECT COUNT(*) FROM (
        SELECT jl.id
        FROM journal_lines jl
        JOIN journal_entries je ON jl.je_id = je.id
        WHERE $journalWhereSql

        UNION ALL

        SELECT ft.id
        FROM financial_transactions ft
        WHERE $financialWhereSql
    ) AS combined
";

$countStmt = $conn->prepare($countSql);
if ($paramTypes) {
    $countStmt->bind_param($paramTypes, ...$params);
}
$countStmt->execute();
$countStmt->bind_result($total_records);
$countStmt->fetch();
$countStmt->close();

$total_pages = ceil($total_records / $limit);

$sql = "
    SELECT * FROM (
        SELECT 
            jl.id AS id,
            je.entry_date AS source_date,
            'journal' AS source,
            COALESCE(je.description, 'Journal Entry') AS description,
            IFNULL(jl.debit, 0) AS debit,
            IFNULL(jl.credit, 0) AS credit,
            a.account_name,
            s.store_name,
            CONCAT(u.first_name, ' ', u.last_name) AS user_fullname,
            NULL AS type
        FROM journal_lines jl
        JOIN journal_entries je ON jl.je_id = je.id
        LEFT JOIN accounts a ON jl.account_id = a.id
        LEFT JOIN stores s ON je.store_id = s.id
        LEFT JOIN users u ON je.created_by = u.id
        WHERE $journalWhereSql

        UNION ALL

        SELECT 
            ft.id,
            ft.transaction_date AS source_date,
            'financial' AS source,
            ft.description,
            IFNULL(ft.debit, 0),
            IFNULL(ft.credit, 0),
            a.account_name,
            s.store_name,
            CONCAT(u.first_name, ' ', u.last_name),
            ft.type
        FROM financial_transactions ft
        LEFT JOIN accounts a ON ft.account_id = a.id
        LEFT JOIN stores s ON ft.store_id = s.id
        LEFT JOIN users u ON ft.created_by = u.id
        WHERE $financialWhereSql
    ) AS combined
    ORDER BY source_date DESC, id DESC
    LIMIT ? OFFSET ?
";

$params[] = $limit;
$params[] = $offset;
$paramTypes .= "ii";

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

// Group by month-year
$grouped = [];
while ($row = $result->fetch_assoc()) {
    $monthKey = date('F Y', strtotime($row['source_date']));
    $grouped[$monthKey][] = $row;
}

// Export handling (CSV)
if (isset($_GET['export']) && in_array($_GET['export'], ['csv', 'pdf'])) {
    $exportType = $_GET['export'];

    // Fetch all data without pagination for export
    $exportSql = str_replace("LIMIT ? OFFSET ?", "", $sql);
    $exportParams = array_slice($params, 0, -2);
    $exportParamTypes = substr($paramTypes, 0, -2);

    $exportStmt = $conn->prepare($exportSql);
    $exportStmt->bind_param($exportParamTypes, ...$exportParams);
    $exportStmt->execute();
    $exportResult = $exportStmt->get_result();

    if ($exportType === 'csv') {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="general_ledger.csv"');
        $output = fopen('php://output', 'w');

        // CSV headers
        fputcsv($output, ['Date', 'Source', 'Type', 'Debit', 'Credit', 'Account', 'Store', 'Description', 'User']);

        while ($row = $exportResult->fetch_assoc()) {
            fputcsv($output, [
                $row['source_date'],
                ucfirst($row['source']),
                $row['type'] ? ucwords(str_replace('_',' ',$row['type'])) : 'Journal',
                number_format($row['debit'], 2),
                number_format($row['credit'], 2),
                $row['account_name'] ?? '',
                $row['store_name'] ?? '',
                $row['description'] ?? '',
                $row['user_fullname'] ?? '',
            ]);
        }
        fclose($output);
        exit();
    } elseif ($exportType === 'pdf') {
        // Simple PDF export (requires TCPDF, FPDF or similar - here just placeholder)
        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment; filename="general_ledger.pdf"');
        echo "PDF export not implemented yet. Please use CSV export.";
        exit();
    }
}


$page_title = "General Ledger";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title><?= htmlspecialchars($page_title) ?></title>
    <link rel="icon" type="image/png" href="../../images/logo.png" />
    <style>
    body {
        margin: 0;
        background: url('../../images/profile.jpg') no-repeat center center fixed;
        background-size: cover;
        font-family: 'Segoe UI', sans-serif;
        color: #eee;
    }
    .content {
        margin: 2rem auto 0 auto;
        padding: 40px;
        max-width: 1850px;
    }
    .page-header {
        display: flex;
        justify-content: space-between;
        align-items: center;
        margin-bottom: 1rem;
    }
    h1.page-title {
        color: #ffa500;
        margin: 0;
        display: flex;
        align-items: center;
        gap: 10px;
        font-weight: 700;
        font-size: 2rem;
    }
    h1.page-title img {
        height: 40px;
        width: auto;
    }
    a.back-link {
        color: #ffa500;
        text-decoration: none;
        font-weight: 600;
        border: 1.5px solid #ffa500;
        padding: 6px 14px;
        border-radius: 8px;
        transition: background-color 0.3s ease, color 0.3s ease;
        white-space: nowrap;
    }
    a.back-link:hover {
        background-color: #ffa500;
        color: #222;
    }
    h2 { color: #ffa500; }
    .filter-form {
        margin: 20px 0;
        display: flex;
        gap: 15px;
        flex-wrap: wrap;
    }
    .filter-form input, .filter-form select, .filter-form button {
        padding: 8px 12px;
        border-radius: 8px;
        border: none;
        font-weight: 500;
    }
    .filter-form button {
        background: #ffa500;
        color: #222;
        cursor: pointer;
    }
    .card {
        background: rgba(255,255,255,0.1);
        border-radius: 15px;
        padding: 20px;
        margin-top: 20px;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        font-size: 15px;
    }
    table th, table td {
        padding: 10px 12px;
        border-bottom: 1px solid rgba(255,255,255,0.2);
        vertical-align: middle;
        text-align: left; /* Default left for all */
    }
    table th:nth-child(4),
    table th:nth-child(5),
    table td:nth-child(4),
    table td:nth-child(5),
    table td:last-child,
    table th:last-child {
        text-align: right;
    }
    table th {
        background: rgba(255,165,0,0.3);
        color: #fff;
    }
    .pagination {
        text-align: center;
        margin-top: 20px;
    }
    .pagination a, .pagination span.current-page {
        margin: 0 5px;
        padding: 8px 12px;
        background: rgba(255,165,0,0.7);
        border-radius: 5px;
        text-decoration: none;
        color: #222;
        font-weight: bold;
    }
    </style>
</head>
<body>

<div class="content">

    <div class="page-header">
        <h1 class="page-title">
            <img src="../../images/logo.png" alt="Logo" />
            <?= htmlspecialchars($page_title) ?>
        </h1>

        <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
    </div>

    <form method="GET" class="filter-form">
        <input type="date" name="from_date" value="<?= htmlspecialchars($from_date) ?>">
        <input type="date" name="to_date" value="<?= htmlspecialchars($to_date) ?>">

        <select name="store_id">
            <option value="">All Stores</option>
            <?php 
            $stores = $conn->query("SELECT id, store_name FROM stores WHERE deleted_at IS NULL");
            while ($store = $stores->fetch_assoc()):
                $sel = ($filter_store == $store['id']) ? 'selected' : '';
                echo "<option value='{$store['id']}' $sel>" . htmlspecialchars($store['store_name']) . "</option>";
            endwhile; 
            ?>
        </select>

        <select name="account_id">
            <option value="">All Accounts</option>
            <?php 
            $accounts = $conn->query("SELECT id, account_name FROM accounts");
            while ($acc = $accounts->fetch_assoc()):
                $sel = ($filter_account == $acc['id']) ? 'selected' : '';
                echo "<option value='{$acc['id']}' $sel>" . htmlspecialchars($acc['account_name']) . "</option>";
            endwhile; 
            ?>
        </select>

        <select name="type">
            <option value="">All Types</option>
            <option value="journal" <?= $filter_type === 'journal' ? 'selected' : '' ?>>Journal</option>
            <?php 
            foreach (['deposit', 'expense', 'sale_income', 'payment', 'transfer'] as $t):
                $sel = ($filter_type == $t) ? 'selected' : '';
                echo "<option value='$t' $sel>" . ucwords(str_replace('_',' ',$t)) . "</option>";
            endforeach; 
            ?>
        </select>

        <input type="text" name="search" placeholder="Search..." value="<?= htmlspecialchars($search) ?>">

        <button type="submit">Filter</button>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>" style="margin-left:auto; padding: 8px 12px; background:#ffa500; color:#222; border-radius: 8px; text-decoration:none;">Export CSV</a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'pdf'])) ?>" style="padding: 8px 12px; background:#ffa500; color:#222; border-radius: 8px; text-decoration:none;">Export PDF</a>
    </form>

    <?php if (count($grouped) === 0): ?>
        <p>No records found.</p>
    <?php else: ?>
        <?php foreach ($grouped as $month => $transactions): ?>
            <div class="card">
                <h3><?= htmlspecialchars($month) ?></h3>
                <table>
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Source</th>
                            <th>Type</th>
                            <th>Debit</th>
                            <th>Credit</th>
                            <th>Account</th>
                            <th>Store</th>
                            <th>Description</th>
                            <th>User</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php 
                    $running_balance = 0;
                    foreach ($transactions as $tx): 
                        $debit = (float)$tx['debit'];
                        $credit = (float)$tx['credit'];
                        $running_balance += $debit - $credit;
                    ?>
                        <tr>
                            <td><?= htmlspecialchars(date('Y-m-d', strtotime($tx['source_date']))) ?></td>
                            <td><?= htmlspecialchars(ucfirst($tx['source'])) ?></td>
                            <td><?= htmlspecialchars($tx['type'] ? ucwords(str_replace('_',' ',$tx['type'])) : 'Journal') ?></td>
                            <td><?= number_format($debit, 2) ?></td>
                            <td><?= number_format($credit, 2) ?></td>
                            <td><?= htmlspecialchars($tx['account_name'] ?? '') ?></td>
                            <td><?= htmlspecialchars($tx['store_name'] ?? '') ?></td>
                            <td><?= htmlspecialchars($tx['description'] ?? '') ?></td>
                            <td><?= htmlspecialchars($tx['user_fullname'] ?? '') ?></td>
                        </tr>
                    <?php endforeach; ?>
                    <tr style="font-weight:bold; background: rgba(255,165,0,0.2);">
                        <td colspan="3" style="text-align:right;">Running Balance:</td>
                        <td colspan="6" style="text-align:right;"><?= number_format($running_balance, 2) ?></td>
                    </tr>
                    </tbody>
                </table>
            </div>
        <?php endforeach; ?>

        <div class="pagination">
            <?php if ($page > 1): ?>
                <a href="?<?= http_build_query(array_merge($_GET, ['page' => $page - 1])) ?>">&laquo; Prev</a>
            <?php endif; ?>
            <span class="current-page"><?= $page ?></span>
            <?php if ($page < $total_pages): ?>
                <a href="?<?= http_build_query(array_merge($_GET, ['page' => $page + 1])) ?>">Next &raquo;</a>
            <?php endif; ?>
        </div>
    <?php endif; ?>
</div>

<?php include('../../includes/footer.php'); ob_end_flush(); ?>

</body>
</html>