<?php
namespace App\Models;
use App\Core\Database;
use PDO;

/**
 * Jatinder (Accounts) KPI model.
 * Completely separate from all other KPI models/tables.
 * Reports to Cesar; Sara has full oversight.
 */
class AccountsKpi
{
    /**
     * Can this user submit Accounts KPI entries?
     * Uses username check as primary (reliable without kpi_form_type column).
     * Will automatically extend to project-based once kpi_form_type is set up.
     */
    public static function canSubmit(array $user): bool
    {
        // Username-based check — works for any user_type
        if (($user['username'] ?? '') === 'jatinder.kumar') return true;
        // Project-based check for future submitters
        try {
            return \App\Models\Lookup::userHasKpiFormType($user['user_type'], $user['user_id'], 'accounts');
        } catch (\Throwable $e) {
            return false;
        }
    }

    public static function canReview(array $user): bool
    {
        if (!empty($user['is_super_admin'])) return true;
        if (($user['user_type'] ?? '') !== 'manager') return false;
        return in_array($user['username'] ?? '', ['cesar.saona', 'sara.lasher'], true);
    }

    public static function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare('SELECT * FROM kpi_accounts_entries WHERE id=:id');
        $stmt->execute(['id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    public static function forSubmitter(int $submitterId, array $filters = []): array
    {
        $sql = 'SELECT * FROM kpi_accounts_entries WHERE submitter_id=:id';
        $p   = ['id' => $submitterId];
        if (!empty($filters['status'])) { $sql .= ' AND status=:s'; $p['s'] = $filters['status']; }
        if (!empty($filters['from']))   { $sql .= ' AND week_ending>=:f'; $p['f'] = $filters['from']; }
        if (!empty($filters['to']))     { $sql .= ' AND week_ending<=:t'; $p['t'] = $filters['to']; }
        $sql .= ' ORDER BY week_ending DESC';
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($p);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function allEntries(array $filters = []): array
    {
        $sql = 'SELECT * FROM kpi_accounts_entries WHERE 1=1';
        $p   = [];
        if (!empty($filters['status'])) { $sql .= ' AND status=:s'; $p['s'] = $filters['status']; }
        if (!empty($filters['from']))   { $sql .= ' AND week_ending>=:f'; $p['f'] = $filters['from']; }
        if (!empty($filters['to']))     { $sql .= ' AND week_ending<=:t'; $p['t'] = $filters['to']; }
        $sql .= ' ORDER BY week_ending DESC';
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($p);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function pendingCount(): int
    {
        try {
            return (int)Database::connection()
                ->query("SELECT COUNT(*) FROM kpi_accounts_entries WHERE status='submitted'")
                ->fetchColumn();
        } catch (\Throwable $e) { return 0; }
    }

    public static function saveDraft(array $user, string $weekEnding, array $f, ?int $existingId): int
    {
        $db = Database::connection();
        $weekStart = date('Y-m-d', strtotime($weekEnding . ' -6 days'));
        $data = [
            'submitter_type' => $user['user_type'],
            'submitter_id'   => $user['user_id'],
            'submitter_name' => $user['name'],
            'week_ending'    => $weekEnding,
            'week_starting'  => $weekStart,
            'new_hires'              => (int)($f['new_hires'] ?? 0),
            'new_hire_names'         => $f['new_hire_names'] ?? null,
            'new_hire_training_hrs'  => (float)($f['new_hire_training_hrs'] ?? 0),
            'new_contract_hrs'       => (float)($f['new_contract_hrs'] ?? 0),
            'new_contract_site'      => $f['new_contract_site'] ?? null,
            'lost_contract_hrs'      => (float)($f['lost_contract_hrs'] ?? 0),
            'lost_contract_site'     => $f['lost_contract_site'] ?? null,
            'guards_sleeping'        => (int)($f['guards_sleeping'] ?? 0),
            'serious_violations'     => (int)($f['serious_violations'] ?? 0),
            'failed_reinspections'   => (int)($f['failed_reinspections'] ?? 0),
            'avg_score_per_site'     => (float)($f['avg_score_per_site'] ?? 0),
            'uniform_compliance_pct' => (int)($f['uniform_compliance_pct'] ?? 0),
            'violation_desc'         => $f['violation_desc'] ?? null,
            'violation_handled'      => $f['violation_handled'] ?? null,
            'improvements'           => $f['improvements'] ?? null,
            'client_meetings'        => (int)($f['client_meetings'] ?? 0),
            'client_names_met'       => $f['client_names_met'] ?? null,
            'client_complaints'      => (int)($f['client_complaints'] ?? 0),
            'client_compliments'     => (int)($f['client_compliments'] ?? 0),
            'avg_response_time_hrs'  => (float)($f['avg_response_time_hrs'] ?? 0),
            'sites_visited'          => $f['sites_visited'] ?? null,
            'recognition_count'      => (int)($f['recognition_count'] ?? 0),
            'recognition_names'      => $f['recognition_names'] ?? null,
            'insp_day_total'         => (int)($f['insp_day_total'] ?? 0),
            'insp_day_failed'        => (int)($f['insp_day_failed'] ?? 0),
            'insp_swing_total'       => (int)($f['insp_swing_total'] ?? 0),
            'insp_swing_failed'      => (int)($f['insp_swing_failed'] ?? 0),
            'insp_grave_total'       => (int)($f['insp_grave_total'] ?? 0),
            'insp_grave_failed'      => (int)($f['insp_grave_failed'] ?? 0),
            'insp_total'             => (int)($f['insp_total'] ?? 0),
            'insp_failed_total'      => (int)($f['insp_failed_total'] ?? 0),
            'failed_inspections_detail' => $f['failed_inspections_detail'] ?? null,
            'refresher_done'         => $f['refresher_done'] ?? null,
            'refresher_detail'       => $f['refresher_detail'] ?? null,
            'site_improvements'      => $f['site_improvements'] ?? null,
            'msu_pickup'             => (int)($f['msu_pickup'] ?? 0),
            'msu_dropoff'            => (int)($f['msu_dropoff'] ?? 0),
            'msu_troubleshoot'       => $f['msu_troubleshoot'] ?? null,
            'msu_trouble_notes'      => $f['msu_trouble_notes'] ?? null,
            'afterhours_response'    => $f['afterhours_response'] ?? null,
            'afterhours_site'        => $f['afterhours_site'] ?? null,
            'afterhours_time'        => $f['afterhours_time'] ?? null,
            'afterhours_notes'       => $f['afterhours_notes'] ?? null,
            'msu_notes'              => $f['msu_notes'] ?? null,
            'phone_audit_done'       => $f['phone_audit_done'] ?? null,
            'phone_items_saved'      => $f['phone_items_saved'] ?? null,
            'phone_unauth_apps'      => $f['phone_unauth_apps'] ?? null,
            'phone_count'            => (int)($f['phone_count'] ?? 0),
            'phone_violators'        => $f['phone_violators'] ?? null,
            'phone_apps_notes'       => $f['phone_apps_notes'] ?? null,
            'phone_notes'            => $f['phone_notes'] ?? null,
            'highlights'             => $f['highlights'] ?? null,
            'concerns'               => $f['concerns'] ?? null,
            'remarks'                => $f['remarks'] ?? null,
        ];

        if ($existingId) {
            $set = implode(', ', array_map(fn($c) => "`$c`=:$c", array_keys($data)));
            $data['id'] = $existingId;
            $db->prepare("UPDATE kpi_accounts_entries SET $set WHERE id=:id")->execute($data);
            return $existingId;
        }
        $cols = implode(',', array_map(fn($c) => "`$c`", array_keys($data)));
        $pls  = implode(',', array_map(fn($c) => ":$c", array_keys($data)));
        $db->prepare("INSERT INTO kpi_accounts_entries ($cols) VALUES ($pls)")->execute($data);
        return (int)$db->lastInsertId();
    }

    public static function submit(int $id): void
    {
        Database::connection()->prepare(
            "UPDATE kpi_accounts_entries SET status='submitted',submitted_at=NOW()
             WHERE id=:id AND status IN ('draft','returned')"
        )->execute(['id' => $id]);
    }

    public static function approve(int $id, array $reviewer, int $score, string $comment): void
    {
        Database::connection()->prepare(
            "UPDATE kpi_accounts_entries
             SET status='locked',approved_at=NOW(),locked_at=NOW(),reviewed_at=NOW(),
                 reviewer_type=:rt,reviewer_id=:rid,reviewer_name=:rn,
                 review_score=:sc,review_comment=:rc
             WHERE id=:id"
        )->execute(['rt'=>$reviewer['user_type'],'rid'=>$reviewer['user_id'],
                    'rn'=>$reviewer['name'],'sc'=>$score,'rc'=>$comment,'id'=>$id]);
    }

    public static function return_(int $id, array $reviewer, string $reason): void
    {
        Database::connection()->prepare(
            "UPDATE kpi_accounts_entries
             SET status='returned',returned_at=NOW(),reviewed_at=NOW(),
                 reviewer_type=:rt,reviewer_id=:rid,reviewer_name=:rn,return_reason=:r
             WHERE id=:id"
        )->execute(['rt'=>$reviewer['user_type'],'rid'=>$reviewer['user_id'],
                    'rn'=>$reviewer['name'],'r'=>$reason,'id'=>$id]);
    }

    public static function review(int $id, array $reviewer, string $comment): void
    {
        Database::connection()->prepare(
            "UPDATE kpi_accounts_entries
             SET status='reviewed',reviewed_at=NOW(),
                 reviewer_type=:rt,reviewer_id=:rid,reviewer_name=:rn,review_comment=:rc
             WHERE id=:id"
        )->execute(['rt'=>$reviewer['user_type'],'rid'=>$reviewer['user_id'],
                    'rn'=>$reviewer['name'],'rc'=>$comment,'id'=>$id]);
    }

    public static function reviseScore(int $id, array $reviewer, int $newScore, string $newComment, string $reason): void
    {
        $db = Database::connection();
        $old = $db->prepare('SELECT review_score,review_comment FROM kpi_accounts_entries WHERE id=:id');
        $old->execute(['id'=>$id]);
        $prev = $old->fetch(PDO::FETCH_ASSOC);
        $db->prepare("UPDATE kpi_accounts_entries SET review_score=:sc,review_comment=:rc,score_revised=1 WHERE id=:id")
           ->execute(['sc'=>$newScore,'rc'=>$newComment,'id'=>$id]);
        $db->prepare("INSERT INTO kpi_accounts_review_history
                      (entry_id,changed_by,old_score,new_score,old_comment,new_comment,reason)
                      VALUES(:eid,:cb,:os,:ns,:oc,:nc,:r)")
           ->execute(['eid'=>$id,'cb'=>$reviewer['name'],
                      'os'=>$prev['review_score']??null,'ns'=>$newScore,
                      'oc'=>$prev['review_comment']??null,'nc'=>$newComment,'r'=>$reason]);
    }

    public static function reviewHistory(int $id): array
    {
        $stmt = Database::connection()->prepare(
            'SELECT * FROM kpi_accounts_review_history WHERE entry_id=:id ORDER BY created_at ASC'
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
