<?php
declare(strict_types=1);

namespace App\Modules\HR\Models;

use App\Core\Model;
use App\Core\App;
use App\Core\Pagination;

class HrEmployeeDocument extends Model
{
    protected static string $table = 'hr_employee_documents';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;
    protected static bool $autoTrackAuthor = true;

    protected static array $fillable = [
        'employee_profile_id', 'document_type', 'title_ar',
        'original_filename', 'stored_filename', 'file_path',
        'file_size', 'mime_type', 'expiry_date',
        'is_verified', 'verified_by', 'verified_at',
        'uploaded_by', 'notes', 'validity_status',
    ];

    public static function getDocumentTypes(): array
    {
        return [
            'national_id'          => 'بطاقة الرقم القومي',
            'passport'             => 'جواز سفر',
            'birth_certificate'    => 'شهادة ميلاد',
            'degree'               => 'شهادة علمية',
            'military_cert'        => 'شهادة تأدية/إعفاء من الخدمة العسكرية',
            'insurance_form1'      => 'استمارة تأمينات 1',
            'insurance_form6'      => 'استمارة تأمينات 6',
            'medical_report'       => 'تقرير طبي',
            'criminal_record'      => 'صحيفة حالة جنائية',
            'employment_cert'      => 'شهادة خبرة',
            'contract_copy'        => 'نسخة عقد',
            'photo'                => 'صورة شخصية',
            'work_stub'            => 'كعب العمل',
            'insurance_certificate'=> 'شهادة تأمينية',
            'other'                => 'أخرى',
        ];
    }

    public static function getForEmployee(int $profileId): array
    {
        return static::query()
            ->where('employee_profile_id', '=', $profileId)
            ->orderBy('created_at', 'DESC')
            ->get();
    }

    public static function getExpiringWithinDays(int $days): array
    {
        $db = App::getInstance()->db();
        $futureDate = date('Y-m-d', strtotime("+{$days} days"));
        return $db->select(
            "SELECT d.*, CONCAT(p.first_name_ar, ' ', p.last_name_ar) as full_name_ar, p.employee_number
             FROM hr_employee_documents d
             JOIN hr_employee_profiles p ON p.id = d.employee_profile_id
             WHERE d.expiry_date IS NOT NULL
               AND d.expiry_date <= ?
               AND d.expiry_date >= CURDATE()
               AND d.is_archived = 0
             ORDER BY d.expiry_date ASC",
            [$futureDate]
        );
    }

    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = 'd.is_archived = 0';
        $params = [];

        if (!empty($filters['employee_profile_id'])) {
            $where .= ' AND d.employee_profile_id = ?';
            $params[] = (int) $filters['employee_profile_id'];
        }
        if (!empty($filters['document_type'])) {
            $where .= ' AND d.document_type = ?';
            $params[] = $filters['document_type'];
        }
        if (isset($filters['is_verified']) && $filters['is_verified'] !== '') {
            $where .= ' AND d.is_verified = ?';
            $params[] = (int) $filters['is_verified'];
        }
        if (!empty($filters['validity_status'])) {
            $where .= ' AND d.validity_status = ?';
            $params[] = $filters['validity_status'];
        }

        $countRow = $db->selectOne(
            "SELECT COUNT(*) as cnt FROM hr_employee_documents d WHERE {$where}",
            $params
        );
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;

        $rows = $db->select(
            "SELECT d.*, CONCAT(p.first_name_ar, ' ', p.last_name_ar) as full_name_ar, p.employee_number
             FROM hr_employee_documents d
             JOIN hr_employee_profiles p ON p.id = d.employee_profile_id
             WHERE {$where}
             ORDER BY d.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return ['data' => $rows, 'pagination' => Pagination::paginate($total, $perPage, $page)];
    }
}
