<?php

declare(strict_types=1);

namespace MauticPlugin\DealFlowBundle\Migrations;

use Doctrine\DBAL\Schema\Schema;
use Mautic\IntegrationsBundle\Migration\AbstractMigration;
use MauticPlugin\DealFlowBundle\Entity\Deal;
use MauticPlugin\DealFlowBundle\Entity\DealAttachment;
use MauticPlugin\DealFlowBundle\Entity\DealAuditEvent;
use MauticPlugin\DealFlowBundle\Entity\DealContactAssociation;
use MauticPlugin\DealFlowBundle\Entity\DealNote;
use MauticPlugin\DealFlowBundle\Entity\DealStage;
use MauticPlugin\DealFlowBundle\Entity\DealTask;
use MauticPlugin\DealFlowBundle\Entity\Pipeline;

final class Version_0_2_0 extends AbstractMigration
{
    /**
     * @var list<array{name: string, slug: string, stage_type: string}>
     */
    private const DEFAULT_STAGE_SEED = [
        ['name' => 'Identified', 'slug' => 'identified', 'stage_type' => 'open'],
        ['name' => 'Prospect', 'slug' => 'prospect', 'stage_type' => 'open'],
        ['name' => 'Opportunity', 'slug' => 'opportunity', 'stage_type' => 'open'],
        ['name' => 'Closed Won', 'slug' => 'closed-won', 'stage_type' => 'won'],
        ['name' => 'Closed Lost', 'slug' => 'closed-lost', 'stage_type' => 'lost'],
    ];

    protected function isApplicable(Schema $schema): bool
    {
        foreach ($this->requiredTables() as $tableName) {
            if (!$schema->hasTable($this->concatPrefix($tableName))) {
                return true;
            }
        }

        $connection = $this->entityManager->getConnection();

        try {
            $pipelineCount = (int) $connection->fetchOne(sprintf('SELECT COUNT(*) FROM `%s`', $this->concatPrefix(Pipeline::TABLE_NAME)));
            $stageCount    = (int) $connection->fetchOne(sprintf('SELECT COUNT(*) FROM `%s`', $this->concatPrefix(DealStage::TABLE_NAME)));

            return 0 === $pipelineCount || 0 === $stageCount;
        } catch (\Throwable) {
            return true;
        }
    }

