<?php

namespace App\Models;

class StaffProfile extends BaseModel
{
    public function findByUserId(int $userId): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM staff_profiles WHERE user_id = :user_id LIMIT 1');
        $stmt->execute(['user_id' => $userId]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): void
    {
        $stmt = $this->pdo->prepare('INSERT INTO staff_profiles 
            (user_id, ic_no, address, phone, hire_date, location, bank_name, account_no, allowed_leave) 
            VALUES (:user_id, :ic_no, :address, :phone, :hire_date, :location, :bank_name, :account_no, :allowed_leave)');

        $stmt->execute([
            'user_id' => $data['user_id'],
            'ic_no' => $data['ic_no'],
            'address' => $data['address'] ?? null,
            'phone' => $data['phone'] ?? null,
            'hire_date' => $data['hire_date'] ?? null,
            'location' => $data['location'] ?? null,
            'bank_name' => $data['bank_name'] ?? null,
            'account_no' => $data['account_no'] ?? null,
            'allowed_leave' => $data['allowed_leave'] ?? 0,
        ]);
    }

    public function allWithUser(?string $locationFilter = null): array
    {
        $sql = 'SELECT u.id AS user_id, u.name, u.login_id, u.email, u.active,
                       sp.ic_no, sp.address, sp.phone, sp.hire_date, sp.location, sp.bank_name, sp.account_no,
                       sp.allowed_leave,
                       COALESCE(SUM(CASE WHEN lr.status = \'approved\' THEN lr.jumlah_hari ELSE 0 END), 0) AS used_leave
                FROM staff_profiles sp
                JOIN users u ON u.id = sp.user_id
                LEFT JOIN leave_requests lr ON lr.user_id = u.id AND lr.status = \'approved\'';
        $params = [];
        if ($locationFilter) {
            $sql .= ' WHERE sp.location = :location';
            $params['location'] = $locationFilter;
        }
        $sql .= ' GROUP BY sp.id, u.id ORDER BY u.name ASC';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function countAll(): int
    {
        $stmt = $this->pdo->query('SELECT COUNT(*) FROM staff_profiles');
        return (int) $stmt->fetchColumn();
    }

    public function countActive(): int
    {
        $stmt = $this->pdo->query("SELECT COUNT(*) 
            FROM staff_profiles sp JOIN users u ON u.id = sp.user_id WHERE u.active = 1");
        return (int) $stmt->fetchColumn();
    }

    public function updateByUserId(int $userId, array $data): void
    {
        $stmt = $this->pdo->prepare('UPDATE staff_profiles 
            SET address = :address, phone = :phone, hire_date = :hire_date, location = :location, 
                bank_name = :bank_name, account_no = :account_no, allowed_leave = :allowed_leave 
            WHERE user_id = :user_id');
        $stmt->execute([
            'address' => $data['address'] ?? null,
            'phone' => $data['phone'] ?? null,
            'hire_date' => $data['hire_date'] ?? null,
            'location' => $data['location'] ?? null,
            'bank_name' => $data['bank_name'] ?? null,
            'account_no' => $data['account_no'] ?? null,
            'allowed_leave' => $data['allowed_leave'] ?? 0,
            'user_id' => $userId,
        ]);
    }

    public function deleteByUserId(int $userId): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM staff_profiles WHERE user_id = :user_id');
        $stmt->execute(['user_id' => $userId]);
    }

    public function allLocations(): array
    {
        $stmt = $this->pdo->query('SELECT DISTINCT location FROM staff_profiles WHERE location IS NOT NULL AND location <> \'\' ORDER BY location ASC');
        return array_column($stmt->fetchAll(), 'location');
    }

    public function userIdsByLocation(string $location): array
    {
        $stmt = $this->pdo->prepare('SELECT user_id FROM staff_profiles WHERE location = :location');
        $stmt->execute(['location' => $location]);
        return array_map('intval', array_column($stmt->fetchAll(), 'user_id'));
    }

    public function getLeaveStats(int $userId): array
    {
        // Get allowed leave from staff profile
        $profile = $this->findByUserId($userId);
        $allowedLeave = $profile['allowed_leave'] ?? 0;

        // Calculate used leave (sum of approved leave requests)
        $stmt = $this->pdo->prepare(
            'SELECT COALESCE(SUM(jumlah_hari), 0) FROM leave_requests 
             WHERE user_id = :user_id AND status = \'approved\''
        );
        $stmt->execute(['user_id' => $userId]);
        $usedLeave = (int) $stmt->fetchColumn();

        // Calculate remaining leave
        $remainingLeave = max(0, $allowedLeave - $usedLeave);

        return [
            'allowed' => $allowedLeave,
            'used' => $usedLeave,
            'remaining' => $remainingLeave,
        ];
    }
}

