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

$annual_rate = 6; // Annual interest rate

// Fetch all FDR statements ordered by transaction_date
$sql = "SELECT * FROM fdr_statement ORDER BY transaction_date ASC";
$result = mysqli_query($conn, $sql);

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

// Calculate interest
$previous_date = null;
$interests = [];

foreach ($fdr_data as $index => $row) {
    $transaction_date = $row['transaction_date'];
    $value_date = $row['value_date'];
    $balance = floatval($row['balance']);

    $current_date = new DateTime($value_date);

    if ($previous_date === null) {
        $days = 0;
    } else {
        $interval = $previous_date->diff($current_date);
        $days = $interval->days;
    }

    $interest = $balance * ($annual_rate / 100) * ($days / 365);

    $interests[] = [
        'sno' => $index + 1,
        'transaction_date' => $transaction_date,
        'value_date' => $value_date,
        'balance' => $balance,
        'days' => $days,
        'interest' => round($interest, 2)
    ];

    $previous_date = $current_date;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>FDR Interest Calculation</title>

    <!-- DataTables CSS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">

    <style>
        body { margin:0; font-family: Arial, sans-serif; background: #f4f6f9; }
        .content { margin-left:250px; padding:30px; padding-top:90px; }

        .header { height: 60px; background:#fff; border-bottom:1px solid #ddd; position:fixed; top:0; left:250px; 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; }
        .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">
    <h2>FDR Interest Calculation</h2>
    <table id="fdrTable" class="display">
        <thead>
        <tr>
            <th>S.No</th>
            <th>Transaction Date</th>
            <th>Value Date</th>
            <th>Balance</th>
            <th>Days</th>
            <th>Interest</th>
        </tr>
        </thead>
        <tbody>
        <?php foreach ($interests as $row) { ?>
            <tr>
                <td><?php echo $row['sno']; ?></td>
                <td><?php echo $row['transaction_date']; ?></td>
                <td><?php echo $row['value_date']; ?></td>
                <td><?php echo number_format($row['balance'], 2); ?></td>
                <td><?php echo $row['days']; ?></td>
                <td><?php echo number_format($row['interest'], 2); ?></td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
</div>

<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<!-- DataTables JS -->
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>

<script>
    $(document).ready(function() {
        $('#fdrTable').DataTable({
            "pageLength": 20,  // Show 20 rows per page
            "order": [[1, "asc"]]  // Default sort by Transaction Date ascending
        });
    });
</script>

</body>
</html>
