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

if (!isset($_SESSION['user_id'])) {
   header('Location: login.php');
   exit();
}

// Get all commission records
$sql = "SELECT 
   pdn.*,
   c.corporate,
   u.username as calculated_by_name
FROM premium_debit_notes pdn
JOIN corporate c ON pdn.corp_id = c.corp_id 
LEFT JOIN users u ON pdn.calculated_by = u.user_id
WHERE pdn.commission_calculated = 1
ORDER BY pdn.calculation_date DESC";

$result = $conn->query($sql);
$commissions = $result->fetch_all(MYSQLI_ASSOC);

$conn->close();
?>

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

<head>
   <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Commission Records')); ?>
   <?php include 'layouts/head-css.php'; ?>
   <!-- DataTables CSS. -->
<link href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css" rel="stylesheet">
<!-- DateRangePicker CSS -->
<link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />

<style>
    .filter-row {
        background-color: #f8f9fa;
        padding: 1rem;
        margin-bottom: 1rem;
        border-radius: 0.25rem;
    }
    .search-box {
        position: relative;
    }
    .search-box .search-icon {
        position: absolute;
        left: 13px;
        top: 50%;
        transform: translateY(-50%);
        color: #74788d;
    }
    .search-box .form-control {
        padding-left: 35px;
    }
</style>
</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' => 'Commission Records')); ?>

                   <div class="row">
                       <div class="col-xxl-12">
                           <div class="card">
                               <div class="card-header align-items-center d-flex">
                                   <h4 class="card-title mb-0 flex-grow-1">All Commissions</h4>
                                   
                                   <div class="d-flex gap-2">
                                       <button type="button" class="btn btn-success btn-sm" onclick="exportToExcel()">
                                           <i class="ri-file-excel-line align-middle me-1"></i> Export
                                       </button>
                                       <button type="button" class="btn btn-primary btn-sm" onclick="window.print()">
                                           <i class="ri-printer-line align-middle me-1"></i> Print
                                       </button>
                                   </div>
                               </div>

                               <div class="card-body">
                                   <div class="row mb-3">
                                       <div class="col-md-4">
                                           <div class="input-group">
                                               <span class="input-group-text">Date Range</span>
                                               <input type="text" class="form-control" id="dateRange">
                                           </div>
                                       </div>
                                       <div class="col-md-4">
                                           <select class="form-select" id="corporateFilter">
                                               <option value="">All Corporates</option>
                                               <?php
                                               $corps = array_unique(array_column($commissions, 'corporate'));
                                               foreach($corps as $corp) {
                                                   echo "<option value='".htmlspecialchars($corp)."'>".htmlspecialchars($corp)."</option>";
                                               }
                                               ?>
                                           </select>
                                       </div>
                                   </div>

                                   <div class="table-responsive">
                                       <table class="table table-bordered" id="commissionsTable">
                                           <thead>
                                               <tr>
                                                   <th>Corporate</th>
                                                   <th>Debit No</th>
                                                   <th>Date</th>
                                                   <th>Gross Premium</th>
                                                   <th>PHCF Amount</th>
                                                   <th>Client Type</th>
                                                   <th>Commission</th>
                                                   <th>Calculated By</th>
                                                   <th>Status</th>
                                               </tr>
                                           </thead>
                                           <tbody>
                                               <?php foreach($commissions as $comm): 
                                                   $phcf = $comm['amount'] * 0.0045;
                                               ?>
                                               <tr>
                                                   <td><?php echo htmlspecialchars($comm['corporate']); ?></td>
                                                   <td><?php echo $comm['debit_no']; ?></td>
                                                   <td><?php echo date('d/m/Y', strtotime($comm['calculation_date'])); ?></td>
                                                   <td class="text-end"><?php echo number_format($comm['amount'], 2); ?></td>
                                                   <td class="text-end"><?php echo number_format($phcf, 2); ?></td>
                                                   <td><?php echo $comm['is_new_client'] ? 'New' : 'Existing'; ?></td>
                                                   <td class="text-end"><?php echo number_format($comm['commission_amount'], 2); ?></td>
                                                   <td><?php echo htmlspecialchars($comm['calculated_by_name']); ?></td>
                                                   <td>
                                                       <span class="badge bg-success"><?php echo $comm['status']; ?></span>
                                                   </td>
                                               </tr>
                                               <?php endforeach; ?>
                                           </tbody>
                                       </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>
