<?php

namespace App\Models;

use App\Core\Database;
use App\Core\KpiConfig;

class KpiScheduling
{
    /**
     * Can this user submit Scheduling KPI entries?
     * Driven by project assignment (kpi_form_type = 'scheduling') rather than
     * hardcoded usernames — so adding a new scheduling submitter only requires
     * assigning them to the scheduling KPI project, no code change needed.
     *
     * Falls back to the legacy username list for backward compatibility during
     * transition (in case the project assignment hasn't been set up yet).
     */

    public static function canSubmitEntries(array $user): bool
{
    if ($user['user_type'] !== 'manager' || empty($user['username'])) return false;
    // Username check first (always works, no DB column dependency)
    if (in_array($user['username'], KpiConfig::schedulingEntryUsernames(), true)) return true;
    // Reviewers who are merely assigned to the Scheduling project (Cesar, Sara)
    // must never be treated as submitters here — they have their own KPI
    // forms/review queues. Without this guard, the project-based fallback
    // below incorrectly matches them too, since it only checks project
    // assignment and not submitter role.
    if (in_array($user['username'], ['cesar.saona', 'sara.lasher'], true)) return false;
    // Project-based check (works once kpi_form_type column exists)
    try {
        return \App\Models\Lookup::userHasKpiFormType($user['user_type'], $user['user_id'], 'scheduling');
    } catch (\Throwable $e) {
        return false;
    }
}

    /** Can this manager review entries submitted by the given submitter? Driven by existing Reports-To data. */
    public static function canReview(array $reviewer, string $submitterType, int $submitterId): bool
    {
        if ($reviewer['is_super_admin']) return true; // Super Admin can do everything
        if ($reviewer['user_type'] !== 'manager') return false;
        // sara.lasher is the top-level manager with full KPI oversight across
        // the whole hierarchy, not just her direct reports.
        if (($reviewer['username'] ?? '') === 'sara.lasher') return true;

        $reportsTo = UserManager::currentReportsTo($submitterType, $submitterId);
        return $reportsTo && $reportsTo['type'] === 'manager' && $reportsTo['id'] === $reviewer['user_id'];
    }

    /** Everyone this manager can review KPI entries for (their direct reports who submit entries). */
    public static function reviewableSubmitters(int $managerId): array
    {
        $db = Database::connection();
        $stmt = $db->prepare(
            "SELECT id, name, username FROM managers WHERE reports_to_type = 'manager' AND reports_to_id = :id"
        );
        $stmt->execute(['id' => $managerId]);
        return $stmt->fetchAll();
    }

    public static function departmentIdFor(string $code): ?int
    {
        $db = Database::connection();
        $stmt = $db->prepare("SELECT id FROM departments WHERE code = :c");
        $stmt->execute(['c' => $code]);
        $r = $stmt->fetch();
        return $r ? (int)$r['id'] : null;
    }

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

