<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;
use PDO;

final class Admission
{
    public static function create(array $data): int
    {
        $sql = 'INSERT INTO admissions (
            consent, race, unit_name, job_title, full_name, father_name, mother_name, birth_date, marital_status,
            is_student, education_level, nationality, place_of_birth, personal_email, phone, emergency_contact,
            address_line, address_number, neighborhood, zip_code, city, address_extra, state,
            rg_number, rg_issue_date, rg_issuer, birth_city, birth_state, cpf, pis_number, pis_issue_date,
            has_dependents, dependent_has_disability, uses_transport_voucher, transport_voucher_daily_value,
            uniform_size, cref_number, professor_mei_cnpj, employment_function, admission_date, dismissal_date, workload_hours, salary_amount, salary_type,
            bank_agency, bank_account, status, internal_notes, lgpd_consent_at, submitted_ip, created_at, updated_at
        ) VALUES (
            :consent, :race, :unit_name, :job_title, :full_name, :father_name, :mother_name, :birth_date, :marital_status,
            :is_student, :education_level, :nationality, :place_of_birth, :personal_email, :phone, :emergency_contact,
            :address_line, :address_number, :neighborhood, :zip_code, :city, :address_extra, :state,
            :rg_number, :rg_issue_date, :rg_issuer, :birth_city, :birth_state, :cpf, :pis_number, :pis_issue_date,
            :has_dependents, :dependent_has_disability, :uses_transport_voucher, :transport_voucher_daily_value,
            :uniform_size, :cref_number, :professor_mei_cnpj, :employment_function, :admission_date, :dismissal_date, :workload_hours, :salary_amount, :salary_type,
            :bank_agency, :bank_account, :status, :internal_notes, :lgpd_consent_at, :submitted_ip, NOW(), NOW()
        )';
        $st = Database::connection()->prepare($sql);
        $st->execute($data);
        return (int) Database::connection()->lastInsertId();
    }

    public static function update(int $id, array $data): void
    {
        $data['id'] = $id;
        $sql = 'UPDATE admissions SET
            race=:race, unit_name=:unit_name, job_title=:job_title, full_name=:full_name, father_name=:father_name, mother_name=:mother_name,
            birth_date=:birth_date, marital_status=:marital_status, is_student=:is_student, education_level=:education_level,
            nationality=:nationality, place_of_birth=:place_of_birth, personal_email=:personal_email, phone=:phone,
            emergency_contact=:emergency_contact, address_line=:address_line, address_number=:address_number, neighborhood=:neighborhood,
            zip_code=:zip_code, city=:city, address_extra=:address_extra, state=:state, rg_number=:rg_number,
            rg_issue_date=:rg_issue_date, rg_issuer=:rg_issuer, birth_city=:birth_city, birth_state=:birth_state,
            cpf=:cpf, pis_number=:pis_number, pis_issue_date=:pis_issue_date, has_dependents=:has_dependents,
            dependent_has_disability=:dependent_has_disability, uses_transport_voucher=:uses_transport_voucher,
            transport_voucher_daily_value=:transport_voucher_daily_value, uniform_size=:uniform_size,
            cref_number=:cref_number, professor_mei_cnpj=:professor_mei_cnpj, employment_function=:employment_function, admission_date=:admission_date, dismissal_date=:dismissal_date,
            workload_hours=:workload_hours, salary_amount=:salary_amount, salary_type=:salary_type,
            bank_agency=:bank_agency, bank_account=:bank_account, status=:status, internal_notes=:internal_notes, updated_at=NOW()
            WHERE id=:id';
        $st = Database::connection()->prepare($sql);
        $st->execute($data);
    }

    public static function updateStatus(int $id, string $status): void
    {
        $st = Database::connection()->prepare('UPDATE admissions SET status = :status, updated_at = NOW() WHERE id = :id');
        $st->execute([
            'id' => $id,
            'status' => $status,
        ]);
    }

    public static function find(int $id): ?array
    {
        $st = Database::connection()->prepare('SELECT * FROM admissions WHERE id = :id LIMIT 1');
        $st->execute(['id' => $id]);
        return $st->fetch() ?: null;
    }

    public static function delete(int $id): void
    {
        $st = Database::connection()->prepare('DELETE FROM admissions WHERE id = :id');
        $st->execute(['id' => $id]);
    }

    public static function findWithRelations(int $id): ?array
    {
        $admission = self::find($id);
        if (!$admission) return null;
        $admission['dependents'] = Dependent::forAdmission($id);
        $admission['academic'] = AcademicInfo::forAdmission($id);
        $admission['attachments'] = Attachment::forAdmission($id);
        return $admission;
    }
    
    public static function existsByCpf(string $cpf): bool
    {
        $st = Database::connection()->prepare('SELECT id FROM admissions WHERE cpf = :cpf LIMIT 1');
        $st->execute(['cpf' => preg_replace('/\D/', '', $cpf)]);
        return (bool) $st->fetchColumn();
    }

    public static function existsByCpfExcept(string $cpf, int $ignoreId): bool
    {
        $st = Database::connection()->prepare('SELECT id FROM admissions WHERE cpf = :cpf AND id != :id LIMIT 1');
        $st->execute([
            'cpf' => preg_replace('/\D/', '', $cpf),
            'id' => $ignoreId,
        ]);
        return (bool) $st->fetchColumn();
    }

    public static function search(array $filters = []): array
    {
        $sql = 'SELECT * FROM admissions WHERE 1=1';
        $bindings = [];
        if (!empty($filters['search'])) {
            $sql .= ' AND (full_name LIKE :search OR cpf LIKE :search OR unit_name LIKE :search OR job_title LIKE :search OR employment_function LIKE :search)';
            $bindings['search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['status'])) {
            $sql .= ' AND status = :status';
            $bindings['status'] = $filters['status'];
        }
        if (!empty($filters['date_from'])) {
            $sql .= ' AND DATE(created_at) >= :date_from';
            $bindings['date_from'] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $sql .= ' AND DATE(created_at) <= :date_to';
            $bindings['date_to'] = $filters['date_to'];
        }
        $sql .= ' ORDER BY created_at DESC';
        $st = Database::connection()->prepare($sql);
        $st->execute($bindings);
        return $st->fetchAll(PDO::FETCH_ASSOC);
    }
}
