<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Handle filters
$where_clauses = [];
$params = [];
$param_types = '';

// Date range filter
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
if ($start_date && $end_date) {
    $where_clauses[] = "i.invoice_date BETWEEN ? AND ?";
    $params[] = $start_date;
    $params[] = $end_date;
    $param_types .= 'ss';
}

// Payment status filter
$payment_status = $_GET['payment_status'] ?? '';
if ($payment_status) {
    switch ($payment_status) {
        case 'unpaid':
            $where_clauses[] = "(i.allocated_amt IS NULL OR i.allocated_amt = 0)";
            break;
        case 'partial':
            $where_clauses[] = "(i.allocated_amt > 0 AND i.allocated_amt < i.invoice_total)";
            break;
        case 'paid':
            $where_clauses[] = "(i.allocated_amt = i.invoice_total)";
            break;
    }
}

// Corporate filter
$corp_id = $_GET['corp_id'] ?? '';
if ($corp_id) {
    $where_clauses[] = "i.corp_id = ?";
    $params[] = $corp_id;
    $param_types .= 's';
}

// Build query
$where_sql = $where_clauses ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

// Fetch invoices
$sql = "SELECT 
    d.*,
    c.corporate as corporate_name,
    pa.surname, pa.first_name,
    u.full_name as created_by_name,
    (
        SELECT SUM(receipt_amount) 
        FROM premium_receipt r 
        WHERE r.corp_id = d.corp_id
        AND r.receipt_date >= d.debit_date 
        AND r.receipt_date <= d.period_to
        AND (r.reversed = 0 OR r.reversed IS NULL)
    ) as paid_amount,
    (d.amount - COALESCE(
        (SELECT SUM(receipt_amount) 
         FROM premium_receipt r 
         WHERE r.corp_id = d.corp_id
         AND r.receipt_date >= d.debit_date 
         AND r.receipt_date <= d.period_to
         AND (r.reversed = 0 OR r.reversed IS NULL)
        ), 0
    )) as balance,
    CASE 
        WHEN (
            SELECT SUM(receipt_amount) 
            FROM premium_receipt r 
            WHERE r.corp_id = d.corp_id
            AND r.receipt_date >= d.debit_date 
            AND r.receipt_date <= d.period_to
            AND (r.reversed = 0 OR r.reversed IS NULL)
        ) >= d.amount THEN 'PAID'
        WHEN (
            SELECT SUM(receipt_amount) 
            FROM premium_receipt r 
            WHERE r.corp_id = d.corp_id
            AND r.receipt_date >= d.debit_date 
            AND r.receipt_date <= d.period_to
            AND (r.reversed = 0 OR r.reversed IS NULL)
        ) > 0 THEN 'PARTIAL'
        ELSE 'UNPAID'
    END as payment_status,
    CASE 
        WHEN (
            SELECT SUM(receipt_amount) 
            FROM premium_receipt r 
            WHERE r.corp_id = d.corp_id
            AND r.receipt_date >= d.debit_date 
            AND r.receipt_date <= d.period_to
            AND (r.reversed = 0 OR r.reversed IS NULL)
        ) IS NOT NULL 
        THEN (
            (SELECT SUM(receipt_amount) 
             FROM premium_receipt r 
             WHERE r.corp_id = d.corp_id
             AND r.receipt_date >= d.debit_date 
             AND r.receipt_date <= d.period_to
             AND (r.reversed = 0 OR r.reversed IS NULL)
            ) / d.amount * 100
        ) 
        ELSE 0 
    END as payment_percentage,
    (
        SELECT GROUP_CONCAT(r.receipt_no ORDER BY r.receipt_date DESC)
        FROM premium_receipt r
        WHERE r.corp_id = d.corp_id 
        AND (r.reversed = 0 OR r.reversed IS NULL)
        AND r.receipt_date >= d.debit_date
        AND r.receipt_date <= d.period_to
    ) as receipt_numbers,
    (
        SELECT COUNT(di.item_id)
        FROM premium_debit_items di
        WHERE di.debit_id = d.debit_id
    ) as items_count,
    (
        SELECT GROUP_CONCAT(
            CONCAT(b.benefit, ':', di.amount) 
            ORDER BY di.benefit_code
        )
        FROM premium_debit_items di
        JOIN benefit b ON di.benefit_code = b.code
        WHERE di.debit_id = d.debit_id
    ) as premium_details
