<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

final class DocumentRepository extends BaseRepository
{
    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.ville, c.telephone, c.mobile, c.email,
                   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 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;
    }

    public function lastNumberForType(string $type, string $prefix): string
    {
        if (!$this->tableExists('documents')) {
            return '';
        }
        $stmt = $this->db->prepare('SELECT numero FROM documents WHERE type_document = :type AND numero LIKE :prefix ORDER BY id DESC LIMIT 1');
        $stmt->execute([
            'type' => strtoupper($type),
            'prefix' => $prefix . '%',
        ]);
        return (string)($stmt->fetchColumn() ?: '');
    }

    public function insert(array $data): int
    {
        $stmt = $this->db->prepare("\n            INSERT INTO documents (\n                type_document, parent_document_id, client_id, numero, date_document, date_echeance, statut,\n                total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations\n            ) VALUES (\n                :type_document, :parent_document_id, :client_id, :numero, :date_document, :date_echeance, :statut,\n                :total_ht, :total_tva, :total_ttc, :montant_regle, :reste_du, :marge_ht, :marge_pourcentage, :observations\n            )\n        ");
        $stmt->execute($data);
        return (int)$this->db->lastInsertId();
    }

    public function insertLines(int $documentId, array $lines): void
    {
        $stmt = $this->db->prepare("\n            INSERT INTO documents_lignes (\n                document_id, article_id, reference, designation, unite, quantite,\n                prix_achat_ht, prix_vente_ht, remise, taux_tva,\n                total_ht, total_tva, total_ttc, marge_ht, marge_pourcentage, ordre\n            ) VALUES (\n                :document_id, :article_id, :reference, :designation, :unite, :quantite,\n                :prix_achat_ht, :prix_vente_ht, :remise, :taux_tva,\n                :total_ht, :total_tva, :total_ttc, :marge_ht, :marge_pourcentage, :ordre\n            )\n        ");

        foreach ($lines as $index => $line) {
            $stmt->execute([
                'document_id' => $documentId,
                'article_id' => !empty($line['article_id']) ? (int)$line['article_id'] : null,
                'reference' => $line['reference'],
                'designation' => $line['designation'],
                'unite' => $line['unite'],
                'quantite' => $line['quantite'],
                'prix_achat_ht' => $line['prix_achat_ht'],
                'prix_vente_ht' => $line['prix_vente_ht'],
                'remise' => $line['remise'],
                'taux_tva' => $line['taux_tva'],
                'total_ht' => $line['total_ht'],
                'total_tva' => $line['total_tva'],
                'total_ttc' => $line['total_ttc'],
                'marge_ht' => $line['marge_ht'],
                'marge_pourcentage' => $line['marge_pourcentage'],
                'ordre' => $index + 1,
            ]);
        }
    }

    public function findRaw(int $id): ?array
    {
        if (!$this->tableExists('documents')) {
            return null;
        }
        $stmt = $this->db->prepare('SELECT * FROM documents WHERE id = :id AND actif = 1 LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findChildByType(int $parentDocumentId, string $targetType): ?array
    {
        if (!$this->tableExists('documents')) {
            return null;
        }
        $stmt = $this->db->prepare('SELECT * FROM documents WHERE parent_document_id = :parent AND type_document = :type AND actif = 1 LIMIT 1');
        $stmt->execute(['parent' => $parentDocumentId, 'type' => strtoupper($targetType)]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function markStatus(int $documentId, string $status): void
    {
        if (!$this->tableExists('documents')) {
            return;
        }
        $this->db->prepare('UPDATE documents SET statut = :statut WHERE id = :id')->execute([
            'statut' => $status,
            'id' => $documentId,
        ]);
    }

}
