<?php

declare(strict_types=1);

namespace App\Service;

use App\Enum\ModelCategory;
use App\Repository\ModelTypeRepository;
use DateTimeImmutable;
use Doctrine\DBAL\Connection;

final class ModelTypeCategoryConversionService
{
    public function __construct(
        private readonly Connection $connection,
        private readonly ModelTypeRepository $modelTypes,
        private readonly ActorProfileResolver $actorProfileResolver,
    ) {}

    /**
     * @return array{canConvert: bool, direction: null|string, itemCount: int, names: list<string>, blockers: list<string>}
     */
    public function checkConversion(string $modelTypeId): array
    {
        $modelType = $this->modelTypes->find($modelTypeId);

        if (!$modelType) {
            return [
                'canConvert' => false,
                'direction'  => null,
                'itemCount'  => 0,
                'names'      => [],
                'blockers'   => ['Catégorie introuvable.'],
            ];
        }

        $category = $modelType->getCategory();

        if (ModelCategory::PRODUCT === $category) {
            return [
                'canConvert' => false,
                'direction'  => null,
                'itemCount'  => 0,
                'names'      => [],
                'blockers'   => ['La conversion n\'est pas disponible pour les catégories de produit.'],
            ];
        }

        if (ModelCategory::PIECE === $category) {
            return $this->checkPieceToComponent($modelTypeId, $modelType->getName());
        }

        return $this->checkComponentToPiece($modelTypeId, $modelType->getName());
    }

    /**
     * @return array{success: bool, convertedCount: int, error: null|string}
     */
    public function convert(string $modelTypeId): array
    {
        $check = $this->checkConversion($modelTypeId);

        if (!$check['canConvert']) {
            return [
                'success'        => false,
                'convertedCount' => 0,
                'error'          => implode(' ', $check['blockers']),
            ];
        }

        $modelType = $this->modelTypes->find($modelTypeId);

        if (!$modelType) {
            return ['success' => false, 'convertedCount' => 0, 'error' => 'Catégorie introuvable.'];
        }

        $category = $modelType->getCategory();

        $direction = ModelCategory::PIECE === $category ? 'piece_to_component' : 'component_to_piece';
        $names     = $check['names'];
        $modelName = $modelType->getName();
        $modelCode = $modelType->getCode();

        $this->connection->beginTransaction();

        try {
            if (ModelCategory::PIECE === $category) {
                $count = $this->convertPieceToComponent($modelTypeId);
            } else {
                $count = $this->convertComponentToPiece($modelTypeId);
            }

            $this->logConversionAudit($modelTypeId, $modelName, $modelCode, $direction, $count, $names);

            $this->connection->commit();

            return ['success' => true, 'convertedCount' => $count, 'error' => null];
        } catch (Throwable $e) {
            $this->connection->rollBack();

            return ['success' => false, 'convertedCount' => 0, 'error' => $e->getMessage()];
        }
    }

    /**
     * @return array{canConvert: bool, direction: string, itemCount: int, names: list<string>, blockers: list<string>}
     */
    private function checkPieceToComponent(string $modelTypeId, string $modelTypeName): array
    {
        $blockers = [];

        $pieceCount = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM pieces WHERE typepieceid = :id',
            ['id' => $modelTypeId],
        );

        $names = $this->connection->fetchFirstColumn(
            'SELECT name FROM pieces WHERE typepieceid = :id ORDER BY name',
            ['id' => $modelTypeId],
        );