FROM premium_debit_notes d
LEFT JOIN corporate c ON d.corp_id = c.corp_id
LEFT JOIN principal_applicant pa ON d.family_no = pa.family_no
LEFT JOIN users u ON d.created_by = u.user_id
$where_sql
ORDER BY d.debit_date DESC";

$stmt = $conn->prepare($sql);
if ($params && $param_types) {
    $stmt->bind_param($param_types, ...$params);
}
$stmt->execute();
$invoices = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Fetch corporates for filter
$sql_corporates = "SELECT corp_id, corporate FROM corporate WHERE cancelled = 0 ORDER BY corporate";
$result_corporates = $conn->query($sql_corporates);
$corporates = $result_corporates->fetch_all(MYSQLI_ASSOC);

$conn->close();
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'View Invoices')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap4.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap4.min.css" rel="stylesheet">
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Finance', 'title' => 'Premium Invoices')); ?>

                    <!-- Summary Cards -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-primary-subtle rounded fs-3">
                                                <i class="ri-file-list-3-line text-primary"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-medium text-muted text-truncate mb-0">Total Invoices</p>
                                            <h4 class="fs-4 mb-0"><?php 
                                                echo number_format(array_sum(array_column($invoices, 'invoice_total')), 2); 
                                            ?></h4>
                                            <p class="text-muted mb-0"><?php echo count($invoices); ?> invoices</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-success-subtle rounded fs-3">
                                                <i class="ri-money-dollar-circle-line text-success"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-medium text-muted text-truncate mb-0">Total Received</p>
                                            <h4 class="fs-4 mb-0"><?php 
                                                echo number_format(array_sum(array_column($invoices, 'allocated_amt')), 2); 
                                            ?></h4>
                                            <p class="text-muted mb-0"><?php 
                                                echo count(array_filter($invoices, function($i) {
                                                    return $i['payment_status'] === 'PAID';
                                                })); 
                                            ?> fully paid</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-warning-subtle rounded fs-3">
                                                <i class="ri-arrow-right-circle-line text-warning"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-medium text-muted text-truncate mb-0">Outstanding</p>
                                            <h4 class="fs-4 mb-0"><?php 
                                                echo number_format(array_sum(array_column($invoices, 'balance')), 2); 
                                            ?></h4>
                                            <p class="text-muted mb-0"><?php 
                                                echo count(array_filter($invoices, function($i) {
                                                    return $i['payment_status'] !== 'PAID';
                                                })); 
                                            ?> pending</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-info-subtle rounded fs-3">
                                                <i class="ri-percent-line text-info"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-medium text-muted text-truncate mb-0">Collection Rate</p>
                                            <h4 class="fs-4 mb-0"><?php 
                                                $total = array_sum(array_column($invoices, 'invoice_total'));
                                                $collected = array_sum(array_column($invoices, 'allocated_amt'));
                                                echo $total > 0 ? number_format(($collected / $total) * 100, 1) : '0';
                                            ?>%</h4>
                                            <p class="text-muted mb-0"><?php 
                                                echo count(array_filter($invoices, function($i) {
                                                    return $i['payment_status'] === 'PARTIAL';
                                                })); 
                                            ?> partial payments</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters -->
                    <div class="row mb-3">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-body">
                                    <form method="GET" class="row gy-2">
                                        <div class="col-md-3">
                                            <label class="form-label">Date Range</label>
                                            <input type="date" class="form-control" name="start_date" value="<?php echo $start_date; ?>">
                                        </div>
                                        <div class="col-md-3">
                                            <label class="form-label">&nbsp;</label>
                                            <input type="date" class="form-control" name="end_date" value="<?php echo $end_date; ?>">
                                        </div>
                                        <div class="col-md-3">
                                            <label class="form-label">Corporate</label>
                                            <select class="form-select" name="corp_id">
                                                <option value="">All Corporates</option>
                                                <?php foreach($corporates as $corp): ?>
                                                    <option value="<?php echo $corp['corp_id']; ?>" 
                                                            <?php echo $corp_id === $corp['corp_id'] ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($corp['corporate']); ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <label class="form-label">Payment Status</label>
                                            <select class="form-select" name="payment_status">
                                                <option value="">All Status</option>
                                                <option value="unpaid" <?php echo $payment_status === 'unpaid' ? 'selected' : ''; ?>>
                                                    Unpaid
                                                </option>
                                                <option value="partial" <?php echo $payment_status === 'partial' ? 'selected' : ''; ?>>
                                                    Partially Paid
                                                </option>
                                                <option value="paid" <?php echo $payment_status === 'paid' ? 'selected' : ''; ?>>
                                                    Fully Paid
                                                </option>
                                            </select>
                                        </div>
                                        <div class="col-md-1 d-flex align-items-end">
                                            <button type="submit" class="btn btn-primary w-100">
                                                Filter
                                            </button>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Invoices Table -->
                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-body">
                                    <div id="table-gridjs-wrapper" class="table-responsive">
                                        <table id="invoicesTable" class="table table-bordered align-middle table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th scope="col">Invoice No</th>
                                                    <th scope="col">Date</th>
                                                    <th scope="col">Corporate</th>
                                                    <th scope="col" class="text-end">Amount</th>
                                                    <th scope="col">Receipts</th>
                                                    <th scope="col">Payment Status</th>
                                                    <th scope="col">Progress</th>
                                                    <th scope="col">Balance</th>
                                                    <th scope="col">Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($invoices as $invoice): ?>
                                                <tr>
                                                    <td>
                                                        <a href="view_debit.php?id=<?php echo $invoice['debit_id']; ?>" 
                                                           class="fw-medium">
                                                            <?php echo $invoice['debit_id']; ?>
                                                        </a>
                                                    </td>
                                                    <td><?php echo date('d M Y', strtotime($invoice['invoice_date'])); ?></td>
                                                    <td><?php echo htmlspecialchars($invoice['corporate_name']); ?></td>
                                                    <td class="text-end">
                                                        <?php echo number_format($invoice['invoice_total'], 2); ?>
                                                    </td>
                                                    <td>
                                                        <?php if ($invoice['receipt_numbers']): ?>
                                                            <?php 
                                                            $receipts = explode(',', $invoice['receipt_numbers']);
                                                            foreach ($receipts as $receipt): ?>
                                                                <span class="badge bg-light text-dark">
                                                                    <?php echo $receipt; ?>
                                                                </span>
                                                            <?php endforeach; ?>
                                                        <?php else: ?>
                                                            <span class="text-muted">No receipts</span>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-<?php 
                                                            echo $invoice['payment_status'] === 'PAID' ? 'success' : 
                                                                ($invoice['payment_status'] === 'PARTIAL' ? 'warning' : 'danger'); 
                                                        ?>">
                                                            <?php echo $invoice['payment_status']; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <div class="progress" style="height: 6px;">
                                                            <div class="progress-bar bg-<?php 
                                                                echo $invoice['payment_percentage'] >= 100 ? 'success' : 
                                                                    ($invoice['payment_percentage'] > 50 ? 'info' : 'warning'); 
                                                                ?>" 
                                                                role="progressbar" 
                                                                style="width: <?php echo $invoice['payment_percentage']; ?>%" 
                                                                aria-valuenow="<?php echo $invoice['payment_percentage']; ?>" 
                                                                aria-valuemin="0" 
                                                                aria-valuemax="100">
                                                            </div>
                                                        </div>
                                                        <small class="text-muted">
                                                            <?php echo number_format($invoice['payment_percentage'], 1); ?>%
                                                        </small>
                                                    </td>
                                                    <td>
                                                        <?php if ($invoice['balance'] > 0): ?>
                                                            <span class="text-danger fw-medium">
                                                                <?php echo number_format($invoice['balance'], 2); ?>
                                                            </span>
                                                        <?php else: ?>
                                                            <span class="text-success">
                                                                Paid in Full
                                                            </span>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <div class="hstack gap-2">
                                                            <a href="view_debit.php?id=<?php echo $invoice['debit_id']; ?>" 
                                                               class="btn btn-sm btn-soft-info" title="View Invoice">
                                                                <i class="ri-eye-line"></i>
                                                            </a>

                                                            <?php if ($invoice['balance'] > 0): ?>
                                                                <a href="add_receipt.php?id=<?php echo $invoice['debit_id']; ?>" 
                                                                   class="btn btn-sm btn-soft-success" title="Add Payment">
                                                                    <i class="ri-money-dollar-circle-line"></i>
                                                                </a>
                                                            <?php endif; ?>

                                                            <button type="button" 
                                                                    class="btn btn-sm btn-soft-primary print-invoice" 
                                                                    title="Print Invoice"
                                                                    data-invoice-no="<?php echo $invoice['invoice_no']; ?>">
                                                                <i class="ri-printer-line"></i>
                                                            </button>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                            <tfoot>
                                                <tr>
                                                    <td colspan="3" class="fw-bold text-end">Totals:</td>
                                                    <td class="text-end fw-bold">
                                                        <?php echo number_format(array_sum(array_column($invoices, 'invoice_total')), 2); ?>
                                                    </td>
                                                    <td></td>
                                                    <td></td>
                                                    <td></td>
                                                    <td class="fw-bold text-danger">
                                                        <?php echo number_format(array_sum(array_column($invoices, 'balance')), 2); ?>
                                                    </td>
                                                    <td></td>
                                                </tr>
                                            </tfoot>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
