<?php
require '../config/db.php';

/* ===== DEFAULT MONTH/YEAR ===== */
$month = $_POST['month'] ?? ($_GET['month'] ?? date('n'));
$year  = $_POST['year']  ?? ($_GET['year'] ?? date('Y'));

/* ===== DESIGNERS ===== */
$designers = $pdo->query("SELECT id, full_name FROM design_employees ORDER BY full_name")->fetchAll(PDO::FETCH_ASSOC);

/* ==================== SAVE ALL KPI ==================== */
if(isset($_POST['save_all'])){
    $designer_id   = $_POST['designer_id'];
    $designer_name = $_POST['designer_name'];

    // ===== QUALITY KPI =====
    $projStmt = $pdo->prepare("
        SELECT id, price, tip_amount, deadline
        FROM projects
        WHERE designer_id=? AND MONTH(created_at)=? AND YEAR(created_at)=? AND status='Completed'
    ");
    $projStmt->execute([$designer_id,$month,$year]);
    $projects = $projStmt->fetchAll(PDO::FETCH_ASSOC);

    $totalCompleted = count($projects);
    $avgTipPercentages = [];
    $tippedProjects = 0;
    $onTimeDelivered = 0;

    foreach($projects as $p){
        if($p['tip_amount'] !== null && $p['tip_amount'] > 0){
            $avgTipPercentages[] = ($p['price']>0)?($p['tip_amount']/$p['price']*100):0;
            $tippedProjects++;
        }
        $actStmt = $pdo->prepare("SELECT created_at FROM project_actions WHERE project_id=? AND action='Final files uploaded' ORDER BY created_at DESC LIMIT 1");
        $actStmt->execute([$p['id']]);
        $finalUpload = $actStmt->fetchColumn();
        if($finalUpload && strtotime($finalUpload) <= strtotime($p['deadline'])){
            $onTimeDelivered++;
        }
    }

    // Avg Tip Score (15)
    $avgTipPercent = ($avgTipPercentages)?array_sum($avgTipPercentages)/count($avgTipPercentages):0;
    if($avgTipPercent>=50) $tipPercentScore=15;
    elseif($avgTipPercent>=30) $tipPercentScore=14;
    elseif($avgTipPercent>=28) $tipPercentScore=13;
    elseif($avgTipPercent>=26) $tipPercentScore=12;
    elseif($avgTipPercent>=24) $tipPercentScore=11;
    elseif($avgTipPercent>=22) $tipPercentScore=10;
    elseif($avgTipPercent>=20) $tipPercentScore=9;
    elseif($avgTipPercent>=18) $tipPercentScore=8;
    elseif($avgTipPercent>=16) $tipPercentScore=7;
    elseif($avgTipPercent>=14) $tipPercentScore=6;
    elseif($avgTipPercent>=12) $tipPercentScore=5;
    elseif($avgTipPercent>=10) $tipPercentScore=4;
    elseif($avgTipPercent>=8) $tipPercentScore=3;
    elseif($avgTipPercent>=6) $tipPercentScore=2;
    elseif($avgTipPercent>=1) $tipPercentScore=1;
    else $tipPercentScore=0;

    // Tip Ratio Score (5)
    $tipRatioPercent = ($totalCompleted>0)?($tippedProjects/$totalCompleted*100):0;
    if($tipRatioPercent==100) $tipRatioScore=5;
    elseif($tipRatioPercent>=80) $tipRatioScore=4;
    elseif($tipRatioPercent>=60) $tipRatioScore=3;
    elseif($tipRatioPercent>=40) $tipRatioScore=2;
    elseif($tipRatioPercent>=1) $tipRatioScore=1;
    else $tipRatioScore=0;

    // On-Time Score (5)
    $onTimeScore = ($totalCompleted>0 && $onTimeDelivered==$totalCompleted)?5:0;

    $totalQuality = $tipPercentScore + $tipRatioScore + $onTimeScore; // Max 25

    // UPSERT Quality
    $upsert = $pdo->prepare("
        INSERT INTO design_quality_kpi
        (designer_id, designer_name, month, year, avg_tip_score, tip_ratio_score, ontime_score, total_quality)
        VALUES (?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
            avg_tip_score=VALUES(avg_tip_score),
            tip_ratio_score=VALUES(tip_ratio_score),
            ontime_score=VALUES(ontime_score),
            total_quality=VALUES(total_quality),
            updated_at=CURRENT_TIMESTAMP
    ");
    $upsert->execute([$designer_id,$designer_name,$month,$year,$tipPercentScore,$tipRatioScore,$onTimeScore,$totalQuality]);

    // ===== OBJECTIVE KPI =====
    
    /* ---- Process KPI (10) ---- */
    $assigned = $pdo->prepare("
        SELECT COUNT(*) FROM projects
        WHERE designer_id=? AND MONTH(created_at)=? AND YEAR(created_at)=?
    ");
    $assigned->execute([$designer_id,$month,$year]);
    $assignedCount = $assigned->fetchColumn();

    $done = $pdo->prepare("
        SELECT COUNT(*) FROM projects
        WHERE designer_id=? AND MONTH(created_at)=? AND YEAR(created_at)=?
        AND status IN ('Completed','Final files uploaded')
    ");
    $done->execute([$designer_id,$month,$year]);
    $doneCount = $done->fetchColumn();

    $compliance = ($assignedCount>0)?($doneCount/$assignedCount*100):0;
    if($compliance>=95) $processScore=10;
    elseif($compliance>=90) $processScore=8;
    elseif($compliance>=85) $processScore=6;
    else $processScore=4;

   // ===== OBJECTIVE KPI (Auto Calculate Revisions + On-Time + Quality) =====

// Calculate Quality first
// $totalQuality = previously calculated from Quality KPI

// Auto Calculate Revisions
$projStmt = $pdo->prepare("
    SELECT id, revision_count, deadline
    FROM projects
    WHERE designer_id=? AND MONTH(created_at)=? AND YEAR(created_at)=? AND status='Completed'
");
$projStmt->execute([$designer_id,$month,$year]);
$projects = $projStmt->fetchAll(PDO::FETCH_ASSOC);

$totalProjects = count($projects);
$totalRevisions = 0;
$onTimeDelivered = 0;

foreach($projects as $p){
    $totalRevisions += (float) $p['revision_count'];

    // On-Time Delivery
    $actStmt = $pdo->prepare("
        SELECT created_at 
        FROM project_actions 
        WHERE project_id=? AND action='Final files uploaded'
        ORDER BY created_at DESC LIMIT 1
    ");
    $actStmt->execute([$p['id']]);
    $finalUpload = $actStmt->fetchColumn();
    if($finalUpload && strtotime($finalUpload) <= strtotime($p['deadline'])){
        $onTimeDelivered++;
    }
}

$avgRevision = ($totalProjects>0)?($totalRevisions/$totalProjects):0;

// Revision Score (0-15)
if($avgRevision == 0) $revisionScore = 15;
elseif($avgRevision <= 1) $revisionScore = 10;
elseif($avgRevision <= 1.5) $revisionScore = 8;
elseif($avgRevision <= 2) $revisionScore = 6;
elseif($avgRevision <= 2.5) $revisionScore = 4;
elseif($avgRevision <= 3) $revisionScore = 2;
else $revisionScore = 0;

// On-Time Delivery Score (0-25)
$onTimePercent = ($totalProjects>0)?($onTimeDelivered/$totalProjects*100):0;
if($onTimePercent >= 95) $onTimeScore=25;
elseif($onTimePercent >= 90) $onTimeScore=20;
elseif($onTimePercent >= 85) $onTimeScore=15;
elseif($onTimePercent >= 80) $onTimeScore=10;
elseif($onTimePercent >= 75) $onTimeScore=5;
else $onTimeScore=0;

// Total Objective = On-Time + Quality + Revision + Process
$totalObjective = $onTimeScore + $totalQuality + $revisionScore + $processScore;


    $upsertObj = $pdo->prepare("
        INSERT INTO design_objective_kpi
        (designer_id, designer_name, month, year, on_time_delivery, quality, revisions, process, total_objective)
        VALUES (?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
            on_time_delivery=VALUES(on_time_delivery),
            quality=VALUES(quality),
            revisions=VALUES(revisions),
            process=VALUES(process),
            total_objective=VALUES(total_objective),
            updated_at=CURRENT_TIMESTAMP
    ");
    $upsertObj->execute([$designer_id,$designer_name,$month,$year,$onTimeScore,$totalQuality,$revisionScore,$processScore,$totalObjective]);

    // ===== BEHAVIOR KPI =====
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM design_employee_attendance WHERE employee_id=? AND MONTH(att_date)=? AND YEAR(att_date)=? AND status='Present'");
    $stmt->execute([$designer_id,$month,$year]);
    $presentDays = (int)$stmt->fetchColumn();
    $daysInMonth = cal_days_in_month(CAL_GREGORIAN,$month,$year);
    $attendancePercent = ($daysInMonth>0)?($presentDays/$daysInMonth*100):0;
    $attendance = ($attendancePercent>=86)?6:($attendancePercent>=76?5:($attendancePercent>=66?4:($attendancePercent>=51?3:($attendancePercent>=31?2:($attendancePercent>=1?1:0))))); 

    $lateStmt = $pdo->prepare("SELECT COUNT(*) FROM design_employee_late_log WHERE employee_id=? AND MONTH(log_date)=? AND YEAR(log_date)=?");
    $lateStmt->execute([$designer_id,$month,$year]);
    $late = (int)$lateStmt->fetchColumn();
    $punctuality = ($late==0?4:($late==1?3:($late==2?2:($late==3?1:0))));

    $communication     = min(max($_POST['communication'],0),3);
    $team_coordination = min(max($_POST['team_coordination'],0),3);
    $overtime          = min(max($_POST['overtime'],0),4);
    $crm_discipline    = min(max($_POST['crm_discipline'],0),3);
    $professionalism   = min(max($_POST['professionalism'],0),2);

    $totalBehavior = $punctuality+$attendance+$communication+$team_coordination+$overtime+$crm_discipline+$professionalism;

    $saveBehavior = $pdo->prepare("
        INSERT INTO design_behavior_kpi
        (designer_id, designer_name, month, year, punctuality, attendance, communication, team_coordination, overtime, crm_discipline, professionalism, total_behavior)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
            punctuality=VALUES(punctuality),
            attendance=VALUES(attendance),
            communication=VALUES(communication),
            team_coordination=VALUES(team_coordination),
            overtime=VALUES(overtime),
            crm_discipline=VALUES(crm_discipline),
            professionalism=VALUES(professionalism),
            total_behavior=VALUES(total_behavior),
            updated_at=CURRENT_TIMESTAMP
    ");
    $saveBehavior->execute([$designer_id,$designer_name,$month,$year,$punctuality,$attendance,$communication,$team_coordination,$overtime,$crm_discipline,$professionalism,$totalBehavior]);

    $msg_all = "✅ All KPIs Saved Successfully!";
}

/* ==================== VIEW KPI ==================== */
$viewObjective = $viewBehavior = $viewQuality = null;
if(isset($_POST['view_kpi'])){
    $designer_id = $_POST['designer_id'];

    $stmt = $pdo->prepare("SELECT * FROM design_objective_kpi WHERE designer_id=? AND month=? AND year=?");
    $stmt->execute([$designer_id,$month,$year]);
    $viewObjective = $stmt->fetch(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("SELECT * FROM design_behavior_kpi WHERE designer_id=? AND month=? AND year=?");
    $stmt->execute([$designer_id,$month,$year]);
    $viewBehavior = $stmt->fetch(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("SELECT * FROM design_quality_kpi WHERE designer_id=? AND month=? AND year=?");
    $stmt->execute([$designer_id,$month,$year]);
    $viewQuality = $stmt->fetch(PDO::FETCH_ASSOC);
}
?>




<!DOCTYPE html>
<html>
<head>
<title>Designer KPI Entry</title>
<style>
body{font-family:Arial;background:#f1f5f9}
.container{max-width:1200px;margin:30px auto}
.card,.filter-box{background:#fff;padding:20px;border-radius:16px;margin-bottom:20px;box-shadow:0 10px 25px rgba(0,0,0,.06)}
.card h3{margin:0;font-size:15px;color:#475569}
.card p{font-size:28px;font-weight:bold;margin:8px 0 0}
.grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:18px}
.progress{height:16px;background:#e5e7eb;border-radius:12px;overflow:hidden}
.progress div{height:100%;background:linear-gradient(90deg,#22c55e,#16a34a)}
button{padding:10px 18px;border:none;background:#2563eb;color:#fff;border-radius:10px;cursor:pointer}
select,input{padding:10px;margin-right:10px;width:150px}
.success{color:green;font-weight:bold;margin-bottom:10px}
</style>
</head>
<body>
<div class="container">
<div class="filter-box">
<form method="post">
<select name="designer_id" required onchange="this.form.designer_name.value=this.options[this.selectedIndex].text">
<option value="">Select Designer</option>
<?php foreach($designers as $d): ?>
<option value="<?=$d['id']?>" <?=($designer_id==$d['id'])?'selected':''?>><?=$d['full_name']?></option>
<?php endforeach ?>
</select>
<input type="hidden" name="designer_name">
<input type="number" name="month" min="1" max="12" value="<?=$month?>">
<input type="number" name="year" value="<?=$year?>">

<h3>Manual Entries</h3>
<input type="number" name="revisions" placeholder="Revisions (0-15)">
<input type="number" name="process" placeholder="Process (0-10)">
<input type="number" name="communication" placeholder="Communication (0-3)">
<input type="number" name="team_coordination" placeholder="Team Coordination (0-3)">
<input type="number" name="overtime" placeholder="Overtime (0-4)">
<input type="number" name="crm_discipline" placeholder="CRM Discipline (0-3)">
<input type="number" name="professionalism" placeholder="Professionalism (0-2)">

<button name="save_all">💾 Save All KPI</button>
<?= isset($msg_all)?$msg_all:'' ?>

<button name="view_kpi">🔍 View KPI</button>
</form>
</div>


<?php if($viewObjective || $viewBehavior || $viewQuality): ?>
<div class="card total">
<h2>Designer KPI Total</h2>
<p><?=$month?> / <?=$year?></p>
<h1>
<?=($viewObjective['total_objective'] ?? 0)+($viewBehavior['total_behavior'] ?? 0)+($viewQuality['total_quality'] ?? 0)?> / 125
</h1>
<div class="progress">
<div style="width:<?=((($viewObjective['total_objective'] ?? 0)+($viewBehavior['total_behavior'] ?? 0)+($viewQuality['total_quality'] ?? 0))/125)*100?>%"></div>
</div>
</div>

<div class="grid">
<div class="card"><h3>Objective KPI</h3><p><?=($viewObjective['total_objective'] ?? 0)?> / 75</p></div>
<div class="card"><h3>Behavior KPI</h3><p><?=($viewBehavior['total_behavior'] ?? 0)?> / 25</p></div>
<div class="card"><h3>Quality KPI</h3><p><?=($viewQuality['total_quality'] ?? 0)?> / 25</p></div>
</div>
<?php endif; ?>


</div>
</body>
</html>
