<?php
declare(strict_types=1);

namespace App\Repositories;

final class CourtCaseRepository extends BaseRepository
{
    private const PARTY_PROCEDURAL_STATUSES = [
        'iddiaci',
        'cavabdeh',
        'musteqil_teleb_ireli_suren_ucuncu_mpm55',
        'musteqil_teleb_ireli_surmeyen_ucuncu_mpm57',
        'ucuncu_sexs_ipm28',
        'erizeci',
        'maraqli_sexs',
        'qetname_ile_maraqlarina_toxunulan',
        'teqsirlendirilen_sexs',
        'beraet_almis_sexs',
        'mehkum',
        'zerercekmish_sexs',
        'mulki_iddiaci',
        'mulki_cavabdeh',
        'inzibati_xeta_icraati_aparilan',
        'borclu',
        'telebkar',
    ];

    private const FINAL_ACT_OUTCOMES = [
        'temin_edildi' => 'Təmin edildi',
        'qismen_temin_edildi' => 'Qismən təmin edildi',
        'ohdelik_qoyulmasi_ipm_73' => 'Öhdəlik qoyuldu (İPM-73)',
        'redd_edildi' => 'Rədd edildi',
        'mumkunsuz_hesab_edildi' => 'Mümkünsüz hesab edildi',
        'baxilmamis_saxlanildi' => 'Baxılmamış saxlanıldı',
        'geri_qaytarildi' => 'Geri qaytarıldı',
        'ittihamedici_hokm' => 'İttihamedici hökm çıxarıldı',
        'beraetverici_hokm' => 'Bəraətverici hökm çıxarıldı',
        'icraata_xitam' => 'İcraata xitam verildi',
        'inzibati_tenbeh' => 'İnzibati tənbeh tətbiq edildi',
        'basqa_isle_birlestirildi' => 'Başqa işlə birləşdirildi',
        'baxisdan_cixarildi' => 'Baxışdan çıxarıldı',
    ];

    private const FINAL_ACT_SUCCESS = [
        'qalib_gelindi' => 'Qalib gəlindi',
        'meglub_olundu' => 'Məğlub olundu',
        'texniki_netice' => 'Texniki nəticə baş verdi',
    ];

    public function create(array $data): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO mehkeme_ishleri
             (is_nomresi, terefin_adi, terefin_prosessual_statusu, mehkemenin_adi, hakimin_adi, is_statusu, mehkeme_tarixi, qeydler, fin, url, folder, owner_user_id)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $stmt->execute([
            $data['is_nomresi'] ?? '',
            $data['terefin_adi'] ?? '',
            $this->normalizePartyProceduralStatus($data['terefin_prosessual_statusu'] ?? null),
            $data['mehkemenin_adi'] ?? '',
            $data['hakimin_adi'] ?? '',
            $data['is_statusu'] ?? 'İcraatdadır',
            ($data['mehkeme_tarixi'] ?? '') ?: null,
            $data['qeydler'] ?? '',
            ($data['fin'] ?? '') ?: null,
            $data['url'] ?? '',
            $data['folder'] ?? '',
            $this->ownerUserId(),
        ]);

        return (int)$this->db->lastInsertId();
    }

    public function update(array $data): void
    {
        $stmt = $this->db->prepare(
            'UPDATE mehkeme_ishleri
             SET is_nomresi=?, terefin_adi=?, terefin_prosessual_statusu=COALESCE(?, terefin_prosessual_statusu), mehkemenin_adi=?, hakimin_adi=?, is_statusu=?,
                 mehkeme_tarixi=?, qeydler=?, fin=?, url=?, folder=?
             WHERE id=? AND owner_user_id=?'
        );
        $stmt->execute([
            $data['is_nomresi'] ?? '',
            $data['terefin_adi'] ?? '',
            $this->normalizePartyProceduralStatus($data['terefin_prosessual_statusu'] ?? null),
            $data['mehkemenin_adi'] ?? '',
            $data['hakimin_adi'] ?? '',
            $data['is_statusu'] ?? 'İcraatdadır',
            ($data['mehkeme_tarixi'] ?? '') ?: null,
            $data['qeydler'] ?? '',
            ($data['fin'] ?? '') ?: null,
            $data['url'] ?? '',
            $data['folder'] ?? '',
            (int)$data['id'],
            $this->ownerUserId(),
        ]);
    }

    public function setStatus(int $id, string $status): void
    {
        $stmt = $this->db->prepare('UPDATE mehkeme_ishleri SET is_statusu=? WHERE id=? AND owner_user_id=?');
        $stmt->execute([$status, $id, $this->ownerUserId()]);
    }

    private function normalizePartyProceduralStatus(mixed $status): ?string
    {
        if ($status === null) {
            return null;
        }
        $normalized = trim((string)$status);
        if ($normalized === '') {
            return null;
        }
        if (!in_array($normalized, self::PARTY_PROCEDURAL_STATUSES, true)) {
            throw new \InvalidArgumentException('Tərəfin prosessual statusu düzgün deyil');
        }
        return $normalized;
    }

    public function delete(int $id): ?array
    {
        $row = $this->find($id);
        if (!$row) {
            return null;
        }

        $stmt = $this->db->prepare('DELETE FROM mehkeme_ishleri WHERE id=? AND owner_user_id=?');
        $stmt->execute([$id, $this->ownerUserId()]);
        return $row;
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_ishleri WHERE id=? AND owner_user_id=?');
        $stmt->execute([$id, $this->ownerUserId()]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function searchByNumber(string $number): array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_ishleri WHERE owner_user_id=? AND is_nomresi LIKE ? ORDER BY mehkeme_tarixi ASC, id ASC');
        $stmt->execute([$this->ownerUserId(), '%' . $number . '%']);
        return $stmt->fetchAll();
    }

    public function search(string $query): array
    {
        $q = '%' . $query . '%';
        $stmt = $this->db->prepare(
            'SELECT * FROM mehkeme_ishleri
             WHERE owner_user_id=? AND (is_nomresi LIKE ? OR terefin_adi LIKE ? OR mehkemenin_adi LIKE ? OR hakimin_adi LIKE ?)
             ORDER BY mehkeme_tarixi ASC, id ASC'
        );
        $stmt->execute([$this->ownerUserId(), $q, $q, $q, $q]);
        return $stmt->fetchAll();
    }

    public function all(): array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_ishleri WHERE owner_user_id=? ORDER BY mehkeme_tarixi ASC, id ASC');
        $stmt->execute([$this->ownerUserId()]);
        return $stmt->fetchAll();
    }

    public function activeForBot(): array
    {
        $stmt = $this->db->prepare(
            "SELECT m.*,
                    (SELECT i.tarix FROM iclaslar i
                     WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                       AND i.veziyyet='quvvededir'
                       AND CONCAT(i.tarix, ' ', i.saat) >= NOW()
                     ORDER BY i.tarix ASC, i.saat ASC, i.id ASC LIMIT 1) AS novbeti_iclas_tarix,
                    (SELECT i.saat FROM iclaslar i
                     WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                       AND i.veziyyet='quvvededir'
                       AND CONCAT(i.tarix, ' ', i.saat) >= NOW()
                     ORDER BY i.tarix ASC, i.saat ASC, i.id ASC LIMIT 1) AS novbeti_iclas_saat,
                    EXISTS(SELECT 1 FROM iclaslar i
                           WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                             AND CONCAT(i.tarix, ' ', i.saat) < NOW()) AS kecmis_iclas_var
             FROM mehkeme_ishleri m
             WHERE m.owner_user_id=?
               AND m.birlestirildiyi_is_id IS NULL
               AND NOT EXISTS (
                   SELECT 1 FROM mehkeme_arxiv a
                   WHERE a.id=m.id AND a.owner_user_id=m.owner_user_id
               )
             ORDER BY m.mehkeme_tarixi ASC, m.id ASC"
        );
        $stmt->execute([$this->ownerUserId()]);
        $rows = $stmt->fetchAll();
        return array_map(fn(array $row): array => $this->withBotDetails($row), $rows);
    }

    public function findForBot(int $id): ?array
    {
        $stmt = $this->db->prepare(
            "SELECT m.*,
                    (SELECT i.tarix FROM iclaslar i
                     WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                       AND i.veziyyet='quvvededir'
                       AND CONCAT(i.tarix, ' ', i.saat) >= NOW()
                     ORDER BY i.tarix ASC, i.saat ASC, i.id ASC LIMIT 1) AS novbeti_iclas_tarix,
                    (SELECT i.saat FROM iclaslar i
                     WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                       AND i.veziyyet='quvvededir'
                       AND CONCAT(i.tarix, ' ', i.saat) >= NOW()
                     ORDER BY i.tarix ASC, i.saat ASC, i.id ASC LIMIT 1) AS novbeti_iclas_saat,
                    (SELECT i.iclas_novu FROM iclaslar i
                     WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                       AND i.veziyyet='quvvededir'
                       AND CONCAT(i.tarix, ' ', i.saat) >= NOW()
                     ORDER BY i.tarix ASC, i.saat ASC, i.id ASC LIMIT 1) AS novbeti_iclas_novu,
                    EXISTS(SELECT 1 FROM iclaslar i
                           WHERE i.is_id=m.id AND i.owner_user_id=m.owner_user_id
                             AND CONCAT(i.tarix, ' ', i.saat) < NOW()) AS kecmis_iclas_var
             FROM mehkeme_ishleri m
             WHERE m.id=? AND m.owner_user_id=?
               AND NOT EXISTS (
                   SELECT 1 FROM mehkeme_arxiv a
                   WHERE a.id=m.id AND a.owner_user_id=m.owner_user_id
               )"
        );
        $stmt->execute([$id, $this->ownerUserId()]);
        $row = $stmt->fetch();
        return $row ? $this->withBotDetails($row) : null;
    }

    public function archivedForBot(): array
    {
        $stmt = $this->db->prepare(
            'SELECT a.* FROM mehkeme_arxiv a
             WHERE a.owner_user_id=?
             ORDER BY a.arxiv_tarixi DESC, a.id DESC'
        );
        $stmt->execute([$this->ownerUserId()]);
        return array_map(fn(array $row): array => $this->withBotDetails($row), $stmt->fetchAll());
    }

    public function findArchivedForBot(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_arxiv WHERE id=? AND owner_user_id=?');
        $stmt->execute([$id, $this->ownerUserId()]);
        $row = $stmt->fetch();
        return $row ? $this->withBotDetails($row) : null;
    }

    public function restoreFromArchive(int $id): array
    {
        $ownerUserId = $this->ownerUserId();
        $this->db->beginTransaction();
        try {
            $archiveStatement = $this->db->prepare(
                'SELECT * FROM mehkeme_arxiv WHERE id=? AND owner_user_id=? FOR UPDATE'
            );
            $archiveStatement->execute([$id, $ownerUserId]);
            $archiveRow = $archiveStatement->fetch();
            if (!$archiveRow) {
                throw new \InvalidArgumentException('Arxiv məhkəmə işi tapılmadı');
            }

            $activeStatement = $this->db->prepare('SELECT owner_user_id FROM mehkeme_ishleri WHERE id=? FOR UPDATE');
            $activeStatement->execute([$id]);
            $activeOwner = $activeStatement->fetchColumn();
            if ($activeOwner !== false && (int)$activeOwner !== $ownerUserId) {
                throw new \RuntimeException('Bu ID başqa istifadəçiyə məxsus aktiv işdə istifadə olunur');
            }
            if ($activeOwner === false) {
                $this->copyCommonColumns('mehkeme_arxiv', 'mehkeme_ishleri', $id, $ownerUserId);
            }

            $this->db->prepare(
                "UPDATE mehkeme_ishleri SET is_statusu='İcraatdadır' WHERE id=? AND owner_user_id=?"
            )->execute([$id, $ownerUserId]);
            $this->db->prepare(
                'UPDATE tapshiriq_mehkeme SET is_id=?, arxiv_is_id=NULL
                 WHERE arxiv_is_id=? AND owner_user_id=?'
            )->execute([$id, $id, $ownerUserId]);
            $this->db->prepare('DELETE FROM mehkeme_arxiv WHERE id=? AND owner_user_id=?')
                ->execute([$id, $ownerUserId]);
            $this->db->commit();
        } catch (\Throwable $exception) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            throw $exception;
        }

        return $this->findForBot($id) ?? [];
    }

    public function patchFromBot(int $id, string $field, mixed $value): array
    {
        $columns = [
            'number' => 'is_nomresi',
            'party' => 'terefin_adi',
            'party_procedural_status' => 'terefin_prosessual_statusu',
            'court' => 'mehkemenin_adi',
            'judge' => 'hakimin_adi',
            'status' => 'is_statusu',
            'case_date' => 'mehkeme_tarixi',
            'summary' => 'is_mahiyyeti',
            'notes' => 'qeydler',
        ];
        if (!isset($columns[$field])) {
            throw new \InvalidArgumentException('Dəyişdirilən məhkəmə işi sahəsi düzgün deyil');
        }

        $normalized = trim((string)$value);
        if (in_array($field, ['number', 'party', 'court', 'judge'], true) && $normalized === '') {
            throw new \InvalidArgumentException('Bu sahə boş saxlanıla bilməz');
        }
        if ($field === 'status') {
            $allowedStatuses = ['İcraatdadır', 'Dayandırıldı', 'İştirakım bitdi', 'Yekunlaşdı'];
            if (!in_array($normalized, $allowedStatuses, true)) {
                throw new \InvalidArgumentException('Məhkəmə işinin statusu düzgün deyil');
            }
        }
        if ($field === 'party_procedural_status') {
            if (!in_array($normalized, self::PARTY_PROCEDURAL_STATUSES, true)) {
                throw new \InvalidArgumentException('Tərəfin prosessual statusu düzgün deyil');
            }
        }
        if ($field === 'case_date') {
            $normalized = $this->normalizeDate($normalized, true);
        }

        $column = $columns[$field];
        $stmt = $this->db->prepare(
            "UPDATE mehkeme_ishleri SET {$column}=?
             WHERE id=? AND owner_user_id=?
               AND NOT EXISTS (
                   SELECT 1 FROM mehkeme_arxiv a
                   WHERE a.id=mehkeme_ishleri.id AND a.owner_user_id=mehkeme_ishleri.owner_user_id
               )"
        );
        $stmt->execute([$normalized !== '' ? $normalized : null, $id, $this->ownerUserId()]);
        if ($stmt->rowCount() < 1 && $this->findForBot($id) === null) {
            throw new \InvalidArgumentException('Məhkəmə işi tapılmadı');
        }
        return $this->findForBot($id) ?? [];
    }

    public function saveFinalActFromBot(
        int $id,
        bool $hasFinalAct,
        ?string $actDate,
        array $outcomeCodes,
        ?string $successCode,
        bool $hasLegalForce,
        ?int $mergedIntoCaseId = null
    ): array {
        if ($this->findForBot($id) === null) {
            throw new \InvalidArgumentException('Məhkəmə işi tapılmadı');
        }

        $selectedCodes = [];
        if ($hasFinalAct) {
            $actDate = $this->normalizeDate((string)$actDate, false);
            foreach ($outcomeCodes as $code) {
                $code = trim((string)$code);
                if (isset(self::FINAL_ACT_OUTCOMES[$code]) && !in_array($code, $selectedCodes, true)) {
                    $selectedCodes[] = $code;
                }
            }
            if ($selectedCodes === []) {
                throw new \InvalidArgumentException('Yekun aktın nəticələrindən ən azı birini seçin');
            }
            if (!isset(self::FINAL_ACT_SUCCESS[(string)$successCode])) {
                throw new \InvalidArgumentException('İşin yekun uğur nəticəsini seçin');
            }
            if (in_array('basqa_isle_birlestirildi', $selectedCodes, true)) {
                if ($mergedIntoCaseId === null || $mergedIntoCaseId <= 0 || $mergedIntoCaseId === $id) {
                    throw new \InvalidArgumentException('İşin birləşdirildiyi aktiv məhkəmə işini seçin');
                }
                $targetStatement = $this->db->prepare(
                    'SELECT COUNT(*) FROM mehkeme_ishleri m
                     WHERE m.id=? AND m.owner_user_id=?
                       AND m.is_statusu IN (\'İcraatdadır\', \'Dayandırıldı\')
                       AND m.birlestirildiyi_is_id IS NULL
                       AND NOT EXISTS (
                           SELECT 1 FROM mehkeme_arxiv a
                           WHERE a.id=m.id AND a.owner_user_id=m.owner_user_id
                       )'
                );
                $targetStatement->execute([$mergedIntoCaseId, $this->ownerUserId()]);
                if ((int)$targetStatement->fetchColumn() === 0) {
                    throw new \InvalidArgumentException('Seçilmiş birləşmə hədəfi aktiv məhkəmə işi deyil');
                }
            } else {
                $mergedIntoCaseId = null;
            }
        } else {
            $actDate = null;
            $successCode = null;
            $hasLegalForce = false;
            $mergedIntoCaseId = null;
        }

        $summary = $hasFinalAct
            ? implode(', ', array_map(static fn(string $code): string => self::FINAL_ACT_OUTCOMES[$code], $selectedCodes))
            : null;
        $stmt = $this->db->prepare(
            'UPDATE mehkeme_ishleri
             SET netice_verildi=?, yekunlasma_tarixi=?, netice=?, yekun_akt_netice_kodlari=?,
                 yekun_akt_ugur_kodu=?, qanuni_quvve_alib=?, birlestirildiyi_is_id=?,
                 is_statusu=CASE
                     WHEN ? IS NOT NULL THEN \'İcraata xitam verildi\'
                     WHEN is_statusu IN (\'Birləşdirildi\', \'İcraata xitam verildi\') THEN \'İcraatdadır\'
                     ELSE is_statusu
                 END
             WHERE id=? AND owner_user_id=?'
        );
        $stmt->execute([
            $hasFinalAct ? 1 : 0,
            $actDate,
            $summary,
            $hasFinalAct ? json_encode($selectedCodes, JSON_UNESCAPED_UNICODE) : null,
            $successCode,
            $hasLegalForce ? 1 : 0,
            $mergedIntoCaseId,
            $mergedIntoCaseId,
            $id,
            $this->ownerUserId(),
        ]);
        return $this->findForBot($id) ?? [];
    }

    public function finalActOptions(): array
    {
        return [
            'outcomes' => self::FINAL_ACT_OUTCOMES,
            'success' => self::FINAL_ACT_SUCCESS,
        ];
    }

    public function today(): array
    {
        return $this->bySqlDateWhere('mehkeme_tarixi = CURDATE()');
    }

    public function thisWeek(): array
    {
        return $this->bySqlDateWhere('YEARWEEK(mehkeme_tarixi, 1) = YEARWEEK(CURDATE(), 1)');
    }

    public function archivedByNumber(string $number): array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_arxiv WHERE owner_user_id=? AND is_nomresi LIKE ? ORDER BY arxiv_tarixi DESC');
        $stmt->execute([$this->ownerUserId(), '%' . $number . '%']);
        return $stmt->fetchAll();
    }

    private function bySqlDateWhere(string $where): array
    {
        $stmt = $this->db->prepare('SELECT * FROM mehkeme_ishleri WHERE owner_user_id=? AND ' . $where . ' ORDER BY mehkeme_tarixi ASC, id ASC');
        $stmt->execute([$this->ownerUserId()]);
        return $stmt->fetchAll();
    }

    private function withBotDetails(array $row): array
    {
        $warnings = [];
        if (($row['is_statusu'] ?? '') === 'İcraatdadır' && empty($row['qeyri_mueyyen_texir'])) {
            $authority = $this->authorityWarning($row);
            if ($authority !== '') {
                $warnings[] = $authority;
            }
            if (
                empty($row['yazili_icraat'])
                && !empty($row['kecmis_iclas_var'])
                && trim((string)($row['novbeti_iclas_tarix'] ?? '')) === ''
            ) {
                $warnings[] = 'sonuncu iclasdan sonra yeni iclas təyin edilməyib';
            }
        }
        $row['warning_text'] = implode('; ', $warnings);
        $row['has_warning'] = $warnings !== [];

        $outcomeCodes = json_decode((string)($row['yekun_akt_netice_kodlari'] ?? ''), true);
        $outcomeCodes = is_array($outcomeCodes) ? $outcomeCodes : [];
        $row['yekun_akt_netice_adlari'] = array_values(array_filter(array_map(
            static fn(string $code): ?string => self::FINAL_ACT_OUTCOMES[$code] ?? null,
            $outcomeCodes
        )));
        $row['yekun_akt_ugur_adi'] = self::FINAL_ACT_SUCCESS[(string)($row['yekun_akt_ugur_kodu'] ?? '')] ?? '';
        return $row;
    }

    private function authorityWarning(array $case): string
    {
        if (!empty($case['vakil_ozu_temsil'])) {
            return '';
        }
        $today = date('Y-m-d');
        $reasons = [];
        if (empty($case['etibarname_lazim_deyil'])) {
            if (empty($case['etibarname'])) {
                $reasons[] = 'etibarnamə yoxdur';
            } else {
                $expires = trim((string)($case['etibarname_bitis'] ?? ''));
                if ($expires === '') {
                    $reasons[] = 'etibarnamənin müddəti göstərilməyib';
                } elseif ($expires <= $today) {
                    $reasons[] = 'etibarnamənin müddəti bitib';
                } elseif (
                    trim((string)($case['novbeti_iclas_tarix'] ?? '')) !== ''
                    && $expires <= (string)$case['novbeti_iclas_tarix']
                ) {
                    $reasons[] = 'etibarnamənin müddəti iclas günü bitmiş olacaq';
                }
            }
        }
        if (empty($case['vakil_order'])) {
            $reasons[] = 'order tərtib edilməyib';
        }
        return $reasons ? 'səlahiyyət yoxdur (' . implode(', ', $reasons) . ')' : '';
    }

    private function normalizeDate(string $value, bool $allowEmpty): ?string
    {
        $value = trim($value);
        if ($value === '' && $allowEmpty) {
            return null;
        }
        foreach (['Y-m-d', 'd.m.Y'] as $format) {
            $date = \DateTimeImmutable::createFromFormat('!' . $format, $value);
            if ($date && $date->format($format) === $value) {
                return $date->format('Y-m-d');
            }
        }
        throw new \InvalidArgumentException('Tarix düzgün deyil');
    }

    private function copyCommonColumns(string $sourceTable, string $targetTable, int $id, int $ownerUserId): void
    {
        $sourceColumns = array_column($this->db->query("SHOW COLUMNS FROM {$sourceTable}")->fetchAll(), 'Field');
        $targetColumns = array_column($this->db->query("SHOW COLUMNS FROM {$targetTable}")->fetchAll(), 'Field');
        $columns = array_values(array_intersect($targetColumns, $sourceColumns));
        if ($columns === []) {
            throw new \RuntimeException('Bərpa üçün uyğun sütun tapılmadı');
        }
        $quoted = implode(', ', array_map(static fn(string $column): string => '`' . $column . '`', $columns));
        $stmt = $this->db->prepare(
            "INSERT INTO {$targetTable} ({$quoted})
             SELECT {$quoted} FROM {$sourceTable} WHERE id=? AND owner_user_id=?"
        );
        $stmt->execute([$id, $ownerUserId]);
        if ($stmt->rowCount() !== 1) {
            throw new \RuntimeException('Məhkəmə işi arxivdən köçürülmədi');
        }
    }
}