<?php
declare(strict_types=1);

namespace App\Services\PackageManager;

use App\Core\Database;
use PDO;
use RuntimeException;

final class PackageRegistryService
{
    public function synchronize(?int $userId = null): array
    {
        $pdo = Database::connection();
        $packages = $this->sourcePackages($pdo);
        $synced = 0;

        $upsert = $pdo->prepare(
            'INSERT INTO system_package_registry(
                update_package_id,package_key,package_name,category,version,revision,publisher,description,
                package_status,trust_score,compatibility_score,certification_status,policy_decision,
                checksum_sha256,file_size,installed_at,last_operation_at,created_at,updated_at
             ) VALUES(
                :update_id,:package_key,:package_name,:category,:version,:revision,:publisher,:description,
                :status,:trust_score,:compatibility_score,:certification_status,:policy_decision,
                :checksum,:file_size,:installed_at,:last_operation_at,NOW(),NOW()
             ) ON DUPLICATE KEY UPDATE
                package_key=VALUES(package_key),package_name=VALUES(package_name),category=VALUES(category),
                version=VALUES(version),revision=VALUES(revision),publisher=VALUES(publisher),description=VALUES(description),
                package_status=VALUES(package_status),trust_score=VALUES(trust_score),compatibility_score=VALUES(compatibility_score),
                certification_status=VALUES(certification_status),policy_decision=VALUES(policy_decision),
                checksum_sha256=VALUES(checksum_sha256),file_size=VALUES(file_size),installed_at=VALUES(installed_at),
                last_operation_at=VALUES(last_operation_at),updated_at=NOW()'
        );
        $history = $pdo->prepare(
            'INSERT INTO system_package_operations(registry_id,operation_type,source_version,target_version,status,message,performed_by,created_at)
             SELECT id,:operation_type,NULL,:target_version,:status,:message,:performed_by,NOW()
             FROM system_package_registry WHERE update_package_id=:update_id'
        );

        foreach ($packages as $package) {
            $name = (string)($package['nom_package'] ?? 'Package AXIOM');
            $version = (string)($package['version_package'] ?? $package['version_cible'] ?? '0.0.0');
            $status = $this->normalizeStatus((string)($package['statut'] ?? 'importe'));
            $upsert->execute([
                'update_id' => (int)$package['id'],
                'package_key' => $this->packageKey($package),
                'package_name' => $this->displayName($name),
                'category' => $this->category($name),
                'version' => $version,
                'revision' => $this->revision($name),
                'publisher' => 'AXIOM Enterprise',
                'description' => $this->description($name, $status),
                'status' => $status,
                'trust_score' => $this->nullableScore($package['trust_score'] ?? null),
                'compatibility_score' => $this->nullableScore($package['compatibility_score'] ?? null),
                'certification_status' => (string)($package['certification_status'] ?? 'NON_EVALUE'),
                'policy_decision' => (string)($package['policy_decision'] ?? 'NON_EVALUE'),
                'checksum' => $package['checksum_sha256'] ?: null,
                'file_size' => (int)($package['taille_octets'] ?? 0),
                'installed_at' => $package['installation_fin'] ?: $package['installe_at'] ?: null,
                'last_operation_at' => $package['installation_fin'] ?: $package['importe_at'] ?: null,
            ]);
            $synced++;
        }

        $pdo->prepare(
            "UPDATE system_package_registry r
             SET package_status='REMOVED',updated_at=NOW()
             WHERE r.update_package_id IS NOT NULL
             AND NOT EXISTS(SELECT 1 FROM cda_update_packages p WHERE p.id=r.update_package_id)"
        )->execute();

        if ($synced > 0) {
            $history->execute([
                'operation_type' => 'REGISTRY_SYNC',
                'target_version' => '10.2.0',
                'status' => 'SUCCESS',
                'message' => 'Synchronisation automatique du registre Enterprise.',
                'performed_by' => $userId,
                'update_id' => (int)($packages[0]['id'] ?? 0),
            ]);
        }