    public static function findDraftOrEntry(string $submitterType, int $submitterId, string $weekEnding): ?array
    {
        $db = Database::connection();
        $stmt = $db->prepare(
            "SELECT * FROM kpi_scheduling_entries WHERE submitter_type=:t AND submitter_id=:id AND week_ending=:w"
        );
        $stmt->execute(['t' => $submitterType, 'id' => $submitterId, 'w' => $weekEnding]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function summaryFor(array $rows): array
    {
        $sum = fn($col) => array_sum(array_column(array_filter($rows, fn($r) => $r[$col] !== null), $col));
        $totalHours = $sum('total_hours');
        $otHours = $sum('ot_hours');
        return [
            'total_hours' => $totalHours,
            'static_hours' => $sum('static_hours'),
            'patrol_hours' => $sum('patrol_hours'),
            'event_hours' => $sum('event_hours'),
            'ot_hours' => $otHours,
            'ot_pct' => $totalHours > 0 ? round(($otHours / $totalHours) * 100, 1) : null,
            'dt_hours' => $sum('dt_hours'),
            'open_post_hours' => $sum('open_post_hours'),
            'new_hires' => $sum('new_hires'),
            'incidents' => $sum('incidents'),
            'client_complaints' => $sum('client_complaints'),
            'entries_logged' => count($rows),
        ];
    }

    public static function forSubmitter(string $submitterType, int $submitterId, array $filters = []): array
    {
        $sql = "SELECT * FROM kpi_scheduling_entries WHERE submitter_type=:t AND submitter_id=:id";
        $params = ['t' => $submitterType, 'id' => $submitterId];
        $sql .= self::applyFilters($filters, $params);
        $sql .= " ORDER BY week_ending DESC";
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function forSubmitterIds(array $ids, string $submitterType, array $filters = []): array
    {
        if (empty($ids)) return [];
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $sql = "SELECT * FROM kpi_scheduling_entries WHERE submitter_type = ? AND submitter_id IN ($placeholders)";
        $params = array_merge([$submitterType], $ids);
        $sql .= " ORDER BY week_ending DESC";
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        return self::filterRowsInMemory($rows, $filters);
    }

    public static function allEntries(array $filters = []): array
    {
        $sql = "SELECT * FROM kpi_scheduling_entries WHERE 1=1";
        $params = [];
        $sql .= self::applyFilters($filters, $params);
        $sql .= " ORDER BY week_ending DESC, submitter_name ASC";
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function forDepartment(int $departmentId, array $filters = []): array
    {
        $sql = "SELECT * FROM kpi_scheduling_entries WHERE department_id = :d";
        $params = ['d' => $departmentId];
        $sql .= self::applyFilters($filters, $params);
        $sql .= " ORDER BY week_ending DESC";
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    private static function applyFilters(array $filters, array &$params): string
    {
        $sql = '';
        if (!empty($filters['status'])) {
            $sql .= " AND status = :status";
            $params['status'] = $filters['status'];
        }
        if (!empty($filters['week_ending'])) {
            $sql .= " AND week_ending = :week_ending";
            $params['week_ending'] = $filters['week_ending'];
        }
        if (!empty($filters['from_date'])) {
            $sql .= " AND week_ending >= :from_date";
            $params['from_date'] = $filters['from_date'];
        }
        if (!empty($filters['to_date'])) {
            $sql .= " AND week_ending <= :to_date";
            $params['to_date'] = $filters['to_date'];
        }
        if (!empty($filters['employee_id'])) {
            $sql .= " AND submitter_id = :employee_id";
            $params['employee_id'] = $filters['employee_id'];
        }
        return $sql;
    }

    private static function filterRowsInMemory(array $rows, array $filters): array
    {
        return array_values(array_filter($rows, function ($r) use ($filters) {
            if (!empty($filters['status']) && $r['status'] !== $filters['status']) return false;
            if (!empty($filters['week_ending']) && $r['week_ending'] !== $filters['week_ending']) return false;
            if (!empty($filters['from_date']) && $r['week_ending'] < $filters['from_date']) return false;
            if (!empty($filters['to_date']) && $r['week_ending'] > $filters['to_date']) return false;
            if (!empty($filters['employee_id']) && (int)$r['submitter_id'] !== (int)$filters['employee_id']) return false;
            return true;
        }));
    }

    /**
     * Optional columns added by the KPI-notes change. They are filtered out
     * automatically if the migration hasn't been applied yet, so the code is
     * safe to deploy in either order (schema-first or code-first) and a
     * missed/rolled-back migration can never break KPI saving.
     */
    private const OPTIONAL_COLUMNS = ['ot_notes', 'time_off_notes', 'coverage_notes', 'staffing_notes'];

    /** Cache of which optional columns actually exist on this database. */
    private static ?array $availableOptional = null;

    private static function availableOptionalColumns(): array
    {
        if (self::$availableOptional !== null) return self::$availableOptional;

        $found = [];
        try {
            $db = Database::connection();
            // NOTE: `SHOW COLUMNS ... LIKE ?` cannot take a bound parameter on
            // MariaDB, so this uses information_schema, which can.
            $placeholders = implode(',', array_fill(0, count(self::OPTIONAL_COLUMNS), '?'));
            $stmt = $db->prepare(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'kpi_scheduling_entries'
                    AND COLUMN_NAME IN ($placeholders)"
            );
            $stmt->execute(self::OPTIONAL_COLUMNS);
            $present = $stmt->fetchAll(\PDO::FETCH_COLUMN);
            // Preserve the canonical order rather than whatever the DB returns.
            foreach (self::OPTIONAL_COLUMNS as $col) {
                if (in_array($col, $present, true)) $found[] = $col;
            }
        } catch (\Throwable $e) {
            $found = [];
        }

        return self::$availableOptional = $found;
    }

    private static function fieldColumns(): array
    {
        return array_merge([
            'total_hours','static_hours','patrol_hours','event_hours','ot_hours','ot_pct','dt_hours','dt_pct',
            'open_post_hours','new_hires','terminations','open_requisition_hours','open_sites',
            'guard_inspections','failed_inspections','incidents','avg_response_hours','client_complaints',
            'employee_complaints','pto_requests','call_off_hours','scheduled_hours','audit_hours',
            'backup_employees','cesar_training','cesar_training_details','learning_development',
            'employee_concerns','remarks',
        ], self::availableOptionalColumns());
    }

    public static function saveDraft(array $user, int $departmentId, ?int $projectId, string $weekEnding, array $fields, ?int $existingId): int
    {
        $db = Database::connection();
        $cols = self::fieldColumns();

        // Guard against the unique (submitter, week) constraint: if the caller
        // didn't pass an existing id (e.g. "+ New Weekly KPI" was clicked while
        // a draft/returned entry already exists for this week), look it up
        // ourselves and update that row instead of attempting a duplicate INSERT.
        if (!$existingId) {
            $existing = self::findDraftOrEntry($user['user_type'], $user['user_id'], $weekEnding);
            if ($existing) {
                if (!in_array($existing['status'], ['draft', 'returned'], true)) {
                    // A non-editable entry (submitted/reviewed/approved/locked) already
                    // exists for this week. Don't silently overwrite it — surface a
                    // clear error instead of letting the DB throw a fatal constraint
                    // violation.
                    throw new \RuntimeException(
                        'A KPI entry for week ending ' . $weekEnding . ' already exists with status "' .
                        $existing['status'] . '". Please edit that entry from My KPI Records instead of starting a new one.'
                    );
                }
                $existingId = (int)$existing['id'];
            }
        }

        if ($existingId) {
            $set = implode(', ', array_map(fn($c) => "`$c` = :$c", $cols));
            $stmt = $db->prepare("UPDATE kpi_scheduling_entries SET {$set}, `week_starting` = :week_starting WHERE id = :id");
            $params = self::pickFields($fields, $cols);
            $params['week_starting'] = $fields['week_starting'] ?? null;
            $params['id'] = $existingId;
            $stmt->execute($params);
            $id = $existingId;
        } else {
            $cols2 = array_merge(['submitter_type', 'submitter_id', 'submitter_name', 'department_id', 'project_id', 'week_ending', 'week_starting', 'status'], $cols);
            $placeholders = implode(', ', array_map(fn($c) => ":$c", $cols2));
            $colList = implode(', ', array_map(fn($c) => "`$c`", $cols2));
            $stmt = $db->prepare("INSERT INTO kpi_scheduling_entries ({$colList}) VALUES ({$placeholders})");
            $params = self::pickFields($fields, $cols);
            $params['submitter_type'] = $user['user_type'];
            $params['submitter_id'] = $user['user_id'];
            $params['submitter_name'] = $user['name'];
            $params['department_id'] = $departmentId;
            $params['project_id'] = $projectId;
            $params['week_ending'] = $weekEnding;
            $params['week_starting'] = $fields['week_starting'] ?? null;
            $params['status'] = 'draft';
            $stmt->execute($params);
            $id = (int)$db->lastInsertId();
        }

        self::audit($id, $user, 'draft_saved');
        return $id;
    }

    private static function pickFields(array $fields, array $cols): array
    {
        // Columns that must be numeric (decimal/int) in the DB. Any value that
        // isn't a valid number (e.g. a stray '—' placeholder from the UI, or
        // any other non-numeric junk) is coerced to null rather than being
        // sent to the database, which would otherwise throw a fatal SQL error
        // (SQLSTATE[22007]) and lose the whole submission.
        $numericCols = [
            'total_hours', 'static_hours', 'patrol_hours', 'event_hours',
            'ot_hours', 'ot_pct', 'dt_hours', 'dt_pct',
        ];

        $out = [];
        foreach ($cols as $c) {
            $v = $fields[$c] ?? null;
            if ($v === '') {
                $out[$c] = null;
                continue;
            }
            if (in_array($c, $numericCols, true) && $v !== null && !is_numeric($v)) {
                $out[$c] = null;
                continue;
            }
            $out[$c] = $v;
        }
        return $out;
    }

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

    public static function review(int $id, array $reviewer, string $comment): void
    {
        $db = Database::connection();
        $db->prepare(
            "UPDATE kpi_scheduling_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,
        ]);
        self::audit($id, $reviewer, 'reviewed', $comment);
    }

    public static function approve(int $id, array $reviewer, int $score = 0, string $reviewComment = ''): void
    {
        $db = Database::connection();
        $db->prepare(
            "UPDATE kpi_scheduling_entries SET status='locked', approved_at=NOW(), locked_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,
        ]);
        self::audit($id, $reviewer, 'approved', $score ? "Score: {$score}/10. {$reviewComment}" : null);
        self::audit($id, $reviewer, 'locked');
    }

    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 kpi_scheduling_entries WHERE id=:id");
        $current->execute(['id' => $id]);
        $old = $current->fetch(\PDO::FETCH_ASSOC);

        $db->prepare(
            "UPDATE kpi_scheduling_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 ('scheduling',: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'],
        ]);
        self::audit($id, $reviewer, 'reviewed', "Score changed to {$newScore}/10. Reason: {$reason}");
    }

    public static function reviewHistory(int $id): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT * FROM kpi_review_history WHERE kpi_type='scheduling' 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 kpi_scheduling_entries SET status='returned', returned_at=NOW(), return_reason=:r WHERE id=:id"
        )->execute(['r' => $reason, 'id' => $id]);
        self::audit($id, $reviewer, 'returned', $reason);
    }

    public static function unlock(int $id, array $superAdmin, string $reason): void
    {
        $db = Database::connection();
        $db->prepare(
            "UPDATE kpi_scheduling_entries SET status='returned', unlock_reason=:r,
             unlocked_by_type='superadmin', unlocked_by_id=:uid, unlocked_by_name=:un, unlocked_at=NOW()
             WHERE id=:id"
        )->execute(['r' => $reason, 'uid' => $superAdmin['user_id'], 'un' => $superAdmin['name'], 'id' => $id]);
        self::audit($id, $superAdmin, 'unlocked', $reason);
    }

    public static function auditHistory(int $id): array
    {
        $stmt = Database::connection()->prepare("SELECT * FROM kpi_entry_audit WHERE kpi_entry_id = :id ORDER BY created_at ASC");
        $stmt->execute(['id' => $id]);
        return $stmt->fetchAll();
    }

    private static function audit(int $id, array $actor, string $action, ?string $reason = null): void
    {
        Database::connection()->prepare(
            "INSERT INTO kpi_entry_audit (kpi_entry_id, actor_type, actor_id, actor_name, action, reason)
             VALUES (:eid, :at, :aid, :an, :ac, :r)"
        )->execute([
            'eid' => $id,
            'at' => $actor['is_super_admin'] ? 'superadmin' : $actor['user_type'],
            'aid' => $actor['user_id'], 'an' => $actor['name'], 'ac' => $action, 'r' => $reason,
        ]);
    }
}
