<?php

namespace App\Models;

use App\Core\Database;

class CesarKpi
{
    // ─── Access control ────────────────────────────────────────────────────────

    /** Only cesar.saona may submit his own KPIs */
    public static function canSubmit(array $user): bool
    {
        return $user['user_type'] === 'manager' && ($user['username'] ?? '') === 'cesar.saona';
    }

    /** Super admin (Sara) can review; managers who have Cesar reporting to them can too */
    public static function canReview(array $user): bool
    {
        if ($user['is_super_admin']) return true;
        // sara.lasher is the top-level manager with full KPI oversight
        if ($user['user_type'] === 'manager' && ($user['username'] ?? '') === 'sara.lasher') return true;
        // A manager can review if Cesar reports to them
        if ($user['user_type'] !== 'manager') return false;
        $db = Database::connection();
        $stmt = $db->prepare(
            "SELECT id FROM managers WHERE username = 'cesar.saona'
             AND reports_to_type = 'manager' AND reports_to_id = :rid"
        );
        $stmt->execute(['rid' => $user['user_id']]);
        return (bool) $stmt->fetch();
    }

    // ─── CRUD ──────────────────────────────────────────────────────────────────

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

    public static function findByWeek(string $weekEnding): ?array
    {
        $db = Database::connection();
        $r = $db->prepare("SELECT * FROM cesar_kpi_entries WHERE week_ending = :w");
        $r->execute(['w' => $weekEnding]);
        return $r->fetch(\PDO::FETCH_ASSOC) ?: null;
    }

    public static function all(array $filters = []): array
    {
        $db = Database::connection();
        $where = ['1=1'];
        $params = [];

        if (!empty($filters['status'])) {
            $where[] = 'status = :status';
            $params['status'] = $filters['status'];
        }
        if (!empty($filters['from'])) {
            $where[] = 'week_ending >= :from';
            $params['from'] = $filters['from'];
        }
        if (!empty($filters['to'])) {
            $where[] = 'week_ending <= :to';
            $params['to'] = $filters['to'];
        }

        $sql = "SELECT * FROM cesar_kpi_entries WHERE " . implode(' AND ', $where) . " ORDER BY week_ending DESC";
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /** Next week_ending not yet in a non-editable status */
    public static function nextAvailableWeek(): string
    {
        $db = Database::connection();
        $locked = $db->query(
            "SELECT week_ending FROM cesar_kpi_entries
             WHERE status NOT IN ('draft','returned') ORDER BY week_ending DESC"
        )->fetchAll(\PDO::FETCH_COLUMN);

        $candidate = date('Y-m-d');
        for ($i = 0; $i < 12; $i++) {
            if (!in_array($candidate, $locked)) return $candidate;
            $candidate = date('Y-m-d', strtotime($candidate . ' +7 days'));
        }
        return $candidate;
    }

    public static function saveDraft(array $data): int
    {
        $db = Database::connection();

        // Compute auto fields
        $started = array_sum(array_column(json_decode($data['contracts_started'] ?? '[]', true) ?: [], 'hours'));
        $lost    = array_sum(array_column(json_decode($data['contracts_lost']    ?? '[]', true) ?: [], 'hours'));
        $netHours = $started - $lost;

        $otBudgeted = (float)($data['ot_budgeted'] ?? 0);
        $otActual   = (float)($data['ot_actual'] ?? 0);
        $otVariance = $otActual - $otBudgeted;

        $fields = [
            'week_ending'              => $data['week_ending'],
            'week_starting'            => $data['week_starting'] ?? null,
            'contracts_started'        => $data['contracts_started']       ?? '[]',
            'contracts_lost'           => $data['contracts_lost']          ?? '[]',
            'net_hours_change'         => $netHours,
            'post_order_updates'       => $data['post_order_updates']      ?? '[]',
            'client_meetings'          => $data['client_meetings']         ?? '[]',
            'site_visit_scores'        => $data['site_visit_scores']       ?? '[]',
            'new_client_reachouts'     => $data['new_client_reachouts']    ?? '[]',
            'contract_renewals'        => $data['contract_renewals']       ?? '[]',
            'ot_budgeted'              => $otBudgeted,
            'ot_actual'                => $otActual,
            'ot_variance'              => $otVariance,
            'score_karan'              => (int)($data['score_karan'] ?? 0),
            'score_karan_notes'        => $data['score_karan_notes']       ?? '',
            'score_harry'              => (int)($data['score_harry'] ?? 0),
            'score_harry_notes'        => $data['score_harry_notes']       ?? '',
            'score_jatinder'           => (int)($data['score_jatinder'] ?? 0),
            'score_jatinder_notes'     => $data['score_jatinder_notes']    ?? '',
            'score_michael'            => (int)($data['score_michael'] ?? 0),
            'score_michael_notes'      => $data['score_michael_notes']     ?? '',
            'open_investigations'      => (int)($data['open_investigations'] ?? 0),
            'investigation_desc'       => $data['investigation_desc']      ?? '',
            'escalations_received'     => (int)($data['escalations_received'] ?? 0),
            'escalations_from_summary' => $data['escalations_from_summary'] ?? '',
        ];

        if (!empty($data['id'])) {
            // Update existing draft/returned
            $id = (int)$data['id'];
            $set = implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($fields)));
            $stmt = $db->prepare("UPDATE cesar_kpi_entries SET $set WHERE id = :id AND status IN ('draft','returned')");
            $fields['id'] = $id;
            $stmt->execute($fields);
            return $id;
        }

        // Insert new
        $cols = implode(', ', array_map(fn($k) => "`$k`", array_keys($fields)));
        $vals = implode(', ', array_map(fn($k) => ":$k", array_keys($fields)));
        $stmt = $db->prepare("INSERT INTO cesar_kpi_entries ($cols) VALUES ($vals)");
        $stmt->execute($fields);
        return (int)$db->lastInsertId();
    }

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

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

    public static function approve(int $id, array $reviewer, int $score = 0, string $reviewComment = ''): void
    {
        $db = Database::connection();
        $db->prepare(
            "UPDATE cesar_kpi_entries SET status='locked', reviewed_at=NOW(),
             reviewer_type=:rt, reviewer_id=:rid, reviewer_name=:rn,
             review_score=:sc, review_comment=:rc WHERE id=:id"
        )->execute([
            'rt'  => $reviewer['is_super_admin'] ? 'superadmin' : 'manager',
            'rid' => $reviewer['user_id'],
            'rn'  => $reviewer['name'],
            'sc'  => $score ?: null,
            'rc'  => $reviewComment,
            'id'  => $id,
        ]);
    }

    public static function reviseScore(int $id, array $reviewer, int $newScore, string $newComment, string $reason): void
    {
        $db = Database::connection();
        $current = $db->prepare("SELECT review_score, review_comment FROM cesar_kpi_entries WHERE id=:id");
        $current->execute(['id' => $id]);
        $old = $current->fetch(\PDO::FETCH_ASSOC);

        $db->prepare(
            "UPDATE cesar_kpi_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_review_history
             (kpi_type,kpi_entry_id,previous_score,previous_comment,new_score,new_comment,revised_reason,revised_by_type,revised_by_id,revised_by_name)
             VALUES ('cesar',:eid,:ps,:pc,:ns,:nc,:rr,:rbt,:rbid,:rbn)"
        )->execute([
            'eid' => $id,
            'ps' => $old['review_score'] ?? null, 'pc' => $old['review_comment'] ?? '',
            'ns' => $newScore, 'nc' => $newComment, 'rr' => $reason,
            'rbt' => $reviewer['is_super_admin'] ? 'superadmin' : 'manager',
            'rbid' => $reviewer['user_id'], 'rbn' => $reviewer['name'],
        ]);
    }

    public static function reviewHistory(int $id): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT * FROM kpi_review_history WHERE kpi_type='cesar' AND kpi_entry_id=:id ORDER BY created_at DESC"
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

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

    public static function sites(): array
    {
        $db = Database::connection();
        return $db->query("SELECT id, name FROM sites ORDER BY name")->fetchAll(\PDO::FETCH_ASSOC);
    }

    public static function decodeJson(?string $json): array
    {
        if (!$json) return [];
        $decoded = json_decode($json, true);
        return is_array($decoded) ? $decoded : [];
    }
}