        return ['synchronized' => $synced, 'generated_at' => date('Y-m-d H:i:s')];
    }

    public function dashboard(array $filters = [], ?int $selectedId = null): array
    {
        $pdo = Database::connection();
        $this->synchronize(null);
        $packages = $this->registryRows($pdo);

        $search = mb_strtolower(trim((string)($filters['q'] ?? '')));
        $category = strtoupper(trim((string)($filters['category'] ?? '')));
        $status = strtoupper(trim((string)($filters['status'] ?? '')));
        $filtered = array_values(array_filter($packages, static function (array $row) use ($search, $category, $status): bool {
            if ($search !== '') {
                $haystack = mb_strtolower(implode(' ', [
                    (string)($row['package_name'] ?? ''), (string)($row['package_key'] ?? ''),
                    (string)($row['version'] ?? ''), (string)($row['publisher'] ?? ''),
                ]));
                if (!str_contains($haystack, $search)) return false;
            }
            if ($category !== '' && strtoupper((string)($row['category'] ?? '')) !== $category) return false;
            if ($status !== '' && strtoupper((string)($row['package_status'] ?? '')) !== $status) return false;
            return true;
        }));

        $selected = null;
        if ($selectedId !== null && $selectedId > 0) {
            foreach ($packages as $row) {
                if ((int)$row['id'] === $selectedId) { $selected = $row; break; }
            }
        }
        if ($selected !== null) {
            $selected['operations'] = $this->operations($pdo, (int)$selected['id']);
            $selected['tabs'] = $this->relatedReports($pdo, (int)($selected['update_package_id'] ?? 0));
        }

        return [
            'packages' => $filtered,
            'selected' => $selected,
            'metrics' => $this->metrics($packages),
            'categories' => array_values(array_unique(array_map(static fn(array $r): string => (string)$r['category'], $packages))),
            'statuses' => array_values(array_unique(array_map(static fn(array $r): string => (string)$r['package_status'], $packages))),
            'recent_operations' => $this->recentOperations($pdo),
            'generated_at' => date('Y-m-d H:i:s'),
        ];
    }

    public function exportRows(): array
    {
        return $this->registryRows(Database::connection());
    }

    private function sourcePackages(PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            "SELECT p.*,
                (SELECT t.trust_score FROM system_package_trust_reports t WHERE t.package_id=p.id ORDER BY t.id DESC LIMIT 1) trust_score,
                (SELECT c.compatibility_score FROM system_package_compatibility_reports c WHERE c.package_id=p.id ORDER BY c.id DESC LIMIT 1) compatibility_score,
                (SELECT pc.status FROM system_package_certificates pc WHERE pc.package_id=p.id ORDER BY pc.id DESC LIMIT 1) certification_status,
                (SELECT d.decision FROM system_package_policy_decisions d WHERE d.package_id=p.id ORDER BY d.id DESC LIMIT 1) policy_decision
             FROM cda_update_packages p ORDER BY p.id DESC"
        );
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function registryRows(PDO $pdo): array
    {
        $stmt = $pdo->prepare('SELECT * FROM system_package_registry ORDER BY COALESCE(installed_at,last_operation_at,created_at) DESC,id DESC');
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function operations(PDO $pdo, int $registryId): array
    {
        $stmt = $pdo->prepare('SELECT * FROM system_package_operations WHERE registry_id=:id ORDER BY id DESC LIMIT 100');
        $stmt->execute(['id' => $registryId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function recentOperations(PDO $pdo): array
    {
        $stmt = $pdo->prepare('SELECT o.*,r.package_name FROM system_package_operations o JOIN system_package_registry r ON r.id=o.registry_id ORDER BY o.id DESC LIMIT 12');
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function relatedReports(PDO $pdo, int $packageId): array
    {
        if ($packageId <= 0) return [];
        $result = ['trust'=>null,'compatibility'=>null,'certification'=>null,'policy'=>null,'enterprise'=>null];
        try {
            $stmt = $pdo->prepare('SELECT trust_score AS score,decision,created_at FROM system_package_trust_reports WHERE package_id=:id ORDER BY id DESC LIMIT 1');
            $stmt->execute(['id'=>$packageId]); $result['trust']=$stmt->fetch(PDO::FETCH_ASSOC)?:null;
            $stmt = $pdo->prepare('SELECT compatibility_score AS score,decision,created_at FROM system_package_compatibility_reports WHERE package_id=:id ORDER BY id DESC LIMIT 1');
            $stmt->execute(['id'=>$packageId]); $result['compatibility']=$stmt->fetch(PDO::FETCH_ASSOC)?:null;
            $stmt = $pdo->prepare('SELECT certification_score AS score,status AS decision,created_at FROM system_package_certificates WHERE package_id=:id ORDER BY id DESC LIMIT 1');
            $stmt->execute(['id'=>$packageId]); $result['certification']=$stmt->fetch(PDO::FETCH_ASSOC)?:null;
            $stmt = $pdo->prepare('SELECT policy_score AS score,decision,created_at FROM system_package_policy_decisions WHERE package_id=:id ORDER BY id DESC LIMIT 1');
            $stmt->execute(['id'=>$packageId]); $result['policy']=$stmt->fetch(PDO::FETCH_ASSOC)?:null;
            $stmt = $pdo->prepare('SELECT global_score AS score,decision,created_at FROM system_package_enterprise_reports WHERE package_id=:id ORDER BY id DESC LIMIT 1');
            $stmt->execute(['id'=>$packageId]); $result['enterprise']=$stmt->fetch(PDO::FETCH_ASSOC)?:null;
        } catch (\Throwable) {
            // Les rapports anciens restent facultatifs pour le registre.
        }
        return $result;
    }

    private function metrics(array $packages): array
    {
        $installed = 0; $system = 0; $repair = 0; $updates = 0; $trust = []; $compat = [];
        foreach ($packages as $row) {
            $state = (string)$row['package_status'];
            if ($state === 'INSTALLED' || $state === 'SYSTEM') $installed++;
            if ($state === 'SYSTEM') $system++;
            if ($state === 'REPAIR_REQUIRED' || $state === 'BROKEN') $repair++;
            if ($state === 'UPDATE_AVAILABLE') $updates++;
            if ($row['trust_score'] !== null) $trust[] = (int)$row['trust_score'];
            if ($row['compatibility_score'] !== null) $compat[] = (int)$row['compatibility_score'];
        }
        return [
            'total' => count($packages), 'installed' => $installed, 'system' => $system,
            'repair' => $repair, 'updates' => $updates,
            'trust_average' => $trust ? (int)round(array_sum($trust) / count($trust)) : 0,
            'compatibility_average' => $compat ? (int)round(array_sum($compat) / count($compat)) : 0,
        ];
    }

    private function normalizeStatus(string $status): string
    {
        return match (strtolower($status)) {
            'installe', 'installed' => 'INSTALLED',
            'echec', 'failed' => 'BROKEN',
            'importe', 'imported' => 'AVAILABLE',
            'desactive', 'disabled' => 'DISABLED',
            default => 'AVAILABLE',
        };
    }

    private function packageKey(array $package): string
    {
        $checksum = trim((string)($package['checksum_sha256'] ?? ''));
        return $checksum !== '' ? 'sha256:'.$checksum : 'update:'.(int)$package['id'];
    }

    private function displayName(string $name): string
    {
        $value = preg_replace('/\.zip$/i', '', $name) ?? $name;
        $value = preg_replace('/^(CDA_|AXIOM_)/i', '', $value) ?? $value;
        return trim(str_replace('_', ' ', $value));
    }

    private function category(string $name): string
    {
        $u = strtoupper($name);
        foreach (['SECURITY','ENTERPRISE','PLATFORM','STUDIO','CONNECTOR','CORE','BUSINESS'] as $category) {
            if (str_contains($u, $category)) return $category === 'CONNECTOR' ? 'CONNECTORS' : $category;
        }
        return 'EXTENSIONS';
    }

    private function revision(string $name): ?string
    {
        return preg_match('/(?:R|FIX|HOTFIX)[0-9.]+/i', $name, $m) ? strtoupper($m[0]) : null;
    }

    private function description(string $name, string $status): string
    {
        return 'Package AXIOM synchronisé depuis le Centre de mises à jour. État actuel : '.$status.'. Source : '.$this->displayName($name).'.';
    }

    private function nullableScore(mixed $value): ?int
    {
        return $value === null || $value === '' ? null : max(0, min(100, (int)$value));
    }
}
