<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use PDO;

final class DocumentCycleService extends BaseService
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::connection();
    }

    public function log(string $type, int $id, string $code, string $label, ?string $linkedType = null, ?int $linkedId = null): void
    {
        if ($id <= 0 || !$this->tableExists('document_actions')) {
            return;
        }
        $stmt = $this->db->prepare('INSERT INTO document_actions (document_type, document_id, action_code, libelle, document_lie_type, document_lie_id, utilisateur_id) VALUES (:type,:id,:code,:label,:linked_type,:linked_id,:user_id)');
        $stmt->execute([
            'type' => strtoupper($type), 'id' => $id, 'code' => $code, 'label' => $label,
            'linked_type' => $linkedType ? strtoupper($linkedType) : null,
            'linked_id' => $linkedId, 'user_id' => isset($_SESSION['user_id']) ? (int)$_SESSION['user_id'] : null,
        ]);
    }

    public function forDocument(string $type, int $id): array
    {
        if (!$this->tableExists('document_actions')) {
            return [];
        }
        $stmt = $this->db->prepare('SELECT * FROM document_actions WHERE document_type = :type AND document_id = :id ORDER BY date_action DESC, id DESC');
        $stmt->execute(['type' => strtoupper($type), 'id' => $id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function links(string $type, int $id): array
    {
        $type = strtoupper($type);
        $result = ['client' => null, 'devis' => null, 'bl' => null, 'facture' => null, 'avoirs' => []];

        if ($type === 'DEVIS') {
            $row = $this->one('SELECT d.client_id, d.numero devis_numero FROM devis d WHERE d.id=:id', $id);
            $result['devis'] = $row ? ['id'=>$id,'numero'=>$row['devis_numero']] : null;
            $result['client'] = $row ? $this->client((int)$row['client_id']) : null;
            $result['bl'] = $this->one('SELECT id, numero FROM bons_livraison WHERE devis_id=:id AND actif=1 ORDER BY id DESC LIMIT 1', $id);
            if ($this->columnExists('factures', 'devis_id')) {
                $result['facture'] = $this->one('SELECT id, numero FROM factures WHERE devis_id=:id AND actif=1 ORDER BY id DESC LIMIT 1', $id);
            }
            if (!$result['facture'] && $result['bl']) {
                $result['facture'] = $this->one('SELECT id, numero FROM factures WHERE bon_livraison_id=:id AND actif=1 ORDER BY id DESC LIMIT 1', (int)$result['bl']['id']);
            }
        } elseif ($type === 'BL') {
            $row = $this->one('SELECT client_id, devis_id, numero FROM bons_livraison WHERE id=:id', $id);
            $result['bl'] = $row ? ['id'=>$id,'numero'=>$row['numero']] : null;
            $result['client'] = $row ? $this->client((int)$row['client_id']) : null;
            if ($row && !empty($row['devis_id'])) $result['devis'] = $this->one('SELECT id, numero FROM devis WHERE id=:id', (int)$row['devis_id']);
            $result['facture'] = $this->one('SELECT id, numero FROM factures WHERE bon_livraison_id=:id AND actif=1 ORDER BY id DESC LIMIT 1', $id);
        } elseif ($type === 'FACTURE') {
            $row = $this->one('SELECT client_id, bon_livraison_id, numero'.($this->columnExists('factures','devis_id')?', devis_id':'').' FROM factures WHERE id=:id', $id);
            $result['facture'] = $row ? ['id'=>$id,'numero'=>$row['numero']] : null;
            $result['client'] = $row ? $this->client((int)$row['client_id']) : null;
            if ($row && !empty($row['bon_livraison_id'])) {
                $result['bl'] = $this->one('SELECT id, numero, devis_id FROM bons_livraison WHERE id=:id', (int)$row['bon_livraison_id']);
                if ($result['bl'] && !empty($result['bl']['devis_id'])) $result['devis'] = $this->one('SELECT id, numero FROM devis WHERE id=:id', (int)$result['bl']['devis_id']);
            } elseif ($row && isset($row['devis_id']) && !empty($row['devis_id'])) {
                $result['devis'] = $this->one('SELECT id, numero FROM devis WHERE id=:id', (int)$row['devis_id']);
            }
            if ($this->tableExists('documents')) {
                $stmtDoc = $this->db->prepare("SELECT id FROM documents WHERE type_document='FACTURE' AND actif=1 AND ((source_table='factures' AND source_id=:id1) OR numero=(SELECT numero FROM factures WHERE id=:id2)) LIMIT 1");
                $stmtDoc->execute(['id1'=>$id,'id2'=>$id]);
                $docRow = $stmtDoc->fetch(PDO::FETCH_ASSOC);
                $doc = $docRow ?: null;
                if ($doc && $this->columnExists('documents', 'document_parent_id')) {
                    $stmt = $this->db->prepare("SELECT id, numero FROM documents WHERE type_document='AVOIR' AND document_parent_id=:id AND actif=1 ORDER BY id DESC");
                    $stmt->execute(['id'=>(int)$doc['id']]);
                    $result['avoirs'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
                }
            }
        }
        return $result;
    }

    public function dashboard(): array
    {
        $today = date('Y-m-d');
        return [
            'devis_attente' => $this->scalar("SELECT COUNT(*) FROM devis d WHERE d.actif=1 AND NOT EXISTS(SELECT 1 FROM bons_livraison bl WHERE bl.devis_id=d.id AND bl.actif=1)" . ($this->columnExists('factures','devis_id') ? " AND NOT EXISTS(SELECT 1 FROM factures f WHERE f.devis_id=d.id AND f.actif=1)" : '')),
            'bl_a_facturer' => $this->scalar("SELECT COUNT(*) FROM bons_livraison bl WHERE bl.actif=1 AND NOT EXISTS(SELECT 1 FROM factures f WHERE f.bon_livraison_id=bl.id AND f.actif=1)"),
            'factures_a_encaisser' => $this->scalar("SELECT COUNT(*) FROM factures WHERE actif=1 AND reste_du > 0.005"),
            'factures_en_retard' => $this->scalar("SELECT COUNT(*) FROM factures WHERE actif=1 AND reste_du > 0.005 AND date_echeance IS NOT NULL AND date_echeance < '".$today."'"),
            'reste_du' => $this->scalar("SELECT COALESCE(SUM(reste_du),0) FROM factures WHERE actif=1 AND reste_du > 0.005"),
            'recent' => $this->recent(),
        ];
    }

    public function status(string $type, array $document, array $links = []): string
    {
        $type = strtoupper($type);
        if ($type === 'DEVIS') {
            if (!empty($links['facture'])) return 'Facturé';
            if (!empty($links['bl'])) return 'Transformé en BL';
            if (!empty($document['validite']) && $document['validite'] < date('Y-m-d')) return 'Expiré';
            return $this->text($document['statut'] ?? 'En cours') ?: 'En cours';
        }
        if ($type === 'BL') {
            if (!empty($links['facture'])) return 'Facturé';
            return $this->text($document['statut'] ?? 'Préparé') ?: 'Préparé';
        }
        if ($type === 'FACTURE') {
            $reste = (float)($document['reste_du'] ?? 0); $regle = (float)($document['montant_regle'] ?? 0);
            if ($reste <= 0.005) return 'Réglée';
            if (!empty($document['date_echeance']) && $document['date_echeance'] < date('Y-m-d')) return 'En retard';
            if ($regle > 0) return 'Partiellement réglée';
            return 'En cours';
        }
        return $this->text($document['statut'] ?? '');
    }

    private function recent(): array
    {
        $sql = "SELECT 'Devis' type, id, numero, date_modification date_action, 'devis_show' page FROM devis WHERE actif=1
                UNION ALL SELECT 'BL', id, numero, date_modification, 'bons_livraison_show' FROM bons_livraison WHERE actif=1
                UNION ALL SELECT 'Facture', id, numero, date_modification, 'factures_show' FROM factures WHERE actif=1
                ORDER BY date_action DESC LIMIT 15";
        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    private function client(int $id): ?array { return $this->one('SELECT id, raison_sociale AS numero FROM clients WHERE id=:id', $id); }
    private function one(string $sql, int $id): ?array { $s=$this->db->prepare($sql); $s->execute(['id'=>$id]); $r=$s->fetch(PDO::FETCH_ASSOC); return $r?:null; }
    private function scalar(string $sql): float { return (float)$this->db->query($sql)->fetchColumn(); }
    private function tableExists(string $table): bool { $s=$this->db->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=:t');$s->execute(['t'=>$table]);return (int)$s->fetchColumn()>0; }
    private function columnExists(string $table,string $column): bool { $s=$this->db->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=:t AND column_name=:c');$s->execute(['t'=>$table,'c'=>$column]);return (int)$s->fetchColumn()>0; }
}
