<?php
ob_start();
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
include('includes/db.php');

// ---------------- Access Control ----------------
if (!isset($_SESSION['user_role'])) {
    $_SESSION['flash'] = ["message" => "Please log in first!", "color" => "#dc3545"];
    header("Location: login.php");
    exit;
}

$username    = $_SESSION['username'] ?? null;
$facility_id = $_SESSION['facility_id'] ?? null;
$facility    = $_SESSION['facility_name'] ?? null;

// Facility must exist
if (!$facility_id || !$facility) {
    $_SESSION['flash'] = ["message" => "No facility assigned to your account.", "color" => "#dc3545"];
    header("Location: dashboard.php");
    exit;
}

// ---------------- Date Filters ----------------
$fromDate = $_GET['from_date'] ?? date('Y-m-01');
$toDate   = $_GET['to_date'] ?? date('Y-m-t');

// ---------------- Fetch Visits ----------------
$dailyInsuranceReport = $pdo->prepare("
    SELECT 
        v.id AS visit_id,
        DATE(v.visit_date) AS day,
        CONCAT(p.first_name,' ',COALESCE(p.middle_name,''),' ',p.last_name) AS patient_name,
        p.d_o_b,
        v.voucher_id,
        v.insurance_card_number,
        p.gender,
        v.affiliate_name,
        v.beneficiary_relationship,
        v.affiliate_affectation,
        ip.name AS insurance_name,
        SUM(COALESCE(inv.patient_co_pay,0)) AS patient_copay,
        SUM(COALESCE(inv.insurance_amount,0)) AS insurance_amount
    FROM visits v
    INNER JOIN patients p ON v.patient_id = p.id
    LEFT JOIN invoices inv ON inv.visit_id = v.id AND inv.is_removed=0
    LEFT JOIN insurance_partners ip ON v.insurance_id = ip.id
    WHERE DATE(v.visit_date) BETWEEN ? AND ?
      AND p.facility_id = ?
    GROUP BY v.id
    ORDER BY v.visit_date DESC
");
$dailyInsuranceReport->execute([$fromDate, $toDate, $facility_id]);
$dailyInsuranceData = $dailyInsuranceReport->fetchAll(PDO::FETCH_ASSOC);

// ---------------- Fetch Products per Visit ----------------
$visitIds = array_column($dailyInsuranceData, 'visit_id');
$categoryColumns = [
    'consultation_followup',
    'lab_test',
    'medical_imaging',
    'hospitalization',
    'other_medical_act',
    'consumable',
    'drug'
];
$productsPerVisit = [];

if (!empty($visitIds)) {
    $inQuery = str_repeat('?,', count($visitIds)-1) . '?';
    $productStmt = $pdo->prepare("
        SELECT 
            inv.visit_id, 
            pr.name AS product_name, 
            pr.category_type, 
            inv.amount AS total_amount,  -- Use full amount from invoice
            inv.patient_co_pay,
            inv.insurance_amount
        FROM invoices inv
        INNER JOIN products pr ON pr.id = inv.product_id
        WHERE inv.visit_id IN ($inQuery)
          AND inv.is_removed=0
          AND pr.facility_id = ?
        ORDER BY inv.visit_id, pr.category_type
    ");
    $executeParams = array_merge($visitIds, [$facility_id]);
    $productStmt->execute($executeParams);
    $allProducts = $productStmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($allProducts as $p) {
        $visitId = $p['visit_id'];
        $categoryType = $p['category_type'];
        
        // For imaging category, use FULL AMOUNT (not patient_co_pay or insurance_amount)
        if ($categoryType === 'imaging') {
            $amount = floatval($p['total_amount']);
            $catKey = 'medical_imaging';
        } else {
            // For other categories, use insurance_amount (85% part)
            $amount = floatval($p['insurance_amount']);
            
            // Map product to the proper category column
            switch ($categoryType) {
                case 'medical_act':
                    if (stripos($p['product_name'],'Consultation') !== false || stripos($p['product_name'],'Follow-up') !== false) {
                        $catKey = 'consultation_followup';
                    } else {
                        $catKey = 'other_medical_act';
                    }
                    break;
                case 'hospitalization':
                    $catKey = 'hospitalization';
                    break;
                case 'imaging':
                    $catKey = 'medical_imaging';
                    break;
                case 'lab_test':
                    $catKey = 'lab_test';
                    break;
                case 'drug':
                    $catKey = 'drug';
                    break;
                case 'consumable':
                    $catKey = 'consumable';
                    break;
                default:
                    $catKey = 'other_medical_act';
            }
        }

        // Store product info
        if (!isset($productsPerVisit[$visitId][$catKey])) {
            $productsPerVisit[$visitId][$catKey] = [];
        }
        
        $productsPerVisit[$visitId][$catKey][] = [
            'name'     => $p['product_name'],
            'amount'   => $amount,
            'full_amount' => floatval($p['total_amount']),  // Keep full amount for reference
            'patient_co_pay' => floatval($p['patient_co_pay']),
            'insurance_amount' => floatval($p['insurance_amount'])
        ];
    }
}
?>

<?php include('includes/header.php'); ?>
<nav class="header-navbar navbar navbar-fixed-top navbar-semi-dark navbar-shadow">
    <?php include('includes/navbar.php'); ?>
</nav>
<div class="main-menu menu-fixed menu-dark menu-accordion menu-shadow">
    <?php include('includes/sidebar.php'); ?>
</div>

<div class="app-content content container-fluid">
    <div class="content-wrapper">

        <div class="content-header row">
            <div class="col-md-12">
                <h3 class="content-header-title"> Insurance Report</h3>
            </div>
        </div>

        <div class="content-body">

            <div class="card mb-3">
                <div class="card-body">
                    <form method="get" class="form-inline">
                        <div class="form-group mr-2">
                            <label for="from_date" class="mr-1">From:</label>
                            <input type="date" name="from_date" class="form-control" value="<?= htmlspecialchars($fromDate) ?>">
                        </div>
                        <div class="form-group mr-2">
                            <label for="to_date" class="mr-1">To:</label>
                            <input type="date" name="to_date" class="form-control" value="<?= htmlspecialchars($toDate) ?>">
                        </div>
                        <button type="submit" class="btn btn-primary">Filter</button>
                    </form>
                </div>
            </div>

            <div class="card mt-3">
                <div class="card-body">
                    <div class="table-responsive">
                        <table id="dailyInsuranceTable" class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>No.</th>
                                    <th>DATE</th>
                                    <th>INSURANCE</th>
                                    <th>Voucher ID</th>
                                    <th>BENEFICIARY'S AFFILIATION NUMBER</th>
                                    <th>BENEFICIARY'S AGE</th>
                                    <th>BENEFICIARY'S SEX</th>
                                    <th>BENEFICIARY'S NAMES</th>
                                    <th>AFFILIATE'S NAMES</th>
                                    <th>AFFILIATE AFFECTATION</th>
                                    <th>BENEFICIARY RELATIONSHIP</th>
                                    <th>COST FOR CONSULTATION</th>
                                    <th>COST FOR LABORATORY TESTS</th>
                                    <th>COST FOR MEDICAL IMAGING 100%</th>
                                    <th>COST FOR HOSPITALIZATION 100%</th>
                                    <th>COST FOR PROCEDURES AND MATERIALS</th>
                                    <th>COST FOR OTHER CONSUMABLES</th>
                                    <th>COST FOR MEDICINES</th>
                                    <th>TOTAL AMOUNT 100%</th>
                                    <th>PATIENT CO-PAY 15%</th>
                                    <th>TOTAL AMOUNT 85%</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $totals = array_fill_keys($categoryColumns, 0);
                                $totalPatientCopay = 0;
                                $totalInsuranceAmount = 0;
                                $totalFullAmount = 0;
                                $rowNumber = 1; // Start numbering from 1
                                ?>
                                <?php foreach($dailyInsuranceData as $row): ?>
                                    <?php 
                                    $visitId = $row['visit_id'];
                                    $visitTotal = $row['patient_copay'] + $row['insurance_amount'];
                                    $totalPatientCopay += $row['patient_copay'];
                                    $totalInsuranceAmount += $row['insurance_amount'];
                                    $totalFullAmount += $visitTotal;
                                    ?>
                                    <tr>
                                        <td data-order="<?= $rowNumber ?>"><?= $rowNumber++ ?></td>
                                        <td><?= $row['day'] ?></td>
                                        <td><?= htmlspecialchars($row['insurance_name'] ?? '-') ?></td>
                                        <td><?= htmlspecialchars($row['voucher_id'] ?: '-') ?></td>
                                        <td><?= htmlspecialchars($row['insurance_card_number'] ?: '-') ?></td>
                                        <td><?= date('d/M/Y', strtotime($row['d_o_b'])) ?></td>
                                        <td><?= htmlspecialchars($row['gender']) ?></td>
                                        <td><?= htmlspecialchars($row['patient_name']) ?></td>
                                        <td><?= htmlspecialchars($row['affiliate_name'] ?: '-') ?></td>
                                        <td><?= htmlspecialchars($row['affiliate_affectation'] ?: '-') ?></td>
                                        <td><?= htmlspecialchars($row['beneficiary_relationship'] ?: '-') ?></td>

                                        <?php
                                        // Calculate category totals for this visit
                                        $categoryTotals = [];
                                        foreach ($categoryColumns as $cat) {
                                            $categoryTotals[$cat] = 0;
                                            if (isset($productsPerVisit[$visitId][$cat])) {
                                                foreach ($productsPerVisit[$visitId][$cat] as $product) {
                                                    // For medical_imaging, use full_amount (100%)
                                                    if ($cat === 'medical_imaging') {
                                                        $categoryTotals[$cat] += $product['full_amount'];
                                                    } else {
                                                        // For other categories, use insurance_amount (85%)
                                                        $categoryTotals[$cat] += $product['insurance_amount'];
                                                    }
                                                }
                                            }
                                            $totals[$cat] += $categoryTotals[$cat];
                                        }
                                        ?>

                                        <?php foreach($categoryColumns as $cat): ?>
                                            <?php
                                            $catTotal = $categoryTotals[$cat];
                                            $formulaParts = [];
                                            if(isset($productsPerVisit[$visitId][$cat])) {
                                                foreach($productsPerVisit[$visitId][$cat] as $p) {
                                                    // For Excel formulas, use the appropriate amount
                                                    if ($cat === 'medical_imaging') {
                                                        $formulaParts[] = $p['full_amount'];
                                                    } else {
                                                        $formulaParts[] = $p['insurance_amount'];
                                                    }
                                                }
                                            }
                                            $formulaStr = implode(',', $formulaParts);
                                            ?>
                                            <?php if($cat == 'lab_test'): ?>
                                                <td class="lab-cell" data-formula="<?= $formulaStr ?>">
                                                    <?= number_format($catTotal, 2) ?>
                                                </td>
                                            <?php elseif($cat == 'medical_imaging'): ?>
                                                <td class="imaging-cell" data-formula="<?= $formulaStr ?>">
                                                    <?= number_format($catTotal, 2) ?></td>
                                            <?php else: ?>
                                                <td><?= number_format($catTotal, 2) ?></td>
                                            <?php endif; ?>
                                        <?php endforeach; ?>

                                        <td><?= number_format($visitTotal, 2) ?></td>
                                        <td><?= number_format($row['patient_copay'], 2) ?></td>
                                        <td><?= number_format($row['insurance_amount'], 2) ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                            <tfoot>
                                <tr>
                                    <th colspan="11" class="text-right">Totals:</th>
                                    <th><?= number_format($totals['consultation_followup'], 2) ?></th>
                                    <th><?= number_format($totals['lab_test'], 2) ?></th>
                                    <th><?= number_format($totals['medical_imaging'], 2) ?></th>
                                    <th><?= number_format($totals['hospitalization'], 2) ?></th>
                                    <th><?= number_format($totals['other_medical_act'], 2) ?></th>
                                    <th><?= number_format($totals['consumable'], 2) ?></th>
                                    <th><?= number_format($totals['drug'], 2) ?></th>
                                    <th><?= number_format($totalFullAmount, 2) ?></th>
                                    <th><?= number_format($totalPatientCopay, 2) ?></th>
                                    <th><?= number_format($totalInsuranceAmount, 2) ?></th>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>

        </div>
    </div>
</div>

<?php include('includes/footer.php'); ?>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.bootstrap5.min.css">

<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/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://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>

<script>
$(document).ready(function() {
    var table = $('#dailyInsuranceTable').DataTable({
        pageLength: 25,
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excelHtml5',
                title: 'Insurance Report',
                messageTop: 'Report Period: <?= $fromDate ?> to <?= $toDate ?>',
                exportOptions: {
                    columns: ':visible',
                    format: {
                        body: function(data, row, column, node) {
                            // For the numbering column (0), generate sequential numbers starting from 1
                            if (column === 0) {
                                // Simple sequential number based on row position
                                var dt = $('#dailyInsuranceTable').DataTable();
                                var rowIndex = dt.row(node.parentNode).index();
                                return rowIndex + 1;
                            }
                            return data;
                        }
                    }
                },
                customize: function(xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    
                    // Remove any existing customizations that might break the export
                    // Just let DataTables handle the basic export first
                }
            },
            {
                extend: 'pdfHtml5',
                title: 'Insurance Report',
                messageTop: 'Report Period: <?= $fromDate ?> to <?= $toDate ?>',
                exportOptions: {
                    columns: ':visible',
                    format: {
                        body: function(data, row, column, node) {
                            if (column === 0) {
                                var dt = $('#dailyInsuranceTable').DataTable();
                                var rowIndex = dt.row(node.parentNode).index();
                                return rowIndex + 1;
                            }
                            return data;
                        }
                    }
                }
            },
            {
                extend: 'print',
                title: 'Insurance Report',
                messageTop: 'Report Period: <?= $fromDate ?> to <?= $toDate ?>',
                exportOptions: {
                    columns: ':visible',
                    format: {
                        body: function(data, row, column, node) {
                            if (column === 0) {
                                var dt = $('#dailyInsuranceTable').DataTable();
                                var rowIndex = dt.row(node.parentNode).index();
                                return rowIndex + 1;
                            }
                            return data;
                        }
                    }
                }
            }
        ],
        order: [[1, 'desc']], // Sort by date descending by default
        columnDefs: [
            { 
                targets: 0, 
                orderable: false, 
                searchable: false,
                className: 'dt-body-right'
            }
        ],
        drawCallback: function(settings) {
            var api = this.api();
            var rows = api.rows({order: 'applied'}).nodes();
            var totalRows = api.rows({order: 'applied'}).count();
            
            // Update numbering based on current order (descending by date)
            api.column(0, {order: 'applied', page: 'current'}).nodes().each(function(cell, i) {
                var rowIndex = api.row($(cell).closest('tr')).index();
                var displayNumber = totalRows - rowIndex;
                cell.innerHTML = displayNumber;
            });
        },
        footerCallback: function (row, data, start, end, display) {
            var api = this.api();
            api.columns().every(function(index) {
                if (index >= 11) {
                    var total = api.column(index, {page: 'current'}).data().reduce(function(a, b) {
                        return parseFloat(a) + parseFloat(b.toString().replace(/,/g, ''));
                    }, 0);
                    $(api.column(index).footer()).html(
                        total.toLocaleString(undefined, {minimumFractionDigits: 2, maximumFractionDigits: 2})
                    );
                }
            });
        }
    });
});
</script>