dada

import { MigrationInterface, QueryRunner } from 'typeorm';

export class CategoryTree1738611197500 implements MigrationInterface {
    name = 'CategoryTree1738611197500';

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(
            `ALTER TABLE \`product\` 
             DROP FOREIGN KEY \`FK_ff0c0301a95e517153df97f6812\``,
        );

        await queryRunner.query(`RENAME TABLE category TO category_backup`);

        await queryRunner.query(
            `CREATE TABLE \`category\` (
                \`id\` int NOT NULL AUTO_INCREMENT,
                \`name\` varchar(255) NOT NULL,
                \`type\` enum('series','category') NOT NULL,
                \`isActive\` tinyint NOT NULL DEFAULT 1,
                \`positionInGroup\` int NOT NULL,
                \`parentId\` int NULL,
                PRIMARY KEY (\`id\`)
            ) ENGINE=InnoDB`,
        );

        await queryRunner.query(
            `ALTER TABLE \`category\` 
             ADD CONSTRAINT \`FK_category_parent\` 
             FOREIGN KEY (\`parentId\`) REFERENCES \`category\`(\`id\`)
             ON DELETE NO ACTION ON UPDATE NO ACTION`,
        );

        await queryRunner.query(
            `CREATE TABLE \`category_closure\` (
                \`id\` int NOT NULL AUTO_INCREMENT,
                \`ancestorId\` int NOT NULL,
                \`descendantId\` int NOT NULL,
                PRIMARY KEY (\`id\`),
                KEY \`IDX_CLOSURE_ANCESTOR\` (\`ancestorId\`),
                KEY \`IDX_CLOSURE_DESCENDANT\` (\`descendantId\`),
                CONSTRAINT \`FK_CLOSURE_ANCESTOR\` FOREIGN KEY (\`ancestorId\`) REFERENCES \`category\` (\`id\`) ON DELETE CASCADE,
                CONSTRAINT \`FK_CLOSURE_DESCENDANT\` FOREIGN KEY (\`descendantId\`) REFERENCES \`category\` (\`id\`) ON DELETE CASCADE
            ) ENGINE=InnoDB`,
        );

        await queryRunner.query(
            `CREATE TABLE \`product_category\` (
                \`productId\` int NOT NULL,
                \`categoryId\` int NOT NULL,
                PRIMARY KEY (\`productId\`, \`categoryId\`),
                CONSTRAINT \`FK_product_category_product\` FOREIGN KEY (\`productId\`) REFERENCES \`product\`(\`id\`) ON DELETE CASCADE ON UPDATE NO ACTION,
                CONSTRAINT \`FK_product_category_category\` FOREIGN KEY (\`categoryId\`) REFERENCES \`category\`(\`id\`) ON DELETE CASCADE ON UPDATE NO ACTION
            ) ENGINE=InnoDB`,
        );

        await queryRunner.query(
            `INSERT INTO \`category\` (name, type, isActive, positionInGroup, parentId)
             VALUES ('Kategorie', 'category', 1, 1, NULL)`,
        );
        await queryRunner.query(
            `INSERT INTO \`category\` (name, type, isActive, positionInGroup, parentId)
             VALUES ('Řady', 'series', 1, 2, NULL)`,
        );

        const seriesRootRow = await queryRunner.query(`SELECT id FROM \`category\` WHERE name = 'Řady'`);
        if (!seriesRootRow || seriesRootRow.length === 0) {
            throw new Error("Cannot find the root category 'Řady'");
        }
        const seriesRootId = seriesRootRow[0].id;

        const minRows = await queryRunner.query(`SELECT MIN(position) as minPos FROM \`category_backup\``);
        const minPosition = minRows[0].minPos ? Number(minRows[0].minPos) : 0;

        await queryRunner.query(
            `INSERT INTO \`category\` (name, type, isActive, positionInGroup, parentId)
             SELECT name, 'series', isActive, (position - ${minPosition} + 1), ${seriesRootId}
             FROM \`category_backup\``,
        );

        await queryRunner.query(`
            INSERT INTO product_category (productId, categoryId)
            SELECT p.id, c.id 
            FROM product p
            JOIN category_backup cb ON p.categoryId = cb.id
            JOIN category c ON c.name = cb.name
        `);

        await queryRunner.query(`
            ALTER TABLE product
            DROP COLUMN categoryId
        `);

        await queryRunner.query(`
            INSERT INTO category_closure (ancestorId, descendantId)
            SELECT c.id, c.id FROM category c
            UNION ALL
            SELECT c.id, c2.id 
            FROM category c
            JOIN category c2 ON c2.parentId = c.id
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            ALTER TABLE product
            ADD COLUMN categoryId int,
            ADD CONSTRAINT FK_ff0c0301a95e517153df97f6812 
            FOREIGN KEY (categoryId) 
            REFERENCES category(id)
        `);

        await queryRunner.query(`
            UPDATE product p
            JOIN product_category pc ON p.id = pc.productId
            JOIN category c ON c.id = pc.categoryId
            JOIN category_backup cb ON cb.name = c.name
            SET p.categoryId = cb.id
        `);

        await queryRunner.query(`DROP TABLE \`product_category\``);
        await queryRunner.query(`DROP TABLE \`category_closure\``);
        await queryRunner.query(`DROP TABLE \`category\``);
        await queryRunner.query(`RENAME TABLE category_backup TO category`);
    }
}