<!-- Required libraries -->
<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.bootstrap5.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.bootstrap5.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<!-- DateRangePicker -->
<script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>

<script>
$(document).ready(function() {
    // Initialize DateRangePicker
    $('#dateRange').daterangepicker({
        startDate: moment().subtract(29, 'days'),
        endDate: moment(),
        ranges: {
           'Today': [moment(), moment()],
           'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
           'Last 7 Days': [moment().subtract(6, 'days'), moment()],
           'Last 30 Days': [moment().subtract(29, 'days'), moment()],
           'This Month': [moment().startOf('month'), moment().endOf('month')],
           'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
        }
    }, function(start, end, label) {
        table.draw();
    });

    // Custom date range filter
    $.fn.dataTable.ext.search.push(
        function(settings, data, dataIndex) {
            var dateRange = $('#dateRange').val();
            if (!dateRange) {
                return true;
            }

            var dates = dateRange.split(' - ');
            var startDate = moment(dates[0], 'MM/DD/YYYY');
            var endDate = moment(dates[1], 'MM/DD/YYYY');
            var date = moment(data[2], 'DD/MM/YYYY');

            return (date.isSameOrAfter(startDate) && date.isSameOrBefore(endDate));
        }
    );

    // Initialize DataTable
    const table = $('#commissionsTable').DataTable({
        dom: "<'row'<'col-sm-12 col-md-6'l><'col-sm-12 col-md-6'f>>" +
             "<'row'<'col-sm-12'tr>>" +
             "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
        buttons: [
            {
                extend: 'excel',
                text: '<i class="ri-file-excel-line"></i> Export Excel',
                className: 'btn btn-success btn-sm',
                title: 'Commission Report ' + moment().format('DD-MM-YYYY'),
                exportOptions: {
                    columns: [0,1,2,3,4,5,6,7,8]
                }
            }
        ],
        responsive: true,
        order: [[2, 'desc']],
        pageLength: 25,
        lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
        footerCallback: function(row, data, start, end, display) {
            const api = this.api();
            
            // Calculate totals
            const totalCommission = api
                .column(6, {search: 'applied'})
                .data()
                .reduce((a, b) => a + parseFloat(b.replace(/[^\d.-]/g, '')), 0);

            const totalGross = api
                .column(3, {search: 'applied'})
                .data()
                .reduce((a, b) => a + parseFloat(b.replace(/[^\d.-]/g, '')), 0);

            // Add footer row if it doesn't exist
            if ($(api.table().footer()).length === 0) {
                const footer = $('<tfoot>').appendTo($(api.table()));
                const footerRow = $('<tr>').appendTo(footer);
                $('<th colspan="3">').text('Totals:').appendTo(footerRow);
                $('<th class="text-end">').appendTo(footerRow);
                $('<th>').appendTo(footerRow);
                $('<th>').appendTo(footerRow);
                $('<th class="text-end">').appendTo(footerRow);
                $('<th colspan="2">').appendTo(footerRow);
            }

            // Update footer values
            $(api.column(3).footer()).html(formatCurrency(totalGross));
            $(api.column(6).footer()).html(formatCurrency(totalCommission));
        }
    });

    // Apply filters
    $('#corporateFilter').on('change', function() {
        table.column(0).search(this.value).draw();
    });

    $('#dateRange').on('apply.daterangepicker', function(ev, picker) {
        table.draw();
    });

    // Format currency helper
    function formatCurrency(amount) {
        return new Intl.NumberFormat('en-KE', {
            style: 'currency',
            currency: 'KES',
            minimumFractionDigits: 2
        }).format(amount);
    }

    // Export function
    window.exportToExcel = function() {
        table.button('.buttons-excel').trigger();
    };
});
</script>
</body>
</html>