<?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");

// GET PARAMETERS (Original Data Logic)
$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';
$chanell = $_REQUEST['chanell'] ?? 'all';

$flt = racs(1);
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."' "; }

$sprt = "&zones=".$zones."&regions=".$regions."&agents=".$agents."&chanell=".$chanell;

// SQL QUERY (Restored Original)
$sqlst = "select mranges,tgvl,prvl,odvl,rdvl,prvldir,prton,trton,odton,prdton,rddton from (select distinct sub_ranges as mranges from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct sub_ranges from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct sub_ranges from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct sub_ranges from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."') as ags 
left join (select sub_ranges,sum(box_qty*box_price) as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as trton from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by sub_ranges) as tgs on tgs.sub_ranges=mranges
left join (select sub_ranges,sum(box_qty*box_price) as prvl,sum(box_qty*packets_per_box*packet_weights/1000000) as prton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by sub_ranges) as acs on acs.sub_ranges=mranges
left join (select sub_ranges,sum(box_qty*box_price) as prvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as prdton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='DIRECT' AND zones<>'H-OFFICE' group by sub_ranges) as acsdir on acsdir.sub_ranges=mranges
left join (select sub_ranges,sum(pcs_qty*pcs_price) as rdvl,sum(pcs_qty*packet_weights/1000000) as rddton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by sub_ranges) as rdc on rdc.sub_ranges=mranges
left join (select sub_ranges,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000000) as odton from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by sub_ranges) as acss on acss.sub_ranges=mranges";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Diana Range Dashboard</title>
    
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

    <style>
        :root {
            --primary: #1e3a8a; /* Deep Navy */
            --accent: #3b82f6;  /* Bright Blue */
            --bg: #f8fafc;
            --success: #10b981;
        }

        body { font-family: 'Inter', 'Segoe UI', sans-serif; background: var(--bg); margin: 0; color: #334155; }
        
        /* Modern Navigation */
        .top-nav { background: var(--primary); color: white; display: flex; align-items: center; justify-content: space-between; padding: 0 40px; height: 60px; position: sticky; top: 0; z-index: 1000; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        .nav-brand { font-size: 20px; font-weight: bold; letter-spacing: 1px; }
        .nav-links { display: flex; gap: 25px; }
        .nav-links a { color: rgba(255,255,255,0.8); text-decoration: none; font-size: 14px; transition: 0.3s; font-weight: 500; }
        .nav-links a:hover { color: white; }
        .nav-links a.active { color: #facc15; } /* Yellow for active */

        .container { padding: 30px; max-width: 1600px; margin: auto; }

        /* KPI Cards & Charts */
        .dashboard-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(450px, 1fr)); gap: 25px; margin-bottom: 30px; }
        .card { background: white; border-radius: 12px; padding: 20px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); }
        
        /* Filters */
        .toolbar { background: white; border-radius: 12px; padding: 20px; margin-bottom: 25px; display: flex; flex-wrap: wrap; align-items: center; gap: 20px; border: 1px solid #e2e8f0; }
        .toolbar input, .toolbar button { padding: 10px; border: 1px solid #cbd5e1; border-radius: 6px; outline: none; }
        .btn-go { background: var(--accent); color: white; border: none !important; cursor: pointer; font-weight: bold; padding: 10px 25px !important; }
        .btn-excel { background: #15803d; color: white; border: none !important; margin-left: auto; cursor: pointer; font-weight: bold; }

        /* Data Table */
        .table-responsive { background: white; border-radius: 12px; overflow-x: auto; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); border: 1px solid #e2e8f0; }
        table { width: 100%; border-collapse: collapse; min-width: 1400px; }
        th { background: #f1f5f9; padding: 15px 10px; text-align: right; font-size: 11px; text-transform: uppercase; color: #475569; letter-spacing: 0.5px; border-bottom: 2px solid #e2e8f0; }
        td { padding: 12px 10px; border-bottom: 1px solid #f1f5f9; text-align: right; font-size: 13px; }
        td:first-child, th:first-child { text-align: left; font-weight: bold; position: sticky; left: 0; background: white; z-index: 10; }
        
        .pill { padding: 4px 10px; border-radius: 20px; font-weight: bold; font-size: 11px; }
        .pill-green { background: #dcfce7; color: #166534; }
        .pill-orange { background: #ffedd5; color: #9a3412; }
        
        .action-link { background: #eff6ff; color: #2563eb; padding: 5px 10px; border-radius: 6px; text-decoration: none; font-size: 11px; border: 1px solid #dbeafe; }
        .action-link:hover { background: #2563eb; color: white; }

        @media (max-width: 768px) {
            .dashboard-grid { grid-template-columns: 1fr; }
            .top-nav { padding: 0 15px; }
            .nav-links { display: none; } /* Mobile Menu would go here */
        }
    </style>
</head>
<body>

<nav class="top-nav">
    <div class="nav-brand">DIANA SALES</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="toolbar">
        <form method="post" action="subrangenewtm.php?actp=rpt<?=$sprt?>" style="display:flex; align-items:center; gap:10px;">
            <b>Performance Period:</b>
            <input type="date" name="starts" value="<?=$starts?>">
            <span>to</span>
            <input type="date" name="ends" value="<?=$ends?>">
            <button type="submit" class="btn-go">Refresh Data</button>
        </form>
        
        <input type="text" id="tableSearch" placeholder="🔍 Quick Search Range..." onkeyup="filterTable()" style="width: 250px;">
        
        <button onclick="exportExcel()" class="btn-excel">📊 Download Excel</button>
    </div>

    
    <div class="dashboard-grid">
        <div class="card"><canvas id="barChart" height="280"></canvas></div>
        <div class="card"><canvas id="doughnutChart" height="280"></canvas></div>
    </div>

    <div class="table-responsive">
        <table id="performanceTable">
            <thead>
                <tr>
                    <th>Range Category</th>
                    <th>Target Val</th>
                    <th>Pri Order</th>
                    <th>Pri Sale</th>
                    <th>Achievement %</th>
                    <th>RD Sale</th>
                    <th>RD %</th>
                    <th>Target (T)</th>
                    <th>Order (T)</th>
                    <th>RD (T)</th>
                    <th>Actual (T)</th>
                    <th>Tonnage %</th>
                    <th>Variance (T)</th>
                    <th>Direct Sale</th>
                    <th class="no-export">Action</th>
                </tr>
            </thead>
            <tbody>
            <?php
            $chartLabels = []; $chartTarget = []; $chartActual = [];
            $sumTargetVal=0; $sumSaleVal=0; $sumRDSale=0; $sumTargetTon=0; $sumActualTon=0; $sumOrderTon=0; $sumRDTon=0; $sumDirect=0;

            $qry = mysqli_query($conn, $sqlst);
            while($rst = mysqli_fetch_row($qry)){
                // Data Aggregation
                $sumTargetVal += $rst[1]; $sumSaleVal += $rst[2]; $sumRDSale += ($rst[4]+$rst[5]);
                $sumActualTon += $rst[6]; $sumTargetTon += $rst[7]; $sumOrderTon += $rst[8];
                $sumRDTon += ($rst[9]+$rst[10]); $sumDirect += $rst[5];

                $chartLabels[] = $rst[0]; $chartTarget[] = $rst[7]; $chartActual[] = $rst[6];

                $priPerc = ($rst[1] > 0) ? ($rst[2] * 100 / $rst[1]) : 0;
                $tonPerc = ($rst[7] > 0) ? ($rst[6] * 100 / $rst[7]) : 0;
                
                $tonClass = ($tonPerc >= 100) ? 'pill-green' : ($tonPerc >= 75 ? 'pill-orange' : '');

                echo "<tr>
                    <td>$rst[0]</td>
                    <td>".number_format(0,0)."</td>
                    <td>".number_format($rst[3],0)."</td>
                    <td>".number_format($rst[2],0)."</td>
                    <td><span class='pill $tonClass'>".number_format(0,1)."%</span></td>
                    <td>".number_format($rst[4]+$rst[5],0)."</td>
                    <td>".number_format((0>0 ? ((0)*100/0) : 0),1)."%</td>
                    <td>".number_format($rst[7],2)."</td>
                    <td>".number_format($rst[8],2)."</td>
                    <td>".number_format($rst[9]+$rst[10],2)."</td>
                    <td>".number_format($rst[6],2)."</td>
                    <td><span class='pill $tonClass'>".number_format($tonPerc,1)."%</span></td>
                    <td>".number_format($rst[7]-$rst[6],2)."</td>
                    <td>".number_format($rst[5],0)."</td>
                    <td class='no-export'>
                        <a href='rpmtgttitemwisetgsummary2tm.php?starts=$starts&ends=$ends&subranges=$rst[0]$sprt' target='_blank' class='action-link'>Items</a>
                    </td>
                </tr>";
            }
            ?>
            </tbody>
            <tr style="background:#f8fafc; font-weight:bold; border-top: 2px solid #e2e8f0;">
                <td>SYSTEM TOTAL</td>
                <td><?=number_format(0,0)?></td>
                <td>-</td>
                <td><?=number_format($sumSaleVal,0)?></td>
                <td><?=number_format((0>0 ? 0*100/0 : 0),1)?>%</td>
                <td><?=number_format($sumRDSale,0)?></td>
                <td>-</td>
                <td><?=number_format($sumTargetTon,2)?></td>
                <td><?=number_format($sumOrderTon,2)?></td>
                <td><?=number_format($sumRDTon,2)?></td>
                <td><?=number_format($sumActualTon,2)?></td>
                <td><?=number_format(($sumTargetTon>0 ? $sumActualTon*100/$sumTargetTon : 0),1)?>%</td>
                <td><?=number_format($sumTargetTon-$sumActualTon,2)?></td>
                <td><?=number_format($sumDirect,0)?></td>
                <td class="no-export"></td>
            </tr>
        </table>
    </div>
</div>

<script>
    // Search Functionality
    function filterTable() {
        const input = document.getElementById('tableSearch').value.toLowerCase();
        const rows = document.querySelectorAll('#performanceTable tbody tr');
        rows.forEach(row => {
            const text = row.cells[0].innerText.toLowerCase();
            row.style.display = text.includes(input) ? '' : 'none';
        });
    }

    // Excel Export
    function exportExcel() {
        const table = document.getElementById("performanceTable").cloneNode(true);
        table.querySelectorAll('.no-export').forEach(el => el.remove());
        const wb = XLSX.utils.table_to_book(table, {sheet: "Range Report"});
        XLSX.writeFile(wb, "Range_Performance_Report.xlsx");
    }

    // Bar Chart
    new Chart(document.getElementById('barChart'), {
        type: 'bar',
        data: {
            labels: <?=json_encode($chartLabels)?>,
            datasets: [
                { label: 'Target (Tons)', data: <?=json_encode($chartTarget)?>, backgroundColor: '#cbd5e1' },
                { label: 'Actual (Tons)', data: <?=json_encode($chartActual)?>, backgroundColor: '#3b82f6' }
            ]
        },
        options: { maintainAspectRatio: false, plugins: { title: { display: true, text: 'Tonnage Target vs Achievement' } } }
    });

    // Doughnut Chart
    new Chart(document.getElementById('doughnutChart'), {
        type: 'doughnut',
        data: {
            labels: <?=json_encode($chartLabels)?>,
            datasets: [{
                data: <?=json_encode($chartActual)?>,
                backgroundColor: ['#1e3a8a', '#3b82f6', '#60a5fa', '#93c5fd', '#bfdbfe', '#2563eb']
            }]
        },
        options: { maintainAspectRatio: false, plugins: { title: { display: true, text: 'Sales Mix (Tons)' } } }
    });
</script>

</body>
</html>