<?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');

// Filters
$from = $_GET['from_date'] ?? date('Y-01-01');
$to   = $_GET['to_date'] ?? date('Y-m-d');
$store_id = $_GET['store_id'] ?? '';

$where = ["ft.status = 'approved'", "ft.transaction_date BETWEEN ? AND ?"];
$params = [$from, $to];
$types = "ss";

if ($store_id != '') {
    $where[] = "ft.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}

$whereSql = implode(" AND ", $where);

// Fetch balances
$sql = "
SELECT a.id as account_id, a.account_name, a.account_type,
       COALESCE(SUM(ft.debit),0) as total_debit,
       COALESCE(SUM(ft.credit),0) as total_credit
FROM accounts a
LEFT JOIN financial_transactions ft ON ft.account_id = a.id AND $whereSql
GROUP BY a.id, a.account_name, a.account_type
";

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

$accounts = [];
$totals = [
    'assets' => 0,
    'liabilities' => 0,
    'equity' => 0
];

while ($row = $res->fetch_assoc()) {
    $balance = $row['total_debit'] - $row['total_credit'];
    $row['balance'] = $balance;
    $accounts[$row['account_type']][] = $row;

    if ($row['account_type'] == 'asset') $totals['assets'] += $balance;
    elseif ($row['account_type'] == 'liability') $totals['liabilities'] += $balance;
    elseif ($row['account_type'] == 'equity') $totals['equity'] += $balance;
}
$stmt->close();


$page_title = "Balance Sheet";
?>

<!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') center/cover fixed;
        font-family: 'Segoe UI', sans-serif;
        color: #eee;
    }
    .wrap {
        max-width: 1850px;
        margin: 60px auto;
        padding: 20 20px;
    }
    .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;
        margin-bottom: 10px;
    }
    .filters {
        margin-bottom: 20px;
        display: flex;
        gap: 10px;
        flex-wrap: wrap;
    }
    .filters input,
    .filters select,
    .filters button,
    .filters a {
        padding: 8px 12px;
        border: none;
        border-radius: 8px;
        font-weight: 500;
        background: rgba(255, 255, 255, 0.15);
        color: #fff;
    }
    .filters button {
        background: #ffa500;
        color: #222;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        font-size: 15px;
        margin-bottom: 30px;
        background: rgba(255, 255, 255, 0.1);
        border-radius: 10px;
        overflow: hidden;
    }
    th, td {
        padding: 10px;
        border-bottom: 1px solid rgba(255, 255, 255, 0.2);
        text-align: left;
    }
    td:last-child, th:last-child {
        text-align: right;
    }
    th {
        background: rgba(255, 165, 0, 0.3);
    }
    .section-title {
        margin-top: 30px;
        font-size: 16px;
        color: #ffa500;
    }
    tfoot td {
        font-weight: bold;
    }
    a.drill {
        color: #ffa500;
        text-decoration: none;
        font-weight: bold;
    }
    </style>
</head>
<body>

<div class="wrap">
    <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 class="filters" method="GET">
        <input type="date" name="from_date" value="<?= htmlspecialchars($from) ?>">
        <input type="date" name="to_date" value="<?= htmlspecialchars($to) ?>">
        <select name="store_id">
            <option value="">All Stores</option>
            <?php
            $stores = $conn->query("SELECT id, store_name FROM stores");
            while ($s = $stores->fetch_assoc()) {
     
                $sel = ($store_id == $s['id']) ? 'selected' : '';
                echo "<option value='{$s['id']}' $sel>" . htmlspecialchars($s['store_name']) . "</option>";
            }
            ?>
        </select>
        <button type="submit">Filter</button>
        <a href="?<?= http_build_query(array_merge($_GET, ['export' => 'csv'])) ?>">Export CSV</a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export' => 'pdf'])) ?>">Export PDF</a>
    </form>

    <?php
    function renderSection($title, $data, $key) {
        global $from, $to, $store_id;
        $total = 0;
        echo "<h3 class='section-title'>" . ucwords($title) . "</h3>";
        echo "<table><thead><tr><th>Account</th><th>Balance</th></tr></thead><tbody>";
        if (!empty($data)) {
            foreach ($data as $acc) {
                $total += $acc['balance'];
                $link = "trial_balance_detail.php?acct_id={$acc['account_id']}&from_date={$from}&to_date={$to}&store_id={$store_id}";
                echo "<tr><td><a class='drill' href='$link' target='_blank'>" . htmlspecialchars($acc['account_name']) . "</a></td><td>" . number_format($acc['balance'], 2) . "</td></tr>";
            }
        } else {
            echo "<tr><td colspan='2'>No accounts found.</td></tr>";
        }
        echo "</tbody><tfoot><tr><td>Total " . ucfirst($title) . "</td><td>" . number_format($total, 2) . "</td></tr></tfoot></table>";
    }

    renderSection('Assets', $accounts['asset'] ?? [], 'assets');
    renderSection('Liabilities', $accounts['liability'] ?? [], 'liabilities');
    renderSection('Equity', $accounts['equity'] ?? [], 'equity');
    ?>

    <h3 class="section-title">Overall</h3>
    <table>
        <thead>
        <tr>
            <th>Total Assets</th>
            <th>Total Liabilities</th>
            <th>Total Equity</th>
            <th>Assets - (Liabilities + Equity)</th>
        </tr>
        </thead>
        <tbody>
        <tr>
            <td><?= number_format($totals['assets'], 2) ?></td>
            <td><?= number_format($totals['liabilities'], 2) ?></td>
            <td><?= number_format($totals['equity'], 2) ?></td>
            <td><?= number_format($totals['assets'] - ($totals['liabilities'] + $totals['equity']), 2) ?></td>
        </tr>
        </tbody>
    </table>
</div>

<?php
// Simple CSV export logic
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename=balance_sheet_' . date('Ymd') . '.csv');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Section', 'Account', 'Balance']);

    foreach (['asset', 'liability', 'equity'] as $section) {
        if (!empty($accounts[$section])) {
            foreach ($accounts[$section] as $a) {
                fputcsv($out, [ucfirst($section), $a['account_name'], $a['balance']]);
            }
        }
    }

    fputcsv($out, ['', 'Total Assets', $totals['assets']]);
    fputcsv($out, ['', 'Total Liabilities', $totals['liabilities']]);
    fputcsv($out, ['', 'Total Equity', $totals['equity']]);
    fputcsv($out, ['', 'Net Balance', $totals['assets'] - ($totals['liabilities'] + $totals['equity'])]);
    fclose($out);
    exit();
}

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