<?php

namespace App\Models;

use App\Core\Database;

class Lookup
{
    public static function departments(): array
    {
        return Database::connection()
            ->query("SELECT id, name, code, is_active FROM departments ORDER BY name")
            ->fetchAll();
    }

    public static function projectsByDepartment(): array
    {
        $rows = Database::connection()
            ->query("SELECT p.id, p.department_id, p.name, p.code, p.status, d.name AS department_name
                     FROM projects p JOIN departments d ON d.id = p.department_id
                     ORDER BY d.name, p.name")
            ->fetchAll();

        $grouped = [];
        foreach ($rows as $r) {
            $grouped[$r['department_id']][] = $r;
        }
        return $grouped;
    }

    public static function roles(): array
    {
        // Super Admin excluded — assigned only via direct DB access, not through this panel
        return Database::connection()
            ->query("SELECT id, code, name, level FROM roles WHERE code != 'super_admin' ORDER BY level ASC")
            ->fetchAll();
    }

    /** Managers already imported (e.g. from Google Sheets) who don't have login access set up yet. */
    public static function availableManagers(): array
    {
        return Database::connection()
            ->query("SELECT id, name, email, dept FROM managers WHERE username IS NULL ORDER BY name")
            ->fetchAll();
    }

    /**
     * ALL imported managers (whether or not they already have login access),
     * for display only in the "Select Manager" list on the Grant Workspace
     * Access modal, so the Super Admin can see the full roster. Each row
     * includes `has_access` so the UI can show/disable ones who already have
     * a login. This is intentionally separate from availableManagers() above,
     * which bulkGenerateCredentials() relies on staying limited to managers
     * WITHOUT access — granting/regenerating credentials for someone who
     * already has access would overwrite their existing username/password.
     */
    public static function allManagersForDisplay(): array
    {
        return Database::connection()
            ->query("SELECT id, name, email, dept, (username IS NOT NULL) AS has_access FROM managers ORDER BY name")
            ->fetchAll();
    }

    /** Employees already imported who don't have login access set up yet. */
    public static function availableEmployees(): array
    {
        if (!\App\Core\Features::employeeOnboardingEnabled()) {
            return [];
        }
        return Database::connection()
            ->query("SELECT id, name, email FROM employees WHERE username IS NULL ORDER BY name")
            ->fetchAll();
    }

    /**
     * Everyone eligible to be selected as a "Reports To" (reporting manager):
     * all managers with login access, plus superadmins. Employees cannot be
     * a reporting manager in this system.
     */
    public static function reportsToCandidates(): array
    {
        $db = Database::connection();
        return $db->query(
            "SELECT id, full_name AS name, 'superadmin' AS user_type FROM superadmins
             UNION ALL
             SELECT id, name, 'manager' AS user_type FROM managers WHERE username IS NOT NULL
             ORDER BY user_type, name"
        )->fetchAll();
    }

    /** Departments a specific manager/employee is assigned to (with project counts). */
    public static function departmentsForUser(string $userType, int $userId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT d.*, (SELECT COUNT(*) FROM projects p WHERE p.department_id = d.id) AS project_count
             FROM departments d
             JOIN user_departments ud ON ud.department_id = d.id
             WHERE ud.user_type = :t AND ud.user_id = :id AND d.is_active = 1
             ORDER BY d.name"
        );
        $stmt->execute(['t' => $userType, 'id' => $userId]);
        return $stmt->fetchAll();
    }

    /** Projects a specific manager/employee is assigned to, within one department. */
    public static function projectsForUserInDepartment(string $userType, int $userId, int $departmentId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT p.* FROM projects p
             JOIN user_projects up ON up.project_id = p.id
             WHERE up.user_type = :t AND up.user_id = :id AND p.department_id = :d
             ORDER BY p.name"
        );
        $stmt->execute(['t' => $userType, 'id' => $userId, 'd' => $departmentId]);
        return $stmt->fetchAll();
    }

    /** All projects a user is assigned to across every department, for the Workspace Home summary. */
    public static function allProjectsForUser(string $userType, int $userId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT p.*, d.name AS department_name FROM projects p
             JOIN user_projects up ON up.project_id = p.id
             JOIN departments d ON d.id = p.department_id
             WHERE up.user_type = :t AND up.user_id = :id
             ORDER BY p.name"
        );
        $stmt->execute(['t' => $userType, 'id' => $userId]);
        return $stmt->fetchAll();
    }

    /** All projects across all departments — for super admin */
    public static function allProjects(): array
    {
        return Database::connection()
            ->query("SELECT p.*, d.name AS department_name FROM projects p
                     JOIN departments d ON d.id = p.department_id
                     ORDER BY d.name, p.name")
            ->fetchAll();
    }

    /**
     * Does this user have access to ANY project with the given kpi_form_type?
     * Used to determine whether to show a KPI form without hardcoding usernames.
     */
    public static function userHasKpiFormType(string $userType, int $userId, string $formType): bool
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT 1 FROM user_projects up
                 JOIN projects p ON p.id = up.project_id
                 WHERE up.user_type = :t AND up.user_id = :id
                   AND p.kpi_form_type = :ft AND p.status = 'active'
                 LIMIT 1"
            );
            $stmt->execute(['t' => $userType, 'id' => $userId, 'ft' => $formType]);
            return (bool)$stmt->fetch();
        } catch (\Throwable $e) {
            // Column doesn't exist yet — fail safe, fall back to username checks
            return false;
        }
    }

    /**
     * Get all active projects of a given kpi_form_type that a user is assigned to.
     */
    public static function userKpiProjects(string $userType, int $userId, string $formType): array
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT p.* FROM user_projects up
                 JOIN projects p ON p.id = up.project_id
                 WHERE up.user_type = :t AND up.user_id = :id
                   AND p.kpi_form_type = :ft AND p.status = 'active'
                 ORDER BY p.name"
            );
            $stmt->execute(['t' => $userType, 'id' => $userId, 'ft' => $formType]);
            return $stmt->fetchAll();
        } catch (\Throwable $e) {
            return [];
        }
    }
}
