<?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
$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. Please contact the Sales and Marketing division.</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)."' ";
// ... (Apply other filters as needed)
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>RD-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=Inter:wght@400;600&display=swap" rel="stylesheet">
    
    <style>
        body { font-family: 'Inter', sans-serif; background-color: #f0f2f5; color: #1a1a1a; }
        .navbar { background-color: #004d73 !important; }
        .card-filter { border: none; border-radius: 12px; box-shadow: 0 2px 10px rgba(0,0,0,0.08); }
        .data-container { background: white; border-radius: 12px; padding: 20px; box-shadow: 0 2px 10px rgba(0,0,0,0.08); }
        .sticky-header th { position: sticky; top: 0; background: #006699 !important; color: white; white-space: nowrap; font-size: 0.85rem; }
        .table { font-size: 0.85rem; }
        .btn-excel { background-color: #107c41; color: white; transition: 0.3s; }
        .btn-excel:hover { background-color: #0a5a2f; color: white; }
        
        @media (max-width: 768px) {
            .navbar-brand { font-size: 1rem; }
            .btn-action-mobile { width: 100%; margin-bottom: 5px; }
        }
    </style>
</head>
<body>

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

<div class="container-fluid">
    <div class="card card-filter 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">Date From</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">Date To</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">Instant Search</label>
                    <input type="text" id="srctxt" class="form-control" placeholder="Search by Region, Distributor, 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 w-100">Refresh Data</button>
                </div>
            </form>
        </div>
    </div>

    <div class="mb-3">
        <button onclick="exportToExcel()" class="btn btn-excel btn-sm btn-action-mobile shadow-sm">
            <b>⬇ Download Excel</b>
        </button>
        <button onclick="copyToClipboard()" class="btn btn-outline-dark btn-sm btn-action-mobile shadow-sm">
            Copy Table
        </button>
    </div>

    <div class="data-container">
        <h5 class="text-primary mb-3 fw-bold border-bottom pb-2">
            <?php echo $agents; ?> RD-Progress Analysis
        </h5>
        
        <div class="table-responsive" style="max-height: 70vh;">
            <table id="rdTable" class="table table-hover table-striped align-middle">
                <thead class="sticky-header text-center">
                    <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>RD-Pcs</th>
                        <th>RD-Box</th>
                        <th>RD-Ton</th>
                        <th>RD-Value</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $sqlst = "SELECT dates, agent_names, regions, zones, item_names, 
                              SUM(pcs_qty)/packets_per_box as acvl, SUM(pcs_qty) as bxut, 
                              SUM(pcs_qty * pcs_price) as bxval, 
                              SUM((pcs_qty*packet_weights)/1000000) as bxton, 
                              item_ranges, itemcodes, asm_names, executive_names, rsm_names, 
                              sub_ranges, range_types 
                              FROM rd_saleinv ".$flt." 
                              AND dates>='".$starts."' AND dates<='".$ends."' 
                              GROUP BY item_names, regions, agent_names, zones 
                              ORDER BY dates";

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

                    while($rst = mysqli_fetch_row($qry)){
                        $total_rd_val += $rst[7];
                        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>$rst[4]</td>
                                <td class='text-end'>".number_format($rst[6], 0)."</td>
                                <td class='text-end'>".number_format($rst[5], 2)."</td>
                                <td class='text-end'>".number_format($rst[8], 3)."</td>
                                <td class='text-end fw-bold'>".number_format($rst[7], 0)."</td>
                              </tr>";
                    }
                    ?>
                </tbody>
                <tfoot class="table-dark">
                    <tr>
                        <td colspan="14" class="text-end fw-bold">Grand Total RD Value:</td>
                        <td class="text-end fw-bold px-2"><?php echo number_format($total_rd_val, 0); ?></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 dynamically
function filterTable() {
    let input = document.getElementById("srctxt").value.toLowerCase();
    let rows = document.querySelectorAll("#rdTable tbody tr");
    
    rows.forEach(row => {
        let text = row.innerText.toLowerCase();
        row.style.display = text.includes(input) ? "" : "none";
    });
}

// Download Table as Excel
function exportToExcel() {
    const table = document.getElementById("rdTable");
    const wb = XLSX.utils.table_to_book(table, { sheet: "RD_Progress" });
    XLSX.writeFile(wb, "RD_Progress_Sheet.xlsx");
}

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

</body>
</html>