<?php

declare(strict_types=1);

namespace App\Repositories;

final class FournisseurRepository extends BaseRepository
{
    public function allActive(): array
    {
        return $this->fetchAll(
            "SELECT * FROM fournisseurs WHERE actif = 1 ORDER BY raison_sociale ASC"
        );
    }

    public function allWithStats(string $status = 'active'): array
    {
        $where = match ($status) {
            'archived' => 'WHERE actif = 0',
            'all' => '',
            default => 'WHERE actif = 1',
        };

        $rows = $this->fetchAll(
            "SELECT * FROM fournisseurs {$where} ORDER BY raison_sociale ASC"
        );
        foreach ($rows as &$row) {
            $stats = $this->stats((int) $row['id']);
            $row['nombre_commandes'] = $stats['nombre_commandes'];
            $row['total_commandes'] = $stats['total_commandes'];
        }
        unset($row);

        return $rows;
    }

    public function allActiveWithStats(): array
    {
        $rows = $this->allActive();
        foreach ($rows as &$row) {
            $stats = $this->stats((int) $row['id']);
            $row['nombre_commandes'] = $stats['nombre_commandes'];
            $row['total_commandes'] = $stats['total_commandes'];
        }
        unset($row);

        return $rows;
    }

    public function find(int $id): ?array
    {
        return $this->fetchOne(
            "SELECT * FROM fournisseurs WHERE id = :id LIMIT 1",
            ['id' => $id]
        );
    }

    public function findByCode(string $code): ?array
    {
        return $this->fetchOne(
            "SELECT * FROM fournisseurs WHERE code_fournisseur = :code LIMIT 1",
            ['code' => $code]
        );
    }

    public function nextCode(): string
    {
        $last = $this->fetchColumn(
            "SELECT code_fournisseur FROM fournisseurs WHERE code_fournisseur LIKE 'FRN%' ORDER BY id DESC LIMIT 1"
        );

        if (!$last) {
            return 'FRN000001';
        }

        $number = (int) substr((string) $last, 3);
        return 'FRN' . str_pad((string) ($number + 1), 6, '0', STR_PAD_LEFT);
    }

    public function create(array $data): int
    {
        $sql = "
            INSERT INTO fournisseurs (
                code_fournisseur, raison_sociale, nom_commercial, contact, fonction,
                telephone, mobile, email, site_web, adresse, complement_adresse,
                code_postal, ville, pays, ice, identifiant_fiscal, registre_commerce,
                patente, siret, tva_intracom, conditions_reglement, delai_livraison,
                observations, actif
            ) VALUES (
                :code_fournisseur, :raison_sociale, :nom_commercial, :contact, :fonction,
                :telephone, :mobile, :email, :site_web, :adresse, :complement_adresse,
                :code_postal, :ville, :pays, :ice, :identifiant_fiscal, :registre_commerce,
                :patente, :siret, :tva_intracom, :conditions_reglement, :delai_livraison,
                :observations, :actif
            )
        ";

        $this->execute($sql, $this->normalize($data));
        return $this->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $sql = "
            UPDATE fournisseurs SET
                code_fournisseur = :code_fournisseur,
                raison_sociale = :raison_sociale,
                nom_commercial = :nom_commercial,
                contact = :contact,
                fonction = :fonction,
                telephone = :telephone,
                mobile = :mobile,
                email = :email,
                site_web = :site_web,
                adresse = :adresse,
                complement_adresse = :complement_adresse,
                code_postal = :code_postal,
                ville = :ville,
                pays = :pays,
                ice = :ice,
                identifiant_fiscal = :identifiant_fiscal,
                registre_commerce = :registre_commerce,
                patente = :patente,
                siret = :siret,
                tva_intracom = :tva_intracom,
                conditions_reglement = :conditions_reglement,
                delai_livraison = :delai_livraison,
                observations = :observations,
                actif = :actif
            WHERE id = :id
        ";

        $params = $this->normalize($data);
        $params['id'] = $id;
        $this->execute($sql, $params);
    }

    public function deactivate(int $id): void
    {
        $this->execute(
            "UPDATE fournisseurs SET actif = 0 WHERE id = :id",
            ['id' => $id]
        );
    }

    public function activate(int $id): void
    {
        $this->execute(
            "UPDATE fournisseurs SET actif = 1 WHERE id = :id",
            ['id' => $id]
        );
    }

    public function stats(int $fournisseurId): array
    {
        if (!$this->tableExists('commandes_fournisseurs')) {
            return [
                'nombre_commandes' => 0,
                'total_commandes' => 0.0,
            ];
        }

        $row = $this->fetchOne(
            "SELECT COUNT(*) AS nombre_commandes, COALESCE(SUM(total_ttc), 0) AS total_commandes
             FROM commandes_fournisseurs
             WHERE fournisseur_id = :id AND actif = 1",
            ['id' => $fournisseurId]
        ) ?? [];

        return [
            'nombre_commandes' => (int)($row['nombre_commandes'] ?? 0),
            'total_commandes' => (float)($row['total_commandes'] ?? 0),
        ];
    }

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

    private function normalize(array $data): array
    {
        return [
            'code_fournisseur' => trim((string) ($data['code_fournisseur'] ?? '')),
            'raison_sociale' => trim((string) ($data['raison_sociale'] ?? '')),
            'nom_commercial' => trim((string) ($data['nom_commercial'] ?? '')),
            'contact' => trim((string) ($data['contact'] ?? '')),
            'fonction' => trim((string) ($data['fonction'] ?? '')),
            'telephone' => trim((string) ($data['telephone'] ?? '')),
            'mobile' => trim((string) ($data['mobile'] ?? '')),
            'email' => trim((string) ($data['email'] ?? '')),
            'site_web' => trim((string) ($data['site_web'] ?? '')),
            'adresse' => trim((string) ($data['adresse'] ?? '')),
            'complement_adresse' => trim((string) ($data['complement_adresse'] ?? '')),
            'code_postal' => trim((string) ($data['code_postal'] ?? '')),
            'ville' => trim((string) ($data['ville'] ?? '')),
            'pays' => trim((string) ($data['pays'] ?? 'Maroc')) ?: 'Maroc',
            'ice' => trim((string) ($data['ice'] ?? '')),
            'identifiant_fiscal' => trim((string) ($data['identifiant_fiscal'] ?? '')),
            'registre_commerce' => trim((string) ($data['registre_commerce'] ?? '')),
            'patente' => trim((string) ($data['patente'] ?? '')),
            'siret' => trim((string) ($data['siret'] ?? '')),
            'tva_intracom' => trim((string) ($data['tva_intracom'] ?? '')),
            'conditions_reglement' => trim((string) ($data['conditions_reglement'] ?? '')),
            'delai_livraison' => ($data['delai_livraison'] ?? '') !== '' ? (int) $data['delai_livraison'] : null,
            'observations' => trim((string) ($data['observations'] ?? '')),
            'actif' => isset($data['actif']) ? (int) $data['actif'] : 1,
        ];
    }
}