    protected function up(): void
    {
        $connection = $this->entityManager->getConnection();

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `name` VARCHAR(191) NOT NULL,
                `is_default` TINYINT(1) NOT NULL DEFAULT 0,
                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                INDEX `dealflow_pipeline_default_idx` (`is_default`),
                PRIMARY KEY(`id`)
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(Pipeline::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `pipeline_id` INT NOT NULL,
                `name` VARCHAR(191) NOT NULL,
                `slug` VARCHAR(191) NOT NULL,
                `sort_order` INT NOT NULL,
                `stage_type` VARCHAR(32) NOT NULL,
                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                INDEX `dealflow_stage_slug_idx` (`slug`),
                INDEX `dealflow_stage_sort_idx` (`sort_order`),
                INDEX `dealflow_stage_pipeline_idx` (`pipeline_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_stage_pipeline_fk` FOREIGN KEY (`pipeline_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealStage::TABLE_NAME),
            $this->concatPrefix(Pipeline::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `pipeline_id` INT NOT NULL,
                `stage_id` INT NOT NULL,
                `company_id` INT DEFAULT NULL,
                `owner_id` INT DEFAULT NULL,
                `title` VARCHAR(191) NOT NULL,
                `amount` NUMERIC(19, 4) DEFAULT NULL,
                `currency` VARCHAR(3) NOT NULL,
                `confidence` INT DEFAULT NULL,
                `source` VARCHAR(191) DEFAULT NULL,
                `expected_close_date` DATE DEFAULT NULL,
                `status` VARCHAR(32) NOT NULL,
                `external_ref` VARCHAR(191) DEFAULT NULL,
                `description` LONGTEXT DEFAULT NULL,
                `created_by` INT DEFAULT NULL,
                `updated_by` INT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                INDEX `dealflow_deal_status_idx` (`status`),
                INDEX `dealflow_deal_owner_idx` (`owner_id`),
                INDEX `dealflow_deal_company_idx` (`company_id`),
                INDEX `dealflow_deal_external_ref_idx` (`external_ref`),
                INDEX `dealflow_deal_pipeline_idx` (`pipeline_id`),
                INDEX `dealflow_deal_stage_idx` (`stage_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_deal_pipeline_fk` FOREIGN KEY (`pipeline_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE,
                CONSTRAINT `dealflow_deal_stage_fk` FOREIGN KEY (`stage_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(Deal::TABLE_NAME),
            $this->concatPrefix(Pipeline::TABLE_NAME),
            $this->concatPrefix(DealStage::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `deal_id` INT NOT NULL,
                `contact_id` INT NOT NULL,
                `label` VARCHAR(191) DEFAULT NULL,
                `created_by` INT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                INDEX `dealflow_contact_assoc_contact_idx` (`contact_id`),
                INDEX `dealflow_contact_assoc_deal_idx` (`deal_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_contact_assoc_deal_fk` FOREIGN KEY (`deal_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealContactAssociation::TABLE_NAME),
            $this->concatPrefix(Deal::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `deal_id` INT NOT NULL,
                `body` LONGTEXT NOT NULL,
                `source_type` VARCHAR(32) NOT NULL,
                `source_ref` VARCHAR(191) DEFAULT NULL,
                `author_user_id` INT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                INDEX `dealflow_note_deal_idx` (`deal_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_note_deal_fk` FOREIGN KEY (`deal_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealNote::TABLE_NAME),
            $this->concatPrefix(Deal::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `deal_id` INT NOT NULL,
                `title` VARCHAR(191) NOT NULL,
                `description` LONGTEXT DEFAULT NULL,
                `status` VARCHAR(32) NOT NULL,
                `owner_id` INT DEFAULT NULL,
                `due_at` DATETIME DEFAULT NULL,
                `created_by` INT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                INDEX `dealflow_task_status_idx` (`status`),
                INDEX `dealflow_task_deal_idx` (`deal_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_task_deal_fk` FOREIGN KEY (`deal_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealTask::TABLE_NAME),
            $this->concatPrefix(Deal::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `deal_id` INT NOT NULL,
                `storage_path` VARCHAR(255) NOT NULL,
                `original_filename` VARCHAR(255) NOT NULL,
                `mime_type` VARCHAR(191) NOT NULL,
                `size_bytes` INT NOT NULL,
                `uploaded_by` INT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                INDEX `dealflow_attachment_deal_idx` (`deal_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_attachment_deal_fk` FOREIGN KEY (`deal_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealAttachment::TABLE_NAME),
            $this->concatPrefix(Deal::TABLE_NAME)
        ));

        $connection->executeStatement(sprintf(
            'CREATE TABLE IF NOT EXISTS `%s` (
                `id` INT AUTO_INCREMENT NOT NULL,
                `deal_id` INT NOT NULL,
                `event_type` VARCHAR(191) NOT NULL,
                `origin` VARCHAR(32) NOT NULL,
                `actor_type` VARCHAR(32) DEFAULT NULL,
                `actor_id` INT DEFAULT NULL,
                `payload_json` LONGTEXT NOT NULL,
                `created_at` DATETIME NOT NULL,
                INDEX `dealflow_audit_event_type_idx` (`event_type`),
                INDEX `dealflow_audit_deal_idx` (`deal_id`),
                PRIMARY KEY(`id`),
                CONSTRAINT `dealflow_audit_deal_fk` FOREIGN KEY (`deal_id`) REFERENCES `%s` (`id`) ON DELETE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB',
            $this->concatPrefix(DealAuditEvent::TABLE_NAME),
            $this->concatPrefix(Deal::TABLE_NAME)
        ));

        $this->seedDefaultPipelineAndStages();
    }

    /**
     * @return list<string>
     */
    private function requiredTables(): array
    {
        return [
            Pipeline::TABLE_NAME,
            DealStage::TABLE_NAME,
            Deal::TABLE_NAME,
            DealContactAssociation::TABLE_NAME,
            DealNote::TABLE_NAME,
            DealTask::TABLE_NAME,
            DealAttachment::TABLE_NAME,
            DealAuditEvent::TABLE_NAME,
        ];
    }

    private function seedDefaultPipelineAndStages(): void
    {
        $connection    = $this->entityManager->getConnection();
        $pipelineTable = $this->concatPrefix(Pipeline::TABLE_NAME);
        $stageTable    = $this->concatPrefix(DealStage::TABLE_NAME);
        $now           = (new \DateTimeImmutable())->format('Y-m-d H:i:s');
        $pipelineId    = $connection->fetchOne(sprintf('SELECT id FROM `%s` WHERE is_default = 1 ORDER BY id ASC LIMIT 1', $pipelineTable));

        if (false === $pipelineId) {
            $pipelineId = $connection->fetchOne(sprintf('SELECT id FROM `%s` ORDER BY id ASC LIMIT 1', $pipelineTable));
        }

        if (false === $pipelineId) {
            $connection->insert($pipelineTable, [
                'name'       => 'Sales Pipeline',
                'is_default' => 1,
                'is_active'  => 1,
                'created_at' => $now,
                'updated_at' => $now,
            ]);
            $pipelineId = (int) $connection->lastInsertId();
        } else {
            $pipelineId = (int) $pipelineId;
        }

        $existingStageCount = (int) $connection->fetchOne(
            sprintf('SELECT COUNT(*) FROM `%s` WHERE pipeline_id = :pipelineId', $stageTable),
            ['pipelineId' => $pipelineId]
        );

        if ($existingStageCount > 0) {
            return;
        }

        $sortOrder = 10;
        foreach (self::DEFAULT_STAGE_SEED as $stage) {
            $connection->insert($stageTable, [
                'pipeline_id' => $pipelineId,
                'name'        => $stage['name'],
                'slug'        => $stage['slug'],
                'sort_order'  => $sortOrder,
                'stage_type'  => $stage['stage_type'],
                'is_active'   => 1,
                'created_at'  => $now,
                'updated_at'  => $now,
            ]);

            $sortOrder += 10;
        }
    }
}
