<?php

namespace Modules\Employees\Models;

use core\Model;
use core\Database;

class Employee extends Model
{
    protected string $table = 'employees';
    protected string $primaryKey = 'id';
    protected bool $timestamps = true;

    protected array $fillable = [
        'user_id', 'branch_id', 'first_name', 'last_name', 'email', 'phone', 'mobile',
        'date_of_birth', 'gender', 'address', 'city', 'state', 'zip', 'photo',
        'job_title', 'hire_date', 'salary_type', 'salary_amount', 'hourly_rate',
        'commission_percent', 'target_amount', 'emergency_contact', 'emergency_phone',
        'notes', 'is_active',
    ];

    protected array $guarded = ['id'];

    public function getWithUser(): array
    {
        $db = Database::getInstance();
        return $db->table('employees e')
            ->select('e.*, u.email as user_email, u.username')
            ->leftJoin('users u', 'e.user_id', '=', 'u.id')
            ->where('e.deleted_at', 'IS', null)
            ->orderBy('e.first_name', 'ASC')
            ->get();
    }

    public function getActive(): array
    {
        return $this->db->table($this->table)
            ->where('is_active', 1)
            ->where('deleted_at', 'IS', null)
            ->orderBy('first_name', 'ASC')
            ->get();
    }

    public function getByService(int $serviceId): array
    {
        $db = Database::getInstance();
        return $db->query(
            "SELECT DISTINCT e.*
             FROM employees e
             INNER JOIN employee_services es ON e.id = es.employee_id
             WHERE es.service_id = :service_id
               AND e.is_active = 1
               AND e.deleted_at IS NULL
             ORDER BY e.first_name ASC",
            [':service_id' => $serviceId]
        );
    }

    public function search(string $query): array
    {
        $searchTerm = '%' . $query . '%';
        return $this->db->table($this->table)
            ->where('deleted_at', 'IS', null)
            ->where(function ($q) use ($searchTerm) {
                $q->where('first_name', 'LIKE', $searchTerm)
                  ->orWhere('last_name', 'LIKE', $searchTerm)
                  ->orWhere('email', 'LIKE', $searchTerm)
                  ->orWhere('phone', 'LIKE', $searchTerm)
                  ->orWhere('mobile', 'LIKE', $searchTerm);
            })
            ->orderBy('first_name', 'ASC')
            ->limit(20)
            ->get();
    }

    public function getAvailableEmployees(string $date, string $time, string $serviceId = ''): array
    {
        $db = Database::getInstance();
        $dayOfWeek = date('N', strtotime($date));

        $where = 'e.is_active = 1 AND e.deleted_at IS NULL';
        $params = [':day_of_week' => $dayOfWeek];

        if (!empty($serviceId)) {
            $where .= ' AND EXISTS (
                SELECT 1 FROM employee_services es WHERE es.employee_id = e.id AND es.service_id = :service_id
            )';
            $params[':service_id'] = $serviceId;
        }

        if (!empty($time)) {
            $where .= ' AND EXISTS (
                SELECT 1 FROM employee_schedule esch
                WHERE esch.employee_id = e.id
                  AND esch.day_of_week = :day_of_week
                  AND esch.start_time <= :time
                  AND esch.end_time >= :time
            )';
            $params[':time'] = $time;
        }

        $where .= ' AND NOT EXISTS (
            SELECT 1 FROM employee_attendance a
            WHERE a.employee_id = e.id
              AND a.date = :att_date
              AND a.status = \'absent\'
        )';
        $params[':att_date'] = $date;

        return $db->query(
            "SELECT e.* FROM employees e WHERE {$where} ORDER BY e.first_name ASC",
            $params
        );
    }
}