        // Check machine links
        $machineLinked = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM machine_piece_links mpl
             JOIN pieces p ON mpl.pieceid = p.id
             WHERE p.typepieceid = :id',
            ['id' => $modelTypeId],
        );

        if ($machineLinked > 0) {
            $blockers[] = sprintf('%d pièce(s) liée(s) à des machines.', $machineLinked);
        }

        // Check name collision with existing composants
        $collisions = $this->connection->fetchFirstColumn(
            'SELECT p.name FROM pieces p
             WHERE p.typepieceid = :id
             AND p.name IN (SELECT c.name FROM composants c)',
            ['id' => $modelTypeId],
        );

        if ([] !== $collisions) {
            $blockers[] = sprintf(
                'Collision de nom avec des composants existants : %s.',
                implode(', ', $collisions),
            );
        }

        // Check ModelType name collision
        $nameCollision = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM model_types WHERE category = :cat AND name = :name AND id != :id',
            ['cat' => ModelCategory::COMPONENT->value, 'name' => $modelTypeName, 'id' => $modelTypeId],
        );

        if ($nameCollision > 0) {
            $blockers[] = sprintf('Une catégorie de composant « %s » existe déjà.', $modelTypeName);
        }

        return [
            'canConvert' => [] === $blockers,
            'direction'  => 'piece_to_component',
            'itemCount'  => $pieceCount,
            'names'      => $names,
            'blockers'   => $blockers,
        ];
    }

    /**
     * @return array{canConvert: bool, direction: string, itemCount: int, names: list<string>, blockers: list<string>}
     */
    private function checkComponentToPiece(string $modelTypeId, string $modelTypeName): array
    {
        $blockers = [];

        $composantCount = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM composants WHERE typecomposantid = :id',
            ['id' => $modelTypeId],
        );

        $names = $this->connection->fetchFirstColumn(
            'SELECT name FROM composants WHERE typecomposantid = :id ORDER BY name',
            ['id' => $modelTypeId],
        );

        // Check machine links
        $machineLinked = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM machine_component_links mcl
             JOIN composants c ON mcl.composantid = c.id
             WHERE c.typecomposantid = :id',
            ['id' => $modelTypeId],
        );

        if ($machineLinked > 0) {
            $blockers[] = sprintf('%d composant(s) lié(s) à des machines.', $machineLinked);
        }

        // Check slot tables for actual data (post-normalization architecture)
        $filledPieceSlots = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM composant_piece_slots cps
             JOIN composants c ON cps.composantid = c.id
             WHERE c.typecomposantid = :id AND cps.selectedpieceid IS NOT NULL',
            ['id' => $modelTypeId],
        );

        $filledSubSlots = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM composant_subcomponent_slots css
             JOIN composants c ON css.composantid = c.id
             WHERE c.typecomposantid = :id AND css.selectedcomposantid IS NOT NULL',
            ['id' => $modelTypeId],
        );

        $filledProductSlots = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM composant_product_slots cps
             JOIN composants c ON cps.composantid = c.id
             WHERE c.typecomposantid = :id AND cps.selectedproductid IS NOT NULL',
            ['id' => $modelTypeId],
        );

        if ($filledPieceSlots > 0 || $filledSubSlots > 0 || $filledProductSlots > 0) {
            $parts = [];
            if ($filledPieceSlots > 0) {
                $parts[] = sprintf('%d slot(s) pièce', $filledPieceSlots);
            }
            if ($filledSubSlots > 0) {
                $parts[] = sprintf('%d slot(s) sous-composant', $filledSubSlots);
            }
            if ($filledProductSlots > 0) {
                $parts[] = sprintf('%d slot(s) produit', $filledProductSlots);
            }
            $blockers[] = sprintf(
                'Des composants ont des données dans leurs slots : %s.',
                implode(', ', $parts),
            );
        }

        // Check name collision with existing pieces
        $collisions = $this->connection->fetchFirstColumn(
            'SELECT c.name FROM composants c
             WHERE c.typecomposantid = :id
             AND c.name IN (SELECT p.name FROM pieces p)',
            ['id' => $modelTypeId],
        );

        if ([] !== $collisions) {
            $blockers[] = sprintf(
                'Collision de nom avec des pièces existantes : %s.',
                implode(', ', $collisions),
            );
        }

        // Check ModelType name collision
        $nameCollision = (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM model_types WHERE category = :cat AND name = :name AND id != :id',
            ['cat' => ModelCategory::PIECE->value, 'name' => $modelTypeName, 'id' => $modelTypeId],
        );

        if ($nameCollision > 0) {
            $blockers[] = sprintf('Une catégorie de pièce « %s » existe déjà.', $modelTypeName);
        }

        return [
            'canConvert' => [] === $blockers,
            'direction'  => 'component_to_piece',
            'itemCount'  => $composantCount,
            'names'      => $names,
            'blockers'   => $blockers,
        ];
    }

    private function convertPieceToComponent(string $modelTypeId): int
    {
        // 1. Insert into composants from pieces
        $count = $this->connection->executeStatement(
            'INSERT INTO composants (id, name, reference, prix, structure, typecomposantid, productid, createdat, updatedat)
             SELECT id, name, reference, prix, NULL, typepieceid, productid, createdat, updatedat
             FROM pieces
             WHERE typepieceid = :id',
            ['id' => $modelTypeId],
        );

        // 2. Transfer constructeur link associations
        $this->connection->executeStatement(
            "INSERT INTO composant_constructeur_links (id, composantid, constructeurid, supplierreference, createdat, updatedat)
             SELECT 'cl' || substring(md5(random()::text || clock_timestamp()::text), 1, 24), pcl.pieceid, pcl.constructeurid, pcl.supplierreference, pcl.createdat, pcl.updatedat
             FROM piece_constructeur_links pcl
             WHERE pcl.pieceid IN (SELECT id FROM composants WHERE typecomposantid = :id)",
            ['id' => $modelTypeId],
        );

        $this->connection->executeStatement(
            'DELETE FROM piece_constructeur_links
             WHERE pieceid IN (SELECT id FROM pieces WHERE typepieceid = :id)',
            ['id' => $modelTypeId],
        );

        // 3. Transfer document references
        $this->connection->executeStatement(
            'UPDATE documents SET composantid = pieceid, pieceid = NULL
             WHERE pieceid IN (SELECT id FROM pieces WHERE typepieceid = :id)',
            ['id' => $modelTypeId],
        );

        // 4. Transfer custom_field_values references
        $this->connection->executeStatement(
            'UPDATE custom_field_values SET composantid = pieceid, pieceid = NULL
             WHERE pieceid IN (SELECT id FROM pieces WHERE typepieceid = :id)',
            ['id' => $modelTypeId],
        );

        // 5. Transfer custom_fields from typePiece to typeComposant
        $this->connection->executeStatement(
            'UPDATE custom_fields SET typecomposantid = typepieceid, typepieceid = NULL
             WHERE typepieceid = :id',
            ['id' => $modelTypeId],
        );

        // 6. Delete original pieces
        $this->connection->executeStatement(
            'DELETE FROM pieces WHERE typepieceid = :id',
            ['id' => $modelTypeId],
        );

        // 7. Update ModelType
        $this->updateModelTypeCategory($modelTypeId, ModelCategory::COMPONENT);

        return $count;
    }

    private function convertComponentToPiece(string $modelTypeId): int
    {
        // 1. Insert into pieces from composants
        $count = $this->connection->executeStatement(
            'INSERT INTO pieces (id, name, reference, prix, typepieceid, productid, createdat, updatedat)
             SELECT id, name, reference, prix, typecomposantid, productid, createdat, updatedat
             FROM composants
             WHERE typecomposantid = :id',
            ['id' => $modelTypeId],
        );

        // 2. Transfer constructeur link associations
        $this->connection->executeStatement(
            "INSERT INTO piece_constructeur_links (id, pieceid, constructeurid, supplierreference, createdat, updatedat)
             SELECT 'cl' || substring(md5(random()::text || clock_timestamp()::text), 1, 24), ccl.composantid, ccl.constructeurid, ccl.supplierreference, ccl.createdat, ccl.updatedat
             FROM composant_constructeur_links ccl
             WHERE ccl.composantid IN (SELECT id FROM pieces WHERE typepieceid = :id)",
            ['id' => $modelTypeId],
        );

        $this->connection->executeStatement(
            'DELETE FROM composant_constructeur_links
             WHERE composantid IN (SELECT id FROM composants WHERE typecomposantid = :id)',
            ['id' => $modelTypeId],
        );

        // 3. Transfer document references
        $this->connection->executeStatement(
            'UPDATE documents SET pieceid = composantid, composantid = NULL
             WHERE composantid IN (SELECT id FROM composants WHERE typecomposantid = :id)',
            ['id' => $modelTypeId],
        );

        // 4. Transfer custom_field_values references
        $this->connection->executeStatement(
            'UPDATE custom_field_values SET pieceid = composantid, composantid = NULL
             WHERE composantid IN (SELECT id FROM composants WHERE typecomposantid = :id)',
            ['id' => $modelTypeId],
        );

        // 5. Transfer custom_fields from typeComposant to typePiece
        $this->connection->executeStatement(
            'UPDATE custom_fields SET typepieceid = typecomposantid, typecomposantid = NULL
             WHERE typecomposantid = :id',
            ['id' => $modelTypeId],
        );

        // 6. Delete original composants (cascades to slot tables)
        $this->connection->executeStatement(
            'DELETE FROM composants WHERE typecomposantid = :id',
            ['id' => $modelTypeId],
        );

        // 6b. Clean up skeleton requirements that only apply to COMPONENT category
        $this->connection->executeStatement(
            'DELETE FROM skeleton_piece_requirements WHERE modeltypeid = :id',
            ['id' => $modelTypeId],
        );
        $this->connection->executeStatement(
            'DELETE FROM skeleton_subcomponent_requirements WHERE modeltypeid = :id',
            ['id' => $modelTypeId],
        );

        // 7. Update ModelType
        $this->updateModelTypeCategory($modelTypeId, ModelCategory::PIECE);

        return $count;
    }

    private function updateModelTypeCategory(string $modelTypeId, ModelCategory $category): void
    {
        $this->connection->executeStatement(
            'UPDATE model_types
             SET category = :cat,
                 updatedat = :now
             WHERE id = :id',
            [
                'cat' => $category->value,
                'now' => new DateTimeImmutable()->format('Y-m-d H:i:s'),
                'id'  => $modelTypeId,
            ],
        );
    }

    /**
     * @param list<string> $names
     */
    private function logConversionAudit(
        string $modelTypeId,
        string $modelName,
        string $modelCode,
        string $direction,
        int $convertedCount,
        array $names,
    ): void {
        $now = new DateTimeImmutable()->format('Y-m-d H:i:s');
        $id  = 'cl'.bin2hex(random_bytes(12));

        $snapshot = [
            'id'   => $modelTypeId,
            'name' => $modelName,
            'code' => $modelCode,
        ];

        $diff = [
            'direction'      => ['from' => null, 'to' => $direction],
            'convertedCount' => ['from' => null, 'to' => $convertedCount],
            'convertedNames' => ['from' => null, 'to' => $names],
        ];

        $this->connection->executeStatement(
            'INSERT INTO audit_logs (id, entitytype, entityid, action, diff, snapshot, actorprofileid, createdat)
             VALUES (:id, :entityType, :entityId, :action, :diff, :snapshot, :actor, :now)',
            [
                'id'         => $id,
                'entityType' => 'model_type',
                'entityId'   => $modelTypeId,
                'action'     => 'convert',
                'diff'       => json_encode($diff),
                'snapshot'   => json_encode($snapshot),
                'actor'      => $this->actorProfileResolver->resolve(),
                'now'        => $now,
            ],
        );
    }
}
