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

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

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

// 1. DATA FILTERS
$flt = racs(1);
$starts = $_REQUEST['starts'] ?? date('Y-m-01');
$ends   = $_REQUEST['ends']   ?? date('Y-m-t');
$zones  = $_REQUEST['zones']  ?? 'all';
$regions = $_REQUEST['regions'] ?? 'all';
$agents = $_REQUEST['agents'] ?? 'all';
$subranges = $_REQUEST['subranges'] ?? 'all';
$ranges = $_REQUEST['ranges'] ?? 'all';

if(strtolower($zones)!='all'){ $flt .= " and zones='".$zones."' "; }
if(strtolower($regions)!='all'){ $flt .= " and regions='".$regions."' "; }
if(strtolower($agents)!='all'){ $flt .= " and agent_names='".$agents."' "; }
if(strtolower($subranges)!='all'){ $flt .= " and sub_ranges='".$subranges."' "; }
if(strtolower($ranges)!='all'){ $flt .= " and item_ranges='".$ranges."' "; }

// 2. SQL QUERY (Sorted by Range)
$sqlst="select mranges,msubranges,mitems,tgvl,acvl,tgwt,acwt,odqt,odvl,rdqty,rdvl,pkbk,rdpcs,prdirect,prdirbox,prdirpcs,tton,prton,prtondi,rdton,prpcs,rnk,ipcode from (select distinct item_ranges as mranges,sub_ranges as msubranges,itemgroups as mitems from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' union select distinct item_ranges,sub_ranges,itemgroups from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct item_ranges,sub_ranges,itemgroups from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' union select distinct item_ranges,sub_ranges,itemgroups from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."') as ags
left join (select item_ranges,sub_ranges,itemgroups, sum(box_qty) as tgvl,sum(box_qty*box_price) as tgwt,sum(box_qty*packets_per_box*packet_weights/1000000) as tton from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by item_ranges,itemgroups) as tgs on tgs.item_ranges=mranges and tgs.sub_ranges=msubranges and tgs.itemgroups=mitems 
left join (select item_ranges,sub_ranges,itemgroups,sum(box_qty) as acvl, sum(box_qty*box_price) as acwt,sum(box_qty*packets_per_box*packet_weights/1000000) as prton,sum(box_qty * packets_per_box) as prpcs,rank_items as rnk,itemcodes as ipcode from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by item_ranges,itemgroups) as acs on acs.item_ranges=mranges and acs.sub_ranges=msubranges and acs.itemgroups=mitems
left join (select item_ranges,sub_ranges,itemgroups,sum(pcs_qty/packets_per_box) as rdqty, sum(pcs_qty*pcs_price) as rdvl,packets_per_box as pkbk,sum(pcs_qty) as rdpcs,sum(pcs_qty*packet_weights/1000000) as rdton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by item_ranges,itemgroups) as rdc on rdc.item_ranges=mranges and rdc.sub_ranges=msubranges and rdc.itemgroups=mitems
left join (select item_ranges,sub_ranges,itemgroups,sum(box_qty*box_price) as prdirect,sum(box_qty) as prdirbox,sum(box_qty*packets_per_box) as prdirpcs,sum(box_qty*packets_per_box*packet_weights/1000000) as prtondi from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' and agent_types='DIRECT'  group by item_ranges,itemgroups) as acdir on acdir.item_ranges=mranges and acdir.sub_ranges=msubranges and acdir.itemgroups=mitems
left join (select item_ranges,sub_ranges,itemgroups,sum(box_qty) as odqt, sum(box_qty*box_price) as odvl from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' and order_status<>'CANCELLED' AND zones<>'H-OFFICE' group by item_ranges,itemgroups) as acss on acss.item_ranges=mranges and acss.sub_ranges=msubranges and acss.itemgroups=mitems 
order by mranges ASC, msubranges DESC";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0">
    <title>Diana Item Analytics</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <style>
        :root { --navy: #1e3a8a; --blue: #3b82f6; --success: #22c55e; --warning: #f59e0b; --danger: #ef4444; --light: #f1f5f9; }
        body { font-family: 'Inter', sans-serif; background: var(--light); margin: 0; color: #1e293b; }
        
        .navbar { background: var(--navy); color: white; padding: 15px 25px; display: flex; align-items: center; justify-content: space-between; position: sticky; top: 0; z-index: 2000; }
        .nav-links a { color: white; text-decoration: none; margin-left: 15px; font-size: 13px; }

        .container { padding: 15px; max-width: 1600px; margin: auto; }
        
        /* Analytics Cards */
        .analytics-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); gap: 15px; margin-bottom: 20px; }
        .card { background: white; border-radius: 10px; padding: 15px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.05); }
        .card h4 { margin: 0 0 10px; font-size: 13px; border-bottom: 1px solid #eee; padding-bottom: 5px; }
        .list-item { display: flex; justify-content: space-between; padding: 5px 0; border-bottom: 1px solid #f8fafc; font-size: 12px; }

        /* Table & Freeze Pane */
        .table-responsive { background: white; border-radius: 10px; overflow-x: auto; position: relative; max-height: 600px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); }
        table { width: 100%; border-collapse: separate; border-spacing: 0; min-width: 1600px; }
        th { background: #f8fafc; padding: 12px 10px; border-bottom: 2px solid #e2e8f0; border-right: 1px solid #f1f5f9; position: sticky; top: 0; z-index: 100; font-size: 11px; text-transform: uppercase; }
        td { padding: 10px; border-bottom: 1px solid #f1f5f9; text-align: right; font-size: 12px; }

        /* Sticky Columns */
        td:nth-child(1), th:nth-child(1) { position: sticky; left: 0; background: #fff; z-index: 50; text-align: left; width: 100px; }
        td:nth-child(2), th:nth-child(2) { position: sticky; left: 100px; background: #fff; z-index: 50; text-align: left; border-right: 2px solid #cbd5e1; width: 180px; }
        th:nth-child(1), th:nth-child(2) { z-index: 150; background: #f8fafc; }

        .pill { padding: 2px 8px; border-radius: 12px; font-weight: bold; font-size: 10px; }
        .pill-green { background: #dcfce7; color: #166534; }
        .pill-red { background: #fee2e2; color: #991b1b; }
        
        .btn-blue { background: var(--blue); color: white; border: none; padding: 8px 15px; border-radius: 5px; cursor: pointer; }
        .btn-excel { background: var(--success); color: white; border: none; padding: 8px 15px; border-radius: 5px; cursor: pointer; font-weight: bold; }
    </style>
</head>
<body>

<nav class="navbar">
    <div style="font-weight:bold;">DIANA SALES | SYSTEM</div>
    <div class="nav-links"><a href="vsregionalperfomanceteam.php">Home</a><a href="dashboard.php">Dashboard</a><a href="rpmpragentinvoicestn.php">Invoice</a><a href="index.php">Logout</a></div>
</nav>

<div class="container">
    <div class="card" style="margin-bottom: 15px; display: flex; justify-content: space-between; align-items: flex-end; flex-wrap: wrap; gap: 10px;">
        <form method="post" action="" style="display:flex; gap:10px; align-items:flex-end;">
            <div><label style="font-size:11px; display:block;">FROM</label><input type="date" name="starts" value="<?=$starts?>"></div>
            <div><label style="font-size:11px; display:block;">TO</label><input type="date" name="ends" value="<?=$ends?>"></div>
            <button type="submit" class="btn-blue">Filter</button>
        </form>
        <button onclick="exportExcel()" class="btn-excel">📊 Download Excel</button>
    </div>

    <?php 
    $dataRows = []; $sumTton = 0; $sumPrton = 0; $sumRdton = 0; $sumTval = 0; $sumAval = 0; $sumRdval = 0;
    $qry = mysqli_query($conn, $sqlst);
    while($rst = mysqli_fetch_row($qry)){
        $ach = ($rst[16]>0)?($rst[17]*100/$rst[16]):0;
        $rst['ach'] = $ach;
        $dataRows[] = $rst;
        // Totals
        $sumTton += $rst[16]; $sumPrton += $rst[17]; $sumRdton += ($rst[18]+$rst[19]);
        $sumTval += $rst[5];  $sumAval += $rst[6];  $sumRdval += ($rst[10]+$rst[13]);
    }
    // Sort for Top/Least 10
    usort($dataRows, function($a, $b) { return $b['ach'] <=> $a['ach']; });
    $top10 = array_slice($dataRows, 0, 10);
    $least10 = array_reverse(array_slice($dataRows, -10));
    ?>

    <div class="analytics-grid">
        <div class="card">
            <h4 style="color:var(--success);">▲ TOP 10 PERFORMERS</h4>
            <?php foreach($top10 as $t) echo "<div class='list-item'><span>{$t[2]}</span><b>".number_format($t['ach'],1)."%</b></div>"; ?>
        </div>
        <div class="card">
            <h4 style="color:var(--danger);">▼ LEAST 10 PERFORMERS</h4>
            <?php foreach($least10 as $l) echo "<div class='list-item'><span>{$l[2]}</span><b>".number_format($l['ach'],1)."%</b></div>"; ?>
        </div>
    </div>

    <div class="table-responsive">
        <table id="dt">
            <thead>
                <tr>
                    <th>Range</th><th>Item Name</th><th>Target Ton</th><th>Actual Ton</th><th>RD Ton</th>
                    <th>Ach %</th>
                    <th>Tar-Box</th><th>Act-Box</th><th>RD-Box</th><th>Diff Box</th><th>Tar Value</th><th>Act Value</th><th>RD Value</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($dataRows as $row): 
                    $pClass = ($row['ach']>=100)?'pill-green':(($row['ach']>=75)?'':'pill-red');
                ?>
                <tr>
                    <td><?=$row[0]?></td><td><?=$row[2]?></td>
                    <td><?=number_format($row[16],3)?></td>
                    <td><?=number_format($row[17],3)?></td>
                    <td><?=number_format($row[18]+$row[19],3)?></td>
                    <td><span class="pill <?=$pClass?>"><?=number_format($row['ach'],1)?>%</span></td>
                    <td><?=number_format($row[3],0)?></td>
                    <td><?=number_format($row[4],0)?></td>
                    <td><?=number_format($row[9]+$row[14],0)?></td>
                    <td style="color:<?=($row[3]-$row[4]>0?'var(--danger)':'var(--success)')?>"><?=number_format($row[3]-$row[4],0)?></td>
                    <td><?=number_format(0,0)?></td>
                    <td><?=number_format($row[6],0)?></td>
                    <td><?=number_format($row[10]+$row[13],0)?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tr style="background:#eaedf2; font-weight:bold; position:sticky; bottom:0;">
                <td colspan="2">TOTAL</td>
                <td><?=number_format($sumTton,3)?></td><td><?=number_format($sumPrton,3)?></td><td><?=number_format($sumRdton,3)?></td>
                <td><?=number_format(($sumTton>0?$sumPrton*100/$sumTton:0),1)?>%</td>
                <td colspan="4"></td>
                <td><?=number_format(0,0)?></td><td><?=number_format($sumAval,0)?></td><td><?=number_format($sumRdval,0)?></td>
                
            </tr>
        </table>
    </div>
</div>

<script>
    function exportExcel() {
        var wb = XLSX.utils.table_to_book(document.getElementById('dt'), {sheet: "Analysis"});
        XLSX.writeFile(wb, "Diana_Report_<?=$starts?>.xlsx");
    }
</script>
</body>
</html>