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

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

// 1. DATABASE CONNECTION
$conn = mysqli_connect("localhost", "primarysales", "S5L7lF01EQZvQrGK", "primarysales_diana");

// 2. ORIGINAL FILTER LOGIC
$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';
$chanell = $_REQUEST['chanell'] ?? '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."' "; }

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

// 3. ORIGINAL SQL QUERY (Restored)
$sqlst="select mzones,tgvl,acvl,odvl,rdvl,odvlc,pcls,rdavgday,wdays,acvldir,tton,prton,odton,penodton,penodval,rdton,rddir,tgvlrd,ttonrd,znn from (select distinct rgs as mzones from (select distinct zone_orders as rds,regions as rgs from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' union select distinct zone_orders,regions from rc_targets ".$flt."  and dates>='".$starts."' and dates<='".$ends."'and chanell='GENERAL TRADE' union select distinct zone_orders,regions from rd_targets ".$flt."  and dates>='".$starts."' and dates<='".$ends."'and chanell='GENERAL TRADE' union select distinct zone_orders,regions from rd_saleinv ".$flt."  and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' union select distinct zone_orders,regions from rc_orders ".$flt."  and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' order by rds) as zns) as ags 
left join (select regions,sum(box_qty*box_price) as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as tton,zones as znn from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by regions) as tgs on tgs.regions=mzones 
left join (select regions,sum(box_qty*box_price) as tgvlrd,sum(box_qty*packets_per_box*packet_weights/1000000) as ttonrd from rd_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by regions) as tgsrd on tgsrd.regions=mzones 
left join (select regions,sum(box_qty*box_price) as acvl,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 regions) as acs on acs.regions=mzones
left join (select regions,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 regions) as acss on acss.regions=mzones 
left join (select regions,sum(box_qty*box_price) as odvlc from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by regions) as acssod on acssod.regions=mzones 
left join (select regions,sum(box_qty*box_price) as penodval,sum(box_qty*packets_per_box*packet_weights/1000000) as penodton from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status='PENDING'and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by regions) as acssopd on acssopd.regions=mzones 
left join (select regions,sum(pcs_qty*pcs_price) as rdvl,sum(pcalls) as pcls,sum(pcs_qty*pcs_price)/count(distinct(invoice_number))*count(distinct(agent_names)) as rdavgday,count(distinct(dates)) as wdays,sum(pcs_qty*packet_weights/1000000) as rdton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE'  group by regions) as rds on rds.regions=mzones
left join (select regions,sum(box_qty*box_price) as acvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as rddir from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and agent_types='DIRECT' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by regions) as acsdr on acsdr.regions=mzones";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Diana Regional Performance</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 { --navy: #002b5c; --blue: #006699; --excel: #217346; --light: #f8fafc; }
        body { font-family: 'Segoe UI', Tahoma, sans-serif; background: var(--light); margin: 0; color: #333; font-size: 13px; }
        
        /* Modern Navbar */
        .navbar { background: var(--navy); color: white; height: 60px; display: flex; align-items: center; padding: 0 30px; position: sticky; top: 0; z-index: 1000; box-shadow: 0 2px 5px rgba(0,0,0,0.1); }
        .navbar .logo { font-weight: bold; font-size: 20px; }
        .nav-links { margin-left: auto; display: flex; gap: 20px; }
        .nav-links a { color: rgba(255,255,255,0.8); text-decoration: none; font-size: 14px; font-weight: 500; transition: 0.3s; }
        .nav-links a:hover { color: white; }

        .container { padding: 20px; }
        
        /* Chart Grid */
        .chart-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(400px, 1fr)); gap: 20px; margin-bottom: 25px; }
        .card { background: white; border-radius: 8px; padding: 20px; box-shadow: 0 1px 3px rgba(0,0,0,0.1); }

        /* Toolbar */
        .toolbar { background: white; padding: 15px; border-radius: 8px; margin-bottom: 20px; display: flex; align-items: center; justify-content: space-between; flex-wrap: wrap; gap: 15px; }
        .btn-excel { background: var(--excel); color: white; border: none; padding: 8px 15px; border-radius: 5px; cursor: pointer; font-weight: bold; }

        /* Responsive Table */
        .table-container { background: white; border-radius: 8px; overflow-x: auto; box-shadow: 0 1px 3px rgba(0,0,0,0.1); }
        table { width: 100%; border-collapse: collapse; min-width: 1600px; }
        th { background: #f1f5f9; padding: 12px 8px; border: 1px solid #e2e8f0; position: sticky; top: 0; z-index: 10; font-size: 11px; text-transform: uppercase; color: #64748b; }
        td { padding: 10px 8px; border-bottom: 1px solid #f1f5f9; text-align: right; }
        td:first-child { text-align: left; font-weight: bold; background: #fff; position: sticky; left: 0; z-index: 5; }
        tr:hover { background: #f8fafc; }
        
        .perc-pill { padding: 3px 8px; border-radius: 12px; font-weight: bold; font-size: 11px; }
        .green { background: #dcfce7; color: #166534; }
        .orange { background: #ffedd5; color: #9a3412; }
        .red { background: #fee2e2; color: #991b1b; }

        .view-btn { display: inline-block; padding: 3px 6px; border: 1px solid var(--blue); color: var(--blue); text-decoration: none; border-radius: 4px; font-size: 10px; }
        .view-btn:hover { background: var(--blue); color: white; }
        
        
/* Dropdown container */
.dropdown {
  position: relative;
  display: inline-block;
}

/* Dropdown menu */
.dropdown-content {
  display: none;
  position: absolute;
  background-color: var(--navy); /* match navbar */
  min-width: 160px;
  box-shadow: 0px 8px 16px rgba(0,0,0,0.2);
  z-index: 1;
}

/* Dropdown links */
.dropdown-content a {
  color: rgba(255,255,255,0.8); /* white text */
  padding: 10px 14px;
  text-decoration: none;
  display: block;
  font-size: 14px;
}

.dropdown-content a:hover {
  background-color: var(--blue); /* highlight on hover */
  color: white;
}

/* Show dropdown on hover */
.dropdown:hover .dropdown-content {
  display: block;
}

    </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</a>
        <a href="rpmpragentinvoicestn.php">Invoice</a>

        <!-- Dropdown for National -->
        <div class="dropdown">
            <a href="rpmpragentinvoicestn.php" class="dropbtn">Details</a>
            <div class="dropdown-content">
                <a href="pritargetsheetdata2.php">Target-Master</a>
                <a href="priprogressheetrddata2.php">Primary-Master</a>
                <a href="rpriprogressheetrddata2.php">RD-Master</a>
            </div>
        </div>
        
        <a href="index.php">Logout</a>
    </div>
</nav>

<div class="container">
    <div class="toolbar">
        <form method='post' action='vsregionalperfomanceteam.php?actp=rpt<?=$sprt?>' style="display:flex; gap:10px; align-items:center;">
            <b>Period:</b>
            <input type="date" name="starts" value="<?=$starts?>"> to 
            <input type="date" name="ends" value="<?=$ends?>">
            <button type="submit" style="background:var(--blue); color:white; border:none; padding:7px 15px; border-radius:4px; cursor:pointer;">Refresh</button>
        </form>
        <button onclick="exportExcel()" class="btn-excel">📊 Download Excel</button>
    </div>

    <div class="chart-grid">
        <div class="card"><canvas id="barChart" height="250"></canvas></div>
        <div class="card"><canvas id="pieChart" height="250"></canvas></div>
    </div>

    <div class="table-container">
        <table id="dt">
            <thead>
                <tr>
                    <th>Area</th><th>Tar-Prim-T</th><th>Primary-T</th><th>Pri%</th><th>RD-TT</th><th>RD-Ton</th><th>RD%</th>
                    <th>Pri-Target-V</th><th>Primary Sale</th><th>Pri% Val</th><th>Deficit</th>
                    <th>RD-Tar-Val</th><th>RD [DB+Dir]</th><th>RD% Val</th><th>DB-RD</th><th>Direct</th><th>Dton</th>
                    <th colspan="4" class="no-export">Detail Reports</th>
                </tr>
            </thead>
            <tbody>
            <?php
            $tottrton=0; $totprton=0; $rdton=0; $acv=0; $rdv=0; $dbrd=0; $ddrd=0; $dton=0; $tgv=0;
            $chartLabels = []; $chartTarget = []; $chartActual = [];

            $qry = mysqli_query($conn, $sqlst);
            while($rst = mysqli_fetch_row($qry)){
                $tottrton+=$rst[10]; $totprton+=$rst[11]; $rdton+=($rst[15]+$rst[16]); $rdtton+=$rst[18];
                $acv+=$rst[2]; $rdv+=($rst[4]+$rst[9]); $dbrd+=$rst[4]; $ddrd+=$rst[9]; $dton+=$rst[16]; $tgv+=$rst[1];

                $chartLabels[] = $rst[0]; $chartTarget[] = $rst[10]; $chartActual[] = $rst[11];

                $perc = ($rst[10] > 0) ? ($rst[11] / $rst[10] * 100) : 0;
                $percr = ($rst[18] > 0) ? (($rst[15]+$rst[16]) / $rst[10] * 100) : 0;
                $color = ($perc >= 100) ? 'green' : ($perc >= 75 ? 'orange' : 'red');
                $colorr = ($percr >= 100) ? 'green' : ($perc >= 75 ? 'orange' : 'red');

                echo "<tr>
                    <td>$rst[0]</td>
                    <td>".number_format($rst[10],2)."</td>
                    <td>".number_format($rst[11],2)."</td>
                    <td><span class='perc-pill $color'>".number_format($perc,1)."%</span></td>
                    <td>".number_format($rst[18],2)."</td>
                    <td>".number_format($rst[15]+$rst[16],2)."</td>
                    <td><span class='perc-pill $colorr'>".number_format($percr,1)."%</span></td>
                    <td>".number_format(0,0)."</td>
                    <td>".number_format($rst[2],0)."</td>
                    <td>".number_format(0,1)."</td>
                    <td>0</td>
                    <td>".number_format(0,0)."</td>
                    <td>".number_format($rst[4]+$rst[9],0)."</td>
                    <td>0</td>
                    <td>".number_format($rst[4],0)."</td>
                    <td>".number_format($rst[9],0)."</td>
                    <td>".number_format($rst[16],2)."</td>
                    <td class='no-export'><a href='rpmrdagenttgsummary2tm.php?starts=$starts&ends=$ends&regions=$rst[0]' class='view-btn'>Agent</a></td>
                    
                    <td class='no-export'><a href='rpmrangenewtm.php?starts=$starts&ends=$ends&regions=$rst[0]' class='view-btn'>Range</a></td>
                    
                    <td class='no-export'><a href='rpmtgttitemwisetgsummary2tm.php?starts=$starts&ends=$ends&regions=$rst[0]' class='view-btn'>Items</a></td>
                    
                    <td class='no-export'><a href='subrangenewtm.php?starts=$starts&ends=$ends&regions=$rst[0]' class='view-btn'>SubRange</a></td>

                </tr>";
            }
            ?>
            </tbody>
            <tfoot>
                <tr style="background:#eaedf2; font-weight:bold; border-top: 2px solid var(--navy);">
                    <td>SYSTEM TOTAL</td>
                    <td><?=number_format($tottrton,2)?></td>
                    <td><?=number_format($totprton,2)?></td>
                    <td><?=($tottrton>0)?number_format($totprton/$tottrton*100,1):0?>%</td>
                    <td><?=number_format($rdtton,2)?></td>
                    <td><?=number_format($rdton,2)?></td>
                    <td><?=($rdtton>0)?number_format($rdton/$rdtton*100,1):0?>%</td>
                    <td><?=number_format(0,0)?></td>
                    <td><?=number_format($acv,0)?></td>
                    <td></td><td>0</td>
                    <td>-</td>
                    <td><?=number_format($rdv,0)?></td>
                    <td><?=($tgv>0)?number_format(0):0?>%</td>
                    <td><?=number_format($dbrd,0)?></td>
                    <td><?=number_format($ddrd,0)?></td>
                    <td><?=number_format($dton,2)?></td>
                    <td class="no-export"><a href="rpmrdagenttgsummary2tm.php?starts=<?=$starts?>&ends=<?=$ends?>&regions=all&chanell=GENERAL TRADE" class="view-btn">Agents</a></td>
                    
                    <td class="no-export"><a href="rpmrangenewtm.php?starts=<?=$starts?>&ends=<?=$ends?>" class="view-btn">Range</a></td>
                    
                    <td class="no-export"><a href="rpmtgttitemwisetgsummary2tm.php?starts=<?=$starts?>&ends=<?=$ends?>&chanell=GENERAL TRADE" class="view-btn">Items</a></td>
                    
<td class="no-export"><a href="subrangenewtm.php?starts=<?=$starts?>&ends=<?=$ends?>" class="view-btn">SubRange</a></td>

                </tr>
            </tfoot>
        </table>
    </div>
</div>

<script>
    function exportExcel() {
        var table = document.getElementById("dt").cloneNode(true);
        table.querySelectorAll('.no-export').forEach(e => e.remove());
        var wb = XLSX.utils.table_to_book(table, {sheet: "Performance"});
        XLSX.writeFile(wb, "Regional_Performance_Report.xlsx");
    }

    new Chart(document.getElementById('barChart'), {
        type: 'bar',
        data: {
            labels: <?=json_encode($chartLabels)?>,
            datasets: [
                { label: 'Target Ton', data: <?=json_encode($chartTarget)?>, backgroundColor: '#cbd5e1' },
                { label: 'Actual Ton', data: <?=json_encode($chartActual)?>, backgroundColor: '#006699' }
            ]
        },
        options: { maintainAspectRatio: false }
    });

    new Chart(document.getElementById('pieChart'), {
        type: 'pie',
        data: {
            labels: <?=json_encode($chartLabels)?>,
            datasets: [{ data: <?=json_encode($chartActual)?>, backgroundColor: ['#003f5c','#2f4b7c','#665191','#a05195','#d45087','#f95d6a','#ff7c43'] }]
        },
        options: { maintainAspectRatio: false }
    });
</script>
</body>
</html>