<?php

namespace App\Models;

use App\Core\Database;

class Kpi
{
    public static function metricsForDepartment(int $departmentId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT * FROM kpi_metric_defs WHERE department_id = :d AND is_active = 1 ORDER BY sort_order"
        );
        $stmt->execute(['d' => $departmentId]);
        return $stmt->fetchAll();
    }

    public static function currentWeekStart(): string
    {
        $monday = new \DateTime('monday this week');
        return $monday->format('Y-m-d');
    }

    public static function saveEntry(int $departmentId, string $userType, int $userId, string $userName, string $periodStart, array $values): void
    {
        $db = Database::connection();
        // one entry per user/department/week - update if it already exists
        $stmt = $db->prepare(
            "SELECT id FROM kpi_entries WHERE department_id=:d AND user_type=:t AND user_id=:u AND period_start=:p"
        );
        $stmt->execute(['d' => $departmentId, 't' => $userType, 'u' => $userId, 'p' => $periodStart]);
        $existing = $stmt->fetch();

        $json = json_encode($values);

        if ($existing) {
            $db->prepare("UPDATE kpi_entries SET values_json = :v, submitted_at = NOW() WHERE id = :id")
               ->execute(['v' => $json, 'id' => $existing['id']]);
        } else {
            $db->prepare(
                "INSERT INTO kpi_entries (department_id, user_type, user_id, user_name, period_start, values_json)
                 VALUES (:d, :t, :u, :n, :p, :v)"
            )->execute(['d' => $departmentId, 't' => $userType, 'u' => $userId, 'n' => $userName, 'p' => $periodStart, 'v' => $json]);
        }
    }

    public static function entriesForDepartment(int $departmentId, ?string $periodStart = null): array
    {
        $sql = "SELECT * FROM kpi_entries WHERE department_id = :d";
        $params = ['d' => $departmentId];
        if ($periodStart) {
            $sql .= " AND period_start = :p";
            $params['p'] = $periodStart;
        }
        $sql .= " ORDER BY period_start DESC, user_name";

        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        foreach ($rows as &$r) { $r['values'] = json_decode($r['values_json'], true) ?: []; }
        return $rows;
    }

    public static function myEntry(int $departmentId, string $userType, int $userId, string $periodStart): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT * FROM kpi_entries WHERE department_id=:d AND user_type=:t AND user_id=:u AND period_start=:p"
        );
        $stmt->execute(['d' => $departmentId, 't' => $userType, 'u' => $userId, 'p' => $periodStart]);
        $row = $stmt->fetch();
        if ($row) { $row['values'] = json_decode($row['values_json'], true) ?: []; }
        return $row ?: null;
    }

    public static function saveMetricDefs(int $departmentId, array $metrics): void
    {
        $db = Database::connection();
        $db->prepare("DELETE FROM kpi_metric_defs WHERE department_id = :d")->execute(['d' => $departmentId]);

        $stmt = $db->prepare(
            "INSERT INTO kpi_metric_defs (department_id, metric_key, metric_label, unit, input_type, sort_order)
             VALUES (:d, :k, :l, :u, :t, :s)"
        );
        foreach ($metrics as $i => $m) {
            if (trim($m['label']) === '') continue;
            $key = preg_replace('/[^a-z0-9_]/', '', strtolower(str_replace(' ', '_', $m['label'])));
            $stmt->execute([
                'd' => $departmentId, 'k' => $key ?: ('metric_' . $i), 'l' => $m['label'],
                'u' => $m['unit'] ?? '', 't' => $m['type'] ?? 'number', 's' => $i,
            ]);
        }
    }
}