<script src="assets/js/app.js"></script>
    <!-- DataTables JS -->
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/dataTables.bootstrap4.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap4.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.print.min.js"></script>

    <script>
        $(document).ready(function() {
            // Initialize DataTable
            var table = $('#invoicesTable').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'collection',
                        text: '<i class="ri-download-2-line"></i> Export',
                        buttons: [
                            {
                                extend: 'copy',
                                text: '<i class="ri-file-copy-line me-2"></i>Copy',
                                className: 'dropdown-item'
                            },
                            {
                                extend: 'excel',
                                text: '<i class="ri-file-excel-line me-2"></i>Excel',
                                className: 'dropdown-item'
                            },
                            {
                                extend: 'pdf',
                                text: '<i class="ri-file-pdf-line me-2"></i>PDF',
                                className: 'dropdown-item'
                            },
                            {
                                extend: 'print',
                                text: '<i class="ri-printer-line me-2"></i>Print',
                                className: 'dropdown-item'
                            }
                        ],
                        className: 'btn btn-sm btn-light'
                    }
                ],
                ordering: true,
                pageLength: 25,
                order: [[1, 'desc']], // Sort by date descending
                columnDefs: [
                    { orderable: false, targets: [4, 6, 8] }, // Disable sorting for receipts, progress, and actions columns
                    { type: 'num', targets: [3, 7] } // Use numeric sorting for amount columns
                ],
                footerCallback: function(row, data, start, end, display) {
                    var api = this.api();
                    
                    // Calculate total amount
                    var totalAmount = api
                        .column(3)
                        .data()
                        .reduce(function(a, b) {
                            return a + parseFloat(b.replace(/[^\d.-]/g, ''));
                        }, 0);

                    // Calculate total balance
                    var totalBalance = api
                        .column(7)
                        .data()
                        .reduce(function(a, b) {
                            var value = b.includes('Paid in Full') ? 0 : parseFloat(b.replace(/[^\d.-]/g, ''));
                            return a + value;
                        }, 0);

                    // Update footer
                    $(api.column(3).footer()).html(totalAmount.toLocaleString('en-US', {minimumFractionDigits: 2}));
                    $(api.column(7).footer()).html(totalBalance.toLocaleString('en-US', {minimumFractionDigits: 2}));
                }
            });

            // Handle print invoice button click
            $('.print-invoice').click(function() {
                var invoiceNo = $(this).data('invoice-no');
                window.open('print_invoice.php?id=' + invoiceNo, '_blank');
            });
        });
    </script>
</body>
</html>