<?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 Logic)
$starts = $_REQUEST['starts'] ?? date('Y-m-01');
$ends   = $_REQUEST['ends']   ?? date('Y-m-t');
$regions = $_REQUEST['regions'] ?? 'all';
$zones  = $_REQUEST['zones']  ?? 'all';
$agents = $_REQUEST['agents'] ?? 'all';

$flt=racs(1);
if(strtolower($regions)!='all'){ $flt.=" and regions='".$regions."' "; }
if(strtolower($zones)!='all'){ $flt.=" and zones='".$zones."' "; }
// ... (Your other existing filter concatenations)

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

// SQL QUERY (Original restored)
$sqlst="select magents,mterrys,tgvl,acvl,tgwt,acwt,odvl,odwt,emp,rdvl,rddisc,atwds,pcalls,dbcomi,acvldir,mcalls,rdton,tgwtrd,tgvlrd,rggn,rdtondr from 
(select distinct agent_names as magents,territory_names as mterrys,executive_names as emp from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."'
union select distinct agent_names,territory_names,executive_names from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' 
union select distinct agent_names,territory_names,executive_names from rd_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' 
union select distinct agent_names,territory_names,executive_names from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' 
union select distinct agent_names,territory_names,executive_names from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."') as ags 
left join(select agent_name,territory_name,region as rggn from agentsmrd group by agent_name,territory_name) as agnt on agnt.agent_name=magents and agnt.territory_name=mterrys
left join(select agent_names,territory_names,sum(box_qty*box_price) as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as tgwt from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names,territory_names) as tgs on tgs.agent_names=magents and tgs.territory_names=mterrys
left join(select agent_names,territory_names,sum(box_qty*box_price) as tgvlrd,sum(box_qty*packets_per_box*packet_weights/1000000) as tgwtrd from rd_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names,territory_names) as tgsrd on tgsrd.agent_names=magents and tgsrd.territory_names=mterrys
left join (select agent_names,territory_names,sum(box_qty*box_price) as acvl,sum(box_qty*packets_per_box*packet_weights/1000000) as acwt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names) as acs on acs.agent_names=magents and acs.territory_names=mterrys
left join (select agent_names,territory_names,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000000) as odwt from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' group by agent_names) as ocs on ocs.agent_names=magents and ocs.territory_names=mterrys
left join (select agent_names,territory_names,sum(pcs_qty*pcs_price) as rdvl,sum(rtdiscount_values+wsdiscount_values) as rddisc,count(distinct(dates)) as atwds,sum(pcalls) as pcalls,sum((pcs_qty*pcs_price)/100*8) as dbcomi,sum(mcalls) as mcalls,sum(pcs_qty*packet_weights/1000000)as rdton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names) as rds on rds.agent_names=magents and rds.territory_names=mterrys
left join (select agent_names,territory_names,sum(box_qty*box_price) as acvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as rdtondr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='DIRECT' group by agent_names) as acsdir on acsdir.agent_names=magents and acsdir.territory_names=mterrys";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Agent Achievement | Diana Sales</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: #0056b3; --light: #f8fafc; --success: #22c55e; --warning: #f59e0b; }
        body { font-family: 'Inter', Calibri, sans-serif; background: var(--light); margin: 0; color: #1e293b; }
        
        /* Top Navigation */
        .top-nav { background: var(--navy); color: white; padding: 0 30px; height: 60px; display: flex; align-items: center; justify-content: space-between; position: sticky; top: 0; z-index: 1000; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
        .nav-links { 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: 25px; max-width: 1600px; margin: auto; }
        
        /* Dashboard KPI Cards */
        .kpi-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin-bottom: 25px; }
        .kpi-card { background: white; padding: 20px; border-radius: 12px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); border-left: 5px solid var(--blue); }
        .kpi-card h3 { margin: 0; font-size: 12px; color: #64748b; text-transform: uppercase; }
        .kpi-card p { margin: 5px 0 0; font-size: 20px; font-weight: bold; color: var(--navy); }

        /* Chart Section */
        .chart-row { display: grid; grid-template-columns: 2fr 1fr; gap: 20px; margin-bottom: 25px; }
        .chart-card { background: white; padding: 20px; border-radius: 12px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); height: 300px; }

        /* Table Area */
        .toolbar { background: white; padding: 15px 25px; border-radius: 12px; margin-bottom: 20px; display: flex; align-items: center; justify-content: space-between; flex-wrap: wrap; gap: 15px; }
        .table-container { background: white; border-radius: 12px; overflow-x: auto; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); }
        table { width: 100%; border-collapse: collapse; font-size: 12px; min-width: 1500px; }
        th { background: #f1f5f9; padding: 12px 10px; text-align: left; color: #475569; font-weight: 600; border-bottom: 2px solid #e2e8f0; position: sticky; top: 0; }
        td { padding: 10px; border-bottom: 1px solid #f1f5f9; }
        .num { text-align: right; font-family: 'JetBrains Mono', monospace; }
        
        .pill { padding: 4px 10px; border-radius: 20px; font-weight: bold; font-size: 10px; }
        .pill-green { background: #dcfce7; color: #166534; }
        .pill-orange { background: #ffedd5; color: #9a3412; }
        
        .action-btn { text-decoration: none; color: var(--blue); border: 1px solid #e2e8f0; padding: 4px 8px; border-radius: 6px; font-size: 11px; }
        .action-btn:hover { background: var(--blue); color: white; }
    </style>
</head>
<body>

<nav class="top-nav">
    <div style="font-weight:bold; font-size:18px;">DIANA SALES | AGENT ACHIEVEMENTS</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="rpmrdagenttgsummary2tm.php?actp=rpt<?=$sprt?>" style="display:flex; gap:10px; align-items:center;">
            <input type="date" name="starts" value="<?=$starts?>" style="padding:8px; border-radius:6px; border:1px solid #ddd;">
            <span>to</span>
            <input type="date" name="ends" value="<?=$ends?>" style="padding:8px; border-radius:6px; border:1px solid #ddd;">
            <select name="regions" style="padding:8px; border-radius:6px; border:1px solid #ddd;">
                <option><?=$regions?></option><option>All</option>
                <?php 
                $q_reg = mysqli_query($conn, "SELECT DISTINCT regions FROM rc_targets");
                while($r = mysqli_fetch_row($q_reg)) echo "<option>$r[0]</option>"; 
                ?>
            </select>
            <button type="submit" style="background:var(--blue); color:white; border:none; padding:8px 20px; border-radius:6px; cursor:pointer;">Filter</button>
        </form>
        <div style="display:flex; gap:10px;">
            <input type="text" id="srctxt" placeholder="Search Agent/Area..." onkeyup="srgs()" style="padding:8px; border-radius:6px; border:1px solid #ddd; width:250px;">
            <button onclick="exportExcel()" style="background:#15803d; color:white; border:none; padding:8px 15px; border-radius:6px; cursor:pointer;">Excel</button>
        </div>
    </div>

    <?php 
    // PRE-FETCH DATA FOR ANALYTICS
    $chartLabels = []; $chartTargetTon = []; $chartActualTon = []; $totalMCalls = 0; $totalPCalls = 0;
    $sumTargetVal=0; $sumActualVal=0; $sumRDTon=0; $sumTargetTon=0;
    
    $dataRows = [];
    $qry = mysqli_query($conn, $sqlst);
    while($rst = mysqli_fetch_row($qry)){
        
        $dataRows[] = $rst;
        $chartLabels[] = $rst[0];
        $chartTargetTon[] = $rst[4];
        $chartActualTon[] = $rst[5];
        $totalMCalls += $rst[15];
        $totalPCalls += $rst[12];
        $sumTargetVal += $rst[2];
        $sumActualVal += $rst[3];
        $sumTargetTon += $rst[4];
        $sumRDTon += ($rst[16]+$rst[20]);
    }
    ?>

    
    <div class="kpi-grid">
        <div class="kpi-card"><h3>Primary Target</h3><p><?=number_format(0,0)?></p></div>
        <div class="kpi-card"><h3>Primary Actual</h3><p><?=number_format($sumActualVal,0)?></p></div>
        <div class="kpi-card" style="border-left-color:var(--success)"><h3>Total RD Ton</h3><p><?=number_format($sumRDTon,2)?></p></div>
        <div class="kpi-card" style="border-left-color:var(--warning)"><h3>Strike Rate</h3><p><?=($totalMCalls>0)?number_format($totalPCalls*100/$totalMCalls,1):0?>%</p></div>
    </div>

    <div class="chart-row">
        <div class="chart-card"><canvas id="tonChart"></canvas></div>
        <div class="chart-card"><canvas id="callChart"></canvas></div>
    </div>

    <div class="table-container">
        <table id="dt">
            <thead>
                <tr>
                    <th>Executive</th>
                    <th>Area</th>
                    <th>Agent</th>
                    <th>Territory</th>
                    <th>WD</th>
                    <th>Tar-Ton (P)</th>
                    <th>Act-Ton (P)</th>
                    <th>Ach %</th>
                    <th>Tar-Ton (RD)</th>
                    <th>Act-Ton (RD)</th>
                    <th>Target (V)</th>
                    <th>Orders</th>
                    <th>Primary</th>
                    <th>RD-Sale</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($dataRows as $rst): 
                    $achPerc = ($rst[4]>0)?($rst[5]*100/$rst[4]):0;
                    $achClass = ($achPerc>=100)?'pill-green':(($achPerc>=75)?'pill-orange':'');
                ?>
                <tr>
                    <td><?=$rst[8]?></td>
                    <td><?=$rst[19]?></td>
                    <td><?=$rst[0]?></td>
                    <td><?=$rst[1]?></td>
                    <td class="num"><?=$rst[11]?></td>
                    <td class="num"><?=number_format($rst[4],2)?></td>
                    <td class="num"><?=number_format($rst[5],2)?></td>
                                        <td><span class="pill <?=$achClass?>"><?=number_format($achPerc,1)?>%</span></td>
                    <td class="num"><?=number_format($rst[17],2)?></td>
                    <td class="num"><?=number_format($rst[16]+$rst[20],2)?></td>
                    <td class="num"><?=number_format(0,0)?></td>
                    <td class="num"><?=number_format($rst[6],0)?></td>
                    <td class="num"><?=number_format($rst[3],0)?></td>
                    <td class="num"><?=number_format($rst[9]+$rst[14],0)?></td>
                    
<td>
    <a href="rpmrangenewtm.php?starts=<?=urlencode($starts)?>&ends=<?=urlencode($ends)?>&agents=<?=urlencode($rst[0])?>" target="_blank" class="action-btn">Range</a>
    <a href="rpmtgttitemwisetgsummary2tm.php?starts=<?=urlencode($starts)?>&ends=<?=urlencode($ends)?>&agents=<?=urlencode($rst[0])?>" target="_blank" class="action-btn">Items</a>
</td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
    // Tonnage Comparison Chart
    new Chart(document.getElementById('tonChart'), {
        type: 'bar',
        data: {
            labels: <?=json_encode($chartLabels)?>,
            datasets: [
                { label: 'Target Ton', data: <?=json_encode($chartTargetTon)?>, backgroundColor: '#cbd5e1' },
                { label: 'Actual Ton', data: <?=json_encode($chartActualTon)?>, backgroundColor: '#0056b3' }
            ]
        },
        options: { maintainAspectRatio: false, plugins: { title: { display: true, text: 'Agent Tonnage Performance' } } }
    });

    // Market Calls Doughnut
    new Chart(document.getElementById('callChart'), {
        type: 'doughnut',
        data: {
            labels: ['Productive', 'Non-Productive'],
            datasets: [{
                data: [<?=$totalPCalls?>, <?=($totalMCalls - $totalPCalls)?>],
                backgroundColor: ['#22c55e', '#ef4444']
            }]
        },
        options: { maintainAspectRatio: false, plugins: { title: { display: true, text: 'Call Productivity Share' } } }
    });

    function srgs() {
        let input = document.getElementById('srctxt').value.toLowerCase();
        let rows = document.querySelectorAll('#dt tbody tr');
        rows.forEach(row => {
            let text = row.innerText.toLowerCase();
            row.style.display = text.includes(input) ? '' : 'none';
        });
    }

    function exportExcel() {
        let wb = XLSX.utils.table_to_book(document.getElementById('dt'), {sheet: "Agent Achievements"});
        XLSX.writeFile(wb, "Agent_Achievement_Report.xlsx");
    }
</script>
</body>
</html>