<?php
session_start();
require("php_con.php");
if(!isset($_COOKIE['user_id'])){
    echo "<script>window.location='logout';</script>";
    exit();
}

function calculateProfit($connation, $startDate, $endDate) {
    $query = "SELECT 
                SUM(ip.amount_paid) as total_paid,
                SUM(ip.amount_paid - (ip.amount_paid / (1 + ipl.interest_rate))) as interest_profit
              FROM installment_payments ip
              JOIN installment_plans ipl ON ip.plan_id = ipl.plan_id
              WHERE ip.payment_date BETWEEN ? AND ?";
    
    $stmt = $connation->prepare($query);
    $stmt->bind_param("ss", $startDate, $endDate);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    
    return [
        'total_paid' => $result['total_paid'] ?? 0,
        'interest_profit' => $result['interest_profit'] ?? 0
    ];
}

function getProductSales($connation, $startDate, $endDate) {
    $query = "SELECT 
                ipl.product_name,
                COUNT(*) as count,
                SUM(ipl.total_amount) as total_amount
              FROM installment_plans ipl
              WHERE ipl.start_date BETWEEN ? AND ?
              GROUP BY ipl.product_name
              ORDER BY count DESC";
    
    $stmt = $connation->prepare($query);
    $stmt->bind_param("ss", $startDate, $endDate);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

function getDefaultedPayments($connation, $startDate, $endDate) {
    $query = "SELECT 
                c.cs_name,
                c.cs_mobile,
                ipl.product_name,
                ipl.monthly_payment,
                ipl.remaining_balance,
                DATEDIFF(CURDATE(), ipl.start_date) / 30 as months_passed
              FROM installment_plans ipl
              JOIN customer_details c ON ipl.cs_id = c.cs_id
              WHERE ipl.start_date BETWEEN ? AND ?
                AND ipl.status = 'active'
                AND ipl.remaining_balance > 0
                AND DATEDIFF(CURDATE(), ipl.start_date) / 30 > 
                    (SELECT COUNT(*) FROM installment_payments ip WHERE ip.plan_id = ipl.plan_id)";
    
    $stmt = $connation->prepare($query);
    $stmt->bind_param("ss", $startDate, $endDate);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

$startDate = $_GET['start_date'] ?? date('Y-m-01');
$endDate = $_GET['end_date'] ?? date('Y-m-t');

$profit = calculateProfit($connation, $startDate, $endDate);
$productSales = getProductSales($connation, $startDate, $endDate);
$defaultedPayments = getDefaultedPayments($connation, $startDate, $endDate);

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">
    <title>Installment Summary Report</title>
    <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.22/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datepicker/0.6.5/datepicker.min.css">
</head>
<body>
<?php include 'navbar.php'; ?>
<div class="container mt-5">
    <h2>Installment Summary Report</h2>
    
    <form class="mb-4">
        <div class="form-row">
            <div class="col">
                <input type="text" class="form-control datepicker" name="start_date" value="<?php echo $startDate; ?>" placeholder="Start Date">
            </div>
            <div class="col">
                <input type="text" class="form-control datepicker" name="end_date" value="<?php echo $endDate; ?>" placeholder="End Date">
            </div>
            <div class="col">
                <button type="submit" class="btn btn-primary">Generate Report</button>
            </div>
        </div>
    </form>

    <div class="card mb-4">
        <div class="card-header">
            Profit Summary
        </div>
        <div class="card-body">
            <p>Total Payments Received: LKR<?php echo number_format($profit['total_paid'], 2); ?></p>
            <p>Total Interest Profit: LKR<?php echo number_format($profit['interest_profit'], 2); ?></p>
        </div>
    </div>

    <div class="card mb-4">
        <div class="card-header">
            Product Sales Summary
        </div>
        <div class="card-body">
            <table class="table table-striped" id="productSalesTable">
                <thead>
                    <tr>
                        <th>Product Name</th>
                        <th>Number of Sales</th>
                        <th>Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($productSales as $product): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($product['product_name']); ?></td>
                        <td><?php echo $product['count']; ?></td>
                        <td>LKR<?php echo number_format($product['total_amount'], 2); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="card mb-4">
        <div class="card-header">
            Defaulted Payments
        </div>
        <div class="card-body">
            <table class="table table-striped" id="defaultedPaymentsTable">
                <thead>
                    <tr>
                        <th>Customer Name</th>
                        <th>Mobile</th>
                        <th>Product</th>
                        <th>Monthly Payment</th>
                        <th>Remaining Balance</th>
                        <th>Months Passed</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($defaultedPayments as $defaulted): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($defaulted['cs_name']); ?></td>
                        <td><?php echo htmlspecialchars($defaulted['cs_mobile']); ?></td>
                        <td><?php echo htmlspecialchars($defaulted['product_name']); ?></td>
                        <td>LKR<?php echo number_format($defaulted['monthly_payment'], 2); ?></td>
                        <td>LKR<?php echo number_format($defaulted['remaining_balance'], 2); ?></td>
                        <td><?php echo floor($defaulted['months_passed']); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.5.3/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/datepicker/0.6.5/datepicker.min.js"></script>

<script>
$(document).ready(function() {
    $('.datepicker').datepicker({
        format: 'yyyy-mm-dd',
        autoclose: true
    });

    $('#productSalesTable').DataTable();
    $('#defaultedPaymentsTable').DataTable();
});
</script>
</body>
</html>