<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

final class Document extends Model
{
    public function all(array $filters = []): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        $where = ['d.actif = 1'];
        $params = [];

        if (!empty($filters['type'])) {
            $where[] = 'd.type_document = :type';
            $params['type'] = $filters['type'];
        }

        if (!empty($filters['q'])) {
            $where[] = '(d.numero LIKE :q OR c.raison_sociale LIKE :q)';
            $params['q'] = '%' . $filters['q'] . '%';
        }

        $sql = "
            SELECT d.*, c.raison_sociale AS client_nom, parent.numero AS parent_numero, parent.type_document AS parent_type
            FROM documents d
            INNER JOIN clients c ON c.id = d.client_id
            LEFT JOIN documents parent ON parent.id = d.parent_document_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY d.date_document DESC, d.id DESC
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find(int $id): ?array
    {
        if (!$this->tableExists('documents')) {
            return null;
        }

        $stmt = $this->db->prepare("\n            SELECT d.*, c.raison_sociale AS client_nom, c.adresse, c.code_postal, c.ville, c.pays, c.telephone, c.mobile, c.email, c.ice AS client_ice,
                   parent.numero AS parent_numero, parent.type_document AS parent_type
            FROM documents d
            INNER JOIN clients c ON c.id = d.client_id
            LEFT JOIN documents parent ON parent.id = d.parent_document_id
            WHERE d.id = :id AND d.actif = 1
            LIMIT 1
        ");
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function lines(int $documentId): array
    {
        if (!$this->tableExists('documents_lignes')) {
            return [];
        }

        $stmt = $this->db->prepare("\n            SELECT *\n            FROM documents_lignes\n            WHERE document_id = :document_id\n            ORDER BY ordre ASC, id ASC\n        ");
        $stmt->execute(['document_id' => $documentId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }


    public function clientsForSelect(): array
    {
        return $this->db->query("\n            SELECT id, code_client, raison_sociale\n            FROM clients\n            WHERE actif = 1\n            ORDER BY raison_sociale ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function articlesForPicker(): array
    {
        return $this->db->query("\n            SELECT id, reference, designation, unite, prix_achat_ht, prix_vente_ht, taux_tva, marge_pourcentage\n            FROM articles\n            WHERE actif = 1\n            ORDER BY designation ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function children(int $documentId): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        $stmt = $this->db->prepare("\n            SELECT d.*, c.raison_sociale AS client_nom\n            FROM documents d\n            INNER JOIN clients c ON c.id = d.client_id\n            WHERE d.parent_document_id = :id AND d.actif = 1\n            ORDER BY d.date_document ASC, d.id ASC\n        ");
        $stmt->execute(['id' => $documentId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }



    public function sourcesForTransformation(string $sourceType, string $targetType): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        $sourceType = strtoupper($sourceType);
        $targetType = strtoupper($targetType);

        $stmt = $this->db->prepare("\n            SELECT d.*, c.raison_sociale AS client_nom,\n                   child.id AS existing_child_id, child.numero AS existing_child_numero\n            FROM documents d\n            INNER JOIN clients c ON c.id = d.client_id\n            LEFT JOIN documents child\n                ON child.parent_document_id = d.id\n               AND child.type_document = :target_type\n               AND child.actif = 1\n            WHERE d.actif = 1\n              AND d.type_document = :source_type\n            ORDER BY d.date_document DESC, d.id DESC\n        ");
        $stmt->execute([
            'source_type' => $sourceType,
            'target_type' => $targetType,
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function relatedDocuments(int $documentId): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        $document = $this->find($documentId);
        if (!$document) {
            return [];
        }

        $ids = [];
        $current = $document;
        while (!empty($current['parent_document_id'])) {
            $parentId = (int)$current['parent_document_id'];
            if ($parentId <= 0 || in_array($parentId, $ids, true)) {
                break;
            }
            $ids[] = $parentId;
            $parent = $this->find($parentId);
            if (!$parent) {
                break;
            }
            $current = $parent;
        }

        $ids[] = $documentId;
        $queue = [$documentId];
        while (!empty($queue)) {
            $parentId = array_shift($queue);
            foreach ($this->children((int)$parentId) as $child) {
                $childId = (int)$child['id'];
                if (!in_array($childId, $ids, true)) {
                    $ids[] = $childId;
                    $queue[] = $childId;
                }
            }
        }

        if (empty($ids)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $this->db->prepare("\n            SELECT d.*, c.raison_sociale AS client_nom\n            FROM documents d\n            INNER JOIN clients c ON c.id = d.client_id\n            WHERE d.id IN ($placeholders) AND d.actif = 1\n            ORDER BY d.date_document ASC, d.id ASC\n        ");
        $stmt->execute($ids);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function countByType(): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        $rows = $this->db->query("\n            SELECT type_document, COUNT(*) AS total\n            FROM documents\n            WHERE actif = 1\n            GROUP BY type_document\n            ORDER BY type_document ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);

        $result = [];
        foreach ($rows as $row) {
            $result[(string)$row['type_document']] = (int)$row['total'];
        }
        return $result;
    }

    public function tableExists(string $table): bool
    {
        $stmt = $this->db->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table');
        $stmt->execute(['table' => $table]);
        return (int) $stmt->fetchColumn() > 0;
    }
}
