<?php
include("connect.php");

/* 🔢 Interest rate */
$interestRate = 4; // 4 % per annum

/* 📅 Date filter */
$fromDate = $_GET['from_date'] ?? '';
$toDate   = $_GET['to_date'] ?? '';

$where = "";
if ($fromDate && $toDate) {
    $where = "WHERE value_date BETWEEN '$fromDate' AND '$toDate'";
}

/* 📊 Fetch bank statement ordered by value date */
$sql = "SELECT value_date, balance 
        FROM bank_statement 
        $where
        ORDER BY value_date ASC";

$result = mysqli_query($conn, $sql);

$rows = [];
while ($row = mysqli_fetch_assoc($result)) {
    $rows[] = $row;
}

$interestRows = [];
$totalInterest = 0;

for ($i = 0; $i < count($rows) - 1; $i++) {

    $startDate = new DateTime($rows[$i]['value_date']);
    $endDate   = new DateTime($rows[$i + 1]['value_date']);

    $days = $startDate->diff($endDate)->days;

    if ($days == 0) continue;

    $balance = $rows[$i]['balance'];

    $interest = ($balance * $interestRate * $days) / 36500;

    $interestRows[] = [
        'from'     => $rows[$i]['value_date'],
        'to'       => $rows[$i + 1]['value_date'],
        'days'     => $days,
        'balance'  => $balance,
        'interest' => $interest
    ];

    $totalInterest += $interest;
}

/* 🔚 Last row till To Date */
if (count($rows) > 0 && $toDate) {

    $lastRow = end($rows);

    $startDate = new DateTime($lastRow['value_date']);
    $endDate   = new DateTime($toDate);
    $days      = $startDate->diff($endDate)->days + 1;

    if ($days > 0) {
        $interest = ($lastRow['balance'] * $interestRate * $days) / 36500;

        $interestRows[] = [
            'from'     => $lastRow['value_date'],
            'to'       => $toDate,
            'days'     => $days,
            'balance'  => $lastRow['balance'],
            'interest' => $interest
        ];

        $totalInterest += $interest;
    }
}
?>
<!DOCTYPE html>
<html>

<head>
    <title>Interest Statement</title>

    <style>
        body {
            margin: 0;
            font-family: Arial;
            background: #f4f6f9;
        }

        .content {
            margin-left: 250px;
            padding: 90px 30px;
        }

        .card {
            background: #fff;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 0 10px rgba(0, 0, 0, .1);
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }

        th,
        td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: center;
        }

        th {
            background: #007bff;
            color: #fff;
        }

        .total {
            font-weight: bold;
            background: #f1f1f1;
        }

        .header {
            height: 60px;
            background: #ffffff;
            border-bottom: 1px solid #ddd;
            position: fixed;
            top: 0;
            left: 250px;
            /* sidebar width */
            right: 0;
            display: flex;
            align-items: center;
            justify-content: space-between;
            padding: 0 20px;
            box-shadow: 0 2px 5px rgba(0, 0, 0, 0.05);
            z-index: 1000;
        }

        .header-left {
            font-size: 16px;
            font-weight: bold;
        }

        .logout-btn {
            background: #dc3545;
            color: #fff;
            padding: 8px 14px;
            text-decoration: none;
            border-radius: 5px;
            font-size: 14px;
        }

        .logout-btn:hover {
            background: #b52a37;
        }
    </style>
</head>

<body>

    <?php include("sidebar.php"); ?>
    <?php include("header.php"); ?>

    <div class="content">
        <div class="card">

            <h2>Bank Interest Statement</h2>

            <!-- 📅 Filter -->
            <form method="get">
                From:
                <input type="date" name="from_date" value="<?php echo $fromDate; ?>" required>
                To:
                <input type="date" name="to_date" value="<?php echo $toDate; ?>" required>
                <button type="submit">Calculate</button>
            </form>

            <table>
                <tr>
                    <th>From Date</th>
                    <th>To Date</th>
                    <th>Days</th>
                    <th>Balance</th>
                    <th>Interest</th>
                </tr>

                <?php if (!empty($interestRows)) {
                    foreach ($interestRows as $row) { ?>
                        <tr>
                            <td><?php echo $row['from']; ?></td>
                            <td><?php echo $row['to']; ?></td>
                            <td><?php echo $row['days']; ?></td>
                            <td><?php echo number_format($row['balance'], 2); ?></td>
                            <td><?php echo number_format($row['interest'], 2); ?></td>
                        </tr>
                    <?php } ?>

                    <tr class="total">
                        <td colspan="4">Total Interest</td>
                        <td><?php echo number_format($totalInterest, 2); ?></td>
                    </tr>

                <?php } else { ?>
                    <tr>
                        <td colspan="5">No data found</td>
                    </tr>
                <?php } ?>

            </table>

        </div>
    </div>

</body>

</html>