<?php
require "racs.php";
require "vtshowsBBB.php";
session_start();

if(!isset($_SESSION['titles']) || !isset($_SESSION['positions']) || !isset($_SESSION['usernames'])){
    header("location:index.php");
    exit;
}

// Database Connection
$conn = mysqli_connect("localhost", "primarysales", "S5L7lF01EQZvQrGK", "primarysales_diana");

// Initialize Filters
$starts = isset($_REQUEST['starts']) ? $_REQUEST['starts'] : date('Y-m-01');
$ends   = isset($_REQUEST['ends'])   ? $_REQUEST['ends']   : date('Y-m-t');
$agents = isset($_REQUEST['agents']) ? $_REQUEST['agents'] : 'all';

// Capturing other request variables
$zones      = $_REQUEST['zones'] ?? 'all';
$chanell    = $_REQUEST['chanell'] ?? 'all';
$regions    = $_REQUEST['regions'] ?? 'all';
$subregions = $_REQUEST['subregions'] ?? 'all';
$terrys     = $_REQUEST['terrys'] ?? 'all';
$items      = $_REQUEST['items'] ?? 'all';

$flt = racs(1);
if($flt == "access failed") {
    die("<div class='alert alert-danger m-3'>Access failed. Contact Sales & Marketing.</div>");
}

// Build Filter Query (Sanitized for basic safety)
if(strtolower($zones) != 'all')      $flt .= " and zones='".mysqli_real_escape_string($conn, $zones)."' ";
if(strtolower($regions) != 'all')    $flt .= " and regions='".mysqli_real_escape_string($conn, $regions)."' ";
if(strtolower($agents) != 'all')     $flt .= " and agent_names='".mysqli_real_escape_string($conn, $agents)."' ";
// ... add other filters as per your business logic
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Primary-Progress Sheet</title>
    
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Segoe+UI:wght@400;600&display=swap" rel="stylesheet">
    
    <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; background-color: #f4f7f9; }
        .navbar { background-color: #006699 !important; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .filter-card { border: none; border-radius: 10px; box-shadow: 0 4px 6px rgba(0,0,0,0.05); }
        .table-wrapper { background: white; border-radius: 10px; padding: 1rem; box-shadow: 0 4px 6px rgba(0,0,0,0.05); }
        .sticky-thead th { position: sticky; top: 0; background-color: #006699 !important; color: white; z-index: 10; }
        .btn-excel { background-color: #1D6F42; color: white; }
        .btn-excel:hover { background-color: #155231; color: white; }
        @media (max-width: 768px) {
            .table-responsive { font-size: 0.8rem; }
            .btn-group-mobile { width: 100%; display: flex; flex-direction: column; gap: 10px; }
        }
    </style>
</head>
<body>

<nav class="navbar navbar-dark mb-4">
    <div class="container-fluid">
        <span class="navbar-brand mb-0 h1">Primary-Progress Dashboard</span>
    </div>
</nav>

<div class="container-fluid">
    <div class="card filter-card mb-4">
        <div class="card-body">
            <form method="POST" class="row g-3">
                <div class="col-6 col-md-2">
                    <label class="form-label small fw-bold text-muted">Start Date</label>
                    <input type="date" name="starts" class="form-control" value="<?php echo $starts; ?>">
                </div>
                <div class="col-6 col-md-2">
                    <label class="form-label small fw-bold text-muted">End Date</label>
                    <input type="date" name="ends" class="form-control" value="<?php echo $ends; ?>">
                </div>
                <div class="col-12 col-md-5">
                    <label class="form-label small fw-bold text-muted">Live Search</label>
                    <input type="text" id="srctxt" class="form-control" placeholder="Search Area, RSM, or Product..." onkeyup="filterTable()">
                </div>
                <div class="col-12 col-md-3 d-flex align-items-end gap-2">
                    <button type="submit" class="btn btn-primary flex-grow-1">Update Results</button>
                </div>
            </form>
        </div>
    </div>

    <div class="d-flex flex-wrap gap-2 mb-3 btn-group-mobile">
        <button onclick="exportToExcel()" class="btn btn-excel btn-sm shadow-sm">
            Download Excel Report
        </button>
        <button onclick="copyToClipboard()" class="btn btn-outline-secondary btn-sm shadow-sm">
            Copy Data Table
        </button>
    </div>

    <div class="table-wrapper">
        <div class="d-flex justify-content-between align-items-center mb-3">
            <h6 class="text-primary mb-0 fw-bold"><?php echo $agents; ?> Primary-Progress Data</h6>
        </div>
        
        <div class="table-responsive" style="max-height: 65vh;">
            <table id="dataTable" class="table table-hover table-bordered table-sm">
                <thead class="sticky-thead">
                    <tr>
                        <th>Area</th>
                        <th>Region</th>
                        <th>RSM</th>
                        <th>ASM</th>
                        <th>ASE</th>
                        <th>Distributor</th>
                        <th>Type</th>
                        <th>Range</th>
                        <th>Sub</th>
                        <th>Code</th>
                        <th>Product</th>
                        <th class="text-end">Pcs</th>
                        <th class="text-end">Box</th>
                        <th class="text-end">Ton</th>
                        <th class="text-end">Value</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $sqlst = "SELECT dates, agent_names, regions, zones, item_names, 
                              SUM(box_qty)*packets_per_box as acvl, SUM(box_qty) as bxut, 
                              SUM(box_qty * box_price) as bxval, 
                              SUM(box_qty*packets_per_box*packet_weights/1000000) as bxton, 
                              item_ranges, itemcodes, asm_names, executive_names, rsm_names, 
                              sub_ranges, range_types 
                              FROM rc_invoices ".$flt." 
                              AND dates>='".$starts."' AND dates<='".$ends."' 
                              GROUP BY item_names, regions, agent_names, zones 
                              ORDER BY dates";

                    $qry = mysqli_query($conn, $sqlst);
                    $grand_total = 0;

                    while($rst = mysqli_fetch_row($qry)){
                        $grand_total += $rst[7]; // Totalling bxval
                        echo "<tr>
                                <td>$rst[2]</td>
                                <td>$rst[3]</td>
                                <td>$rst[13]</td>
                                <td>$rst[11]</td>
                                <td>$rst[12]</td>
                                <td>$rst[1]</td>
                                <td><span class='badge bg-light text-dark border'>$rst[15]</span></td>
                                <td>$rst[9]</td>
                                <td>$rst[14]</td>
                                <td>$rst[10]</td>
                                <td class='small'>$rst[4]</td>
                                <td class='text-end'>".number_format($rst[5],0)."</td>
                                <td class='text-end'>".number_format($rst[6],0)."</td>
                                <td class='text-end'>".number_format($rst[8],3)."</td>
                                <td class='text-end fw-bold'>".number_format($rst[7],2)."</td>
                              </tr>";
                    }
                    ?>
                </tbody>
                <tfoot class="table-dark">
                    <tr>
                        <td colspan="14" class="text-end fw-bold">Grand Total Value:</td>
                        <td class="text-end fw-bold"><?php echo number_format($grand_total, 2); ?></td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

<script>
// Search function: filters table rows instantly
function filterTable() {
    const query = document.getElementById('srctxt').value.toLowerCase();
    const rows = document.querySelectorAll('#dataTable tbody tr');
    
    rows.forEach(row => {
        const text = row.innerText.toLowerCase();
        row.style.display = text.includes(query) ? '' : 'none';
    });
}

// Excel Export function using SheetJS
function exportToExcel() {
    const table = document.getElementById("dataTable");
    const workbook = XLSX.utils.table_to_book(table, { sheet: "ProgressReport" });
    XLSX.writeFile(workbook, "Primary_Progress_Report.xlsx");
}

// Copy to Clipboard function
function copyToClipboard() {
    const table = document.getElementById("dataTable");
    const range = document.createRange();
    range.selectNode(table);
    window.getSelection().removeAllRanges();
    window.getSelection().addRange(range);
    try {
        document.execCommand('copy');
        alert('Data copied to clipboard!');
    } catch (err) {
        alert('Unable to copy');
    }
    window.getSelection().removeAllRanges();
}
</script>

</body>
</html>