<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;
use PDOException;

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
    {
        $sql = 'SELECT * FROM admissions WHERE 1=1';
        $bindings = [];

        $status = trim((string) ($filters['status'] ?? ''));
        if ($status !== '' && self::hasColumn('status')) {
            $sql .= ' AND status = :status';
            $bindings['status'] = $status;
        }

        $dateFrom = trim((string) ($filters['date_from'] ?? ''));
        if ($dateFrom !== '' && self::hasColumn('created_at')) {
            $sql .= ' AND DATE(created_at) >= :date_from';
            $bindings['date_from'] = $dateFrom;
        }

        $dateTo = trim((string) ($filters['date_to'] ?? ''));
        if ($dateTo !== '' && self::hasColumn('created_at')) {
            $sql .= ' AND DATE(created_at) <= :date_to';
            $bindings['date_to'] = $dateTo;
        }

        if (self::hasColumn('created_at')) {
            $sql .= ' ORDER BY created_at DESC';
        } elseif (self::hasColumn('id')) {
            $sql .= ' ORDER BY id DESC';
        }

        $st = Database::connection()->prepare($sql);
        $st->execute($bindings);
        $rows = $st->fetchAll();

        $search = trim((string) ($filters['search'] ?? ''));
        if ($search === '') {
            return $rows;
        }

        return array_values(array_filter($rows, static fn (array $row): bool => self::rowMatchesSearch($row, $search)));
    }

    private static function rowMatchesSearch(array $row, string $search): bool
    {
        $terms = self::searchTerms($search);
        if ($terms === []) {
            return true;
        }

        $textHaystack = self::normalizeSearchText(implode(' ', array_filter([
            (string) ($row['full_name'] ?? ''),
            (string) ($row['personal_email'] ?? ''),
            (string) ($row['phone'] ?? ''),
            (string) ($row['cpf'] ?? ''),
            (string) ($row['unit_name'] ?? ''),
            (string) ($row['job_title'] ?? ''),
            (string) ($row['employment_function'] ?? ''),
            (string) ($row['status'] ?? ''),
            '#' . (string) ($row['id'] ?? ''),
        ])));

        $digitsHaystack = implode(' ', array_filter([
            self::digitsOnly((string) ($row['id'] ?? '')),
            self::digitsOnly((string) ($row['cpf'] ?? '')),
            self::digitsOnly((string) ($row['phone'] ?? '')),
        ]));

        foreach ($terms as $term) {
            $matched = str_contains($textHaystack, $term);

            if (!$matched) {
                $termDigits = self::digitsOnly($term);
                if ($termDigits !== '' && str_contains($digitsHaystack, $termDigits)) {
                    $matched = true;
                }
            }

            if (!$matched) {
                return false;
            }
        }

        return true;
    }

    private static function searchTerms(string $search): array
    {
        $normalized = self::normalizeSearchText($search);
        if ($normalized === '') {
            return [];
        }

        return array_values(array_filter(preg_split('/\s+/u', $normalized) ?: []));
    }

    private static function normalizeSearchText(string $value): string
    {
        $value = trim($value);
        if ($value === '') {
            return '';
        }

        if (function_exists('mb_strtolower')) {
            $value = mb_strtolower($value, 'UTF-8');
        } else {
            $value = strtolower($value);
        }

        $transliterated = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
        if ($transliterated !== false) {
            $value = $transliterated;
        }

        $value = preg_replace('/[^a-z0-9@._#\-\s]+/i', ' ', $value) ?? $value;
        $value = preg_replace('/\s+/u', ' ', $value) ?? $value;

        return trim($value);
    }

    private static function digitsOnly(string $value): string
    {
        return preg_replace('/\D/', '', $value) ?? '';
    }

    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
    {
        return isset(self::loadColumns()[$column]);
    }

    private static function loadColumns(): array
    {
        if (self::$columns !== null) {
            return self::$columns;
        }

        $loadedColumns = self::loadColumnsFromDatabase();

        if ($loadedColumns === []) {
            $loadedColumns = self::loadColumnsFromSchemaFile();
        }

        if ($loadedColumns === []) {
            foreach ([
                'id', 'full_name', 'personal_email', 'phone', 'cpf', 'unit_name', 'job_title',
                'status', 'created_at', 'bank_agency', 'bank_account'
            ] as $field) {
                $loadedColumns[$field] = true;
            }
        }

        self::$columns = $loadedColumns;
        return self::$columns;
    }

    private static function loadColumnsFromDatabase(): array
    {
        $loadedColumns = [];

        try {
            $st = Database::connection()->query('SHOW COLUMNS FROM admissions');
            foreach ($st->fetchAll() as $row) {
                $field = (string) ($row['Field'] ?? $row['COLUMN_NAME'] ?? '');
                if ($field !== '') {
                    $loadedColumns[$field] = true;
                }
            }
        } catch (PDOException) {
            return [];
        }

        return $loadedColumns;
    }

    private static function loadColumnsFromSchemaFile(): array
    {
        $schemaPath = dirname(__DIR__, 2) . '/database/schema.sql';
        if (!is_file($schemaPath)) {
            return [];
        }

        $schema = (string) file_get_contents($schemaPath);
        if ($schema === '') {
            return [];
        }

        if (!preg_match('/CREATE TABLE IF NOT EXISTS admissions\s*\((.*?)\) ENGINE=/si', $schema, $matches)) {
            return [];
        }

        $loadedColumns = [];
        foreach (preg_split('/\R/', $matches[1]) ?: [] as $line) {
            $line = trim($line);
            if ($line === '' || str_starts_with($line, 'PRIMARY KEY') || str_starts_with($line, 'INDEX ') || str_starts_with($line, 'UNIQUE KEY') || str_starts_with($line, 'CONSTRAINT ')) {
                continue;
            }

            if (preg_match('/^`?([a-zA-Z0-9_]+)`?\s+/u', $line, $columnMatch)) {
                $loadedColumns[$columnMatch[1]] = true;
            }
        }

        return $loadedColumns;
    }
}
