<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;
use PDO;

final class Admission
{
    private static ?array $columns = null;

    public static function create(array $data): int
    {
        $data = self::normalizePaymentDataForPersistence($data);

        $fields = [
            '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',
            'status', 'internal_notes', 'lgpd_consent_at', 'submitted_ip'
        ];

        if (self::hasColumn('bank_payment_method')) {
            $fields[] = 'bank_payment_method';
        }

        $fields[] = 'bank_agency';
        $fields[] = 'bank_account';

        if (self::hasColumn('pix_key')) {
            $fields[] = 'pix_key';
        }

        $columns = implode(', ', $fields) . ', created_at, updated_at';
        $placeholders = implode(', ', array_map(static fn (string $field): string => ':' . $field, $fields)) . ', NOW(), NOW()';

        $sql = "INSERT INTO admissions ({$columns}) VALUES ({$placeholders})";
        $st = Database::connection()->prepare($sql);
        $st->execute(self::filterDataForFields($data, $fields));
        return (int) Database::connection()->lastInsertId();
    }

    public static function update(int $id, array $data): void
    {
        $data = self::normalizePaymentDataForPersistence($data);
        $data['id'] = $id;

        $fields = [
            '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',
            'status', 'internal_notes'
        ];

        if (self::hasColumn('bank_payment_method')) {
            $fields[] = 'bank_payment_method';
        }

        $fields[] = 'bank_agency';
        $fields[] = 'bank_account';

        if (self::hasColumn('pix_key')) {
            $fields[] = 'pix_key';
        }

        $assignments = implode(",
            ", array_map(static fn (string $field): string => "{$field}=:{$field}", $fields));
        $sql = "UPDATE admissions SET
            {$assignments}, updated_at=NOW()
            WHERE id=:id";
        $st = Database::connection()->prepare($sql);
        $st->execute(self::filterDataForFields($data, array_merge($fields, ['id'])));
    }

    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
    {
        $summaryColumns = ['id', 'full_name', 'personal_email', 'cpf', 'unit_name', 'job_title', 'status', 'created_at'];
        $sql = 'SELECT ' . implode(', ', $summaryColumns) . ' FROM admissions WHERE 1=1';
        $bindings = [];
        if (!empty($filters['search'])) {
            $searchColumns = ['full_name', 'cpf', 'unit_name', 'job_title'];
            if (self::hasColumn('employment_function')) {
                $searchColumns[] = 'employment_function';
            }
            $likeClauses = array_map(static fn (string $column): string => $column . ' LIKE :search', $searchColumns);
            $sql .= ' AND (' . implode(' OR ', $likeClauses) . ')';
            $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();
    }

    private static function filterDataForFields(array $data, array $fields): array
    {
        $filtered = [];
        foreach ($fields as $field) {
            $filtered[$field] = $data[$field] ?? null;
        }
        return $filtered;
    }

    private static function normalizePaymentDataForPersistence(array $data): array
    {
        $method = ($data['bank_payment_method'] ?? 'bank') === 'pix' ? 'pix' : 'bank';

        if (self::hasColumn('bank_payment_method') && self::hasColumn('pix_key')) {
            $data['bank_payment_method'] = $method;
            if ($method === 'pix') {
                $data['bank_agency'] = null;
                $data['bank_account'] = null;
            } else {
                $data['pix_key'] = null;
            }
            return $data;
        }

        if ($method === 'pix') {
            $pixKey = trim((string) ($data['pix_key'] ?? ''));
            $data['bank_agency'] = $pixKey !== '' ? 'PIX' : null;
            $data['bank_account'] = $pixKey !== '' ? $pixKey : null;
        }

        unset($data['bank_payment_method'], $data['pix_key']);
        return $data;
    }

    private static function hasColumn(string $column): bool
    {
        if (self::$columns === null) {
            $dbName = (string) Database::connection()->query('SELECT DATABASE()')->fetchColumn();
            $st = Database::connection()->prepare('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table');
            $st->execute([
                'schema' => $dbName,
                'table' => 'admissions',
            ]);
            self::$columns = array_fill_keys(array_map(static fn (array $row): string => (string) $row['COLUMN_NAME'], $st->fetchAll()), true);
        }

        return isset(self::$columns[$column]);
    }
}
