<?php
session_start();
require("php_con.php");
if(!isset($_COOKIE['user_id'])){
    echo "<script>window.location='logout';</script>";
    exit();
}
?>
<?php require("header.php"); ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css">
    <title>Installment Reports</title>
</head>
<body>
<?php include 'navbar.php'; ?>
<div class="container mt-5">
    <h2>Installment Reports</h2>
    
    <ul class="nav nav-tabs" id="myTab" role="tablist">
        <li class="nav-item">
            <a class="nav-link active" id="all-tab" data-toggle="tab" href="#all" role="tab">All Plans</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" id="paid-tab" data-toggle="tab" href="#paid" role="tab">Paid This Month</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" id="unpaid-tab" data-toggle="tab" href="#unpaid" role="tab">Unpaid This Month</a>
        </li>
    </ul>

    <div class="tab-content mt-3" id="myTabContent">
        <div class="tab-pane fade show active" id="all" role="tabpanel">
            <table id="allPlansTable" class="table table-striped table-bordered">
                <thead>
                    <tr>
                        <th>Customer</th>
                        <th>Mobile</th>
                        <th>Product</th>
                        <th>Total Amount</th>
                        <th>Duration (Months)</th>
                        <th>Monthly Payment</th>
                        <th>Remaining Balance</th>
                        <th>Months Paid</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $query = $connation->query("
                        SELECT c.cs_name, c.cs_mobile, ip.product_name, ip.total_amount, ip.duration_months, 
                               ip.monthly_payment, ip.remaining_balance, ip.status, ip.plan_id,
                               (SELECT COUNT(*) FROM installment_payments WHERE plan_id = ip.plan_id) as months_paid
                        FROM installment_plans ip
                        JOIN customer_details c ON ip.cs_id = c.cs_id
                        ORDER BY ip.start_date DESC
                    ");

                    while ($row = $query->fetch_assoc()) {
                        echo "<tr>
                                <td>{$row['cs_name']}</td>
                                <td>{$row['cs_mobile']}</td>
                                <td>{$row['product_name']}</td>
                                <td>{$row['total_amount']}</td>
                                <td>{$row['duration_months']}</td>
                                <td>{$row['monthly_payment']}</td>
                                <td>{$row['remaining_balance']}</td>
                                <td>{$row['months_paid']}</td>
                                <td>{$row['status']}</td>
                            </tr>";
                    }
                    ?>
                </tbody>
            </table>
        </div>
        
        <div class="tab-pane fade" id="paid" role="tabpanel">
            <table id="paidTable" class="table table-striped table-bordered">
                <thead>
                    <tr>
                        <th>Customer</th>
                        <th>Mobile</th>
                        <th>Product</th>
                        <th>Amount Paid</th>
                        <th>Payment Date</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $currentMonth = date('Y-m');
                    $query = $connation->query("
                        SELECT c.cs_name, c.cs_mobile, ip.product_name, ipy.amount_paid, ipy.payment_date
                        FROM installment_payments ipy
                        JOIN installment_plans ip ON ipy.plan_id = ip.plan_id
                        JOIN customer_details c ON ip.cs_id = c.cs_id
                        WHERE DATE_FORMAT(ipy.payment_date, '%Y-%m') = '$currentMonth'
                        ORDER BY ipy.payment_date DESC
                    ");

                    while ($row = $query->fetch_assoc()) {
                        echo "<tr>
                                <td>{$row['cs_name']}</td>
                                <td>{$row['cs_mobile']}</td>
                                <td>{$row['product_name']}</td>
                                <td>{$row['amount_paid']}</td>
                                <td>{$row['payment_date']}</td>
                            </tr>";
                    }
                    ?>
                </tbody>
            </table>
        </div>
        
        <div class="tab-pane fade" id="unpaid" role="tabpanel">
            <table id="unpaidTable" class="table table-striped table-bordered">
                <thead>
                    <tr>
                        <th>Customer</th>
                        <th>Mobile</th>
                        <th>Product</th>
                        <th>Monthly Payment</th>
                        <th>Remaining Balance</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $currentMonth = date('Y-m');
                    $query = $connation->query("
                        SELECT c.cs_name, c.cs_mobile, ip.product_name, ip.monthly_payment, ip.remaining_balance
                        FROM installment_plans ip
                        JOIN customer_details c ON ip.cs_id = c.cs_id
                        WHERE ip.plan_id NOT IN (
                            SELECT DISTINCT plan_id 
                            FROM installment_payments 
                            WHERE DATE_FORMAT(payment_date, '%Y-%m') = '$currentMonth'
                        )
                        AND ip.status = 'active'
                        ORDER BY ip.start_date DESC
                    ");

                    while ($row = $query->fetch_assoc()) {
                        echo "<tr>
                                <td>{$row['cs_name']}</td>
                                <td>{$row['cs_mobile']}</td>
                                <td>{$row['product_name']}</td>
                                <td>{$row['monthly_payment']}</td>
                                <td>{$row['remaining_balance']}</td>
                            </tr>";
                    }
                    ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Bootstrap, jQuery, DataTables JS -->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>

<!-- DataTables Initialization -->
<script>
    $(document).ready(function() {
        $('#allPlansTable').DataTable();
        $('#paidTable').DataTable();
        $('#unpaidTable').DataTable();
    });
</script>
</body>
</html>