<?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';

// Extract filter parameters for the SQL query
$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'>Access failed. Contact Sales and Marketing.</div>");
}

// Build Filter Query
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 needed following this pattern)
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Primary-Target 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=Inter:wght@400;600&display=swap" rel="stylesheet">
    
    <style>
        body { font-family: 'Inter', sans-serif; background-color: #f8f9fa; color: #333; }
        .card { border: none; box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075); }
        .table-container { background: white; border-radius: 8px; padding: 15px; }
        .sticky-header th { position: sticky; top: 0; background: #006699 !important; color: white; }
        @media (max-width: 768px) {
            .btn-mobile-full { width: 100%; margin-bottom: 10px; }
            .header-title { font-size: 1.2rem; }
        }
    </style>
</head>
<body>

<nav class="navbar navbar-dark bg-primary mb-4">
    <div class="container-fluid">
        <span class="navbar-brand mb-0 h1">Primary-Target Sheet</span>
    </div>
</nav>

<div class="container-fluid">
    <div class="card mb-4">
        <div class="card-body">
            <form method="POST" class="row g-3">
                <div class="col-6 col-md-3">
                    <label class="form-label small fw-bold">From</label>
                    <input type="date" name="starts" class="form-control" value="<?php echo $starts; ?>">
                </div>
                <div class="col-6 col-md-3">
                    <label class="form-label small fw-bold">To</label>
                    <input type="date" name="ends" class="form-control" value="<?php echo $ends; ?>">
                </div>
                <div class="col-12 col-md-4">
                    <label class="form-label small fw-bold">Search Data</label>
                    <input type="text" id="srctxt" class="form-control" placeholder="Search Area, Region, or Distributor..." onkeyup="filterTable()">
                </div>
                <div class="col-12 col-md-2 d-flex align-items-end">
                    <button type="submit" class="btn btn-primary w-100">Apply Filter</button>
                </div>
            </form>
        </div>
    </div>

    <div class="d-flex flex-wrap gap-2 mb-3">
        <button onclick="exportToExcel()" class="btn btn-success btn-sm">
            <i class="bi bi-file-earmark-excel"></i> Download Excel
        </button>
        <button onclick="copyTable()" class="btn btn-outline-secondary btn-sm">Copy Table Data</button>
    </div>

    <div class="table-container shadow-sm">
        <h5 class="text-primary mb-3 header-title"><?php echo $agents; ?> Primary-Progress Data</h5>
        <div class="table-responsive" style="max-height: 70vh;">
            <table id="targetTable" class="table table-hover table-sm align-middle">
                <thead class="sticky-header">
                    <tr>
                        <th>Date</th>
                        <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>Product</th>
                        <th class="text-end">T-Box</th>
                        <th class="text-end">T-Ton</th>
                        <th class="text-end">T-Value</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $sqlst = "SELECT dates, agent_names, regions, zones, item_names, 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, asm_names, executive_names, rsm_names, itemcode, sub_ranges, range_types 
                              FROM rc_targets ".$flt." 
                              AND chanell='GENERAL TRADE' 
                              AND dates>='".$starts."' AND dates<='".$ends."' 
                              GROUP BY item_names, regions, agent_names, zones, dates";

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

                    while($rst = mysqli_fetch_row($qry)){
                        $total_val += $rst[6];
                        echo "<tr>
                                <td>$rst[0]</td>
                                <td>$rst[2]</td>
                                <td>$rst[3]</td>
                                <td>$rst[11]</td>
                                <td>$rst[9]</td>
                                <td>$rst[10]</td>
                                <td>$rst[1]</td>
                                <td><span class='badge bg-light text-dark'>$rst[14]</span></td>
                                <td>$rst[8]</td>
                                <td>$rst[13]</td>
                                <td><small>$rst[4]</small></td>
                                <td class='text-end'>".number_format($rst[5], 0)."</td>
                                <td class='text-end'>".number_format($rst[7], 3)."</td>
                                <td class='text-end fw-bold'>".number_format($rst[6], 2)."</td>
                              </tr>";
                    }
                    ?>
                </tbody>
                <tfoot class="table-secondary fw-bold">
                    <tr>
                        <td colspan="13">Grand Total</td>
                        <td class="text-end"><?php echo number_format($total_val, 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>
// Filter table rows based on search input
function filterTable() {
    let input = document.getElementById("srctxt").value.toLowerCase();
    let rows = document.querySelectorAll("#targetTable tbody tr");
    
    rows.forEach(row => {
        let text = row.innerText.toLowerCase();
        row.style.display = text.includes(input) ? "" : "none";
    });
}

// Export Table to Excel
function exportToExcel() {
    let table = document.getElementById("targetTable");
    let wb = XLSX.utils.table_to_book(table, {sheet: "Targets"});
    XLSX.writeFile(wb, "Primary_Target_Sheet.xlsx");
}

// Copy to Clipboard
function copyTable() {
    let range = document.createRange();
    range.selectNode(document.getElementById("targetTable"));
    window.getSelection().removeAllRanges();
    window.getSelection().addRange(range);
    document.execCommand("copy");
    window.getSelection().removeAllRanges();
    alert("Table data copied to clipboard!");
}
</script>

</body>
</html>