Vendure 0.18.0 Migration

example-mysql-migration.ts

import {MigrationInterface, QueryRunner} from "typeorm";
import { addProductVariantsToProductChannels,
     migrateDefaultShippingCalculatorArgs,
     migrateOrderAdjustmentsToSurcharges,
     migrateOrderItemPromotionsAndTaxes, migrateOrderShippingToShippingLines } from '../migration-utils';

export class v18011609156046047 implements MigrationInterface {

   public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("ALTER TABLE `order_item` DROP FOREIGN KEY `FK_eed51be48640c21e1c76d3e9fbe`", undefined);
        await queryRunner.query("ALTER TABLE `order` DROP FOREIGN KEY `FK_4af424d3e7b2c3cb26e075e20fc`", undefined);
        await queryRunner.query("DROP INDEX `IDX_42187bb72520a713d625389489` ON `product_translation`", undefined);
        await queryRunner.query("CREATE TABLE `surcharge` (`createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `description` varchar(255) NOT NULL, `listPrice` int NOT NULL, `listPriceIncludesTax` tinyint NOT NULL, `sku` varchar(255) NOT NULL, `taxLines` text NOT NULL, `id` int NOT NULL AUTO_INCREMENT, `orderId` int NULL, `orderModificationId` int NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `order_modification` (`createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `note` varchar(255) NOT NULL, `priceChange` int NOT NULL, `shippingAddressChange` text NULL, `billingAddressChange` text NULL, `id` int NOT NULL AUTO_INCREMENT, `orderId` int NULL, `paymentId` int NULL, `refundId` int NULL, UNIQUE INDEX `REL_ad2991fa2933ed8b7f86a71633` (`paymentId`), UNIQUE INDEX `REL_cb66b63b6e97613013795eadbd` (`refundId`), PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `shipping_line` (`createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `listPrice` int NOT NULL, `listPriceIncludesTax` tinyint NOT NULL, `adjustments` text NOT NULL, `taxLines` text NOT NULL, `id` int NOT NULL AUTO_INCREMENT, `shippingMethodId` int NOT NULL, `orderId` int NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `product_variant_channels_channel` (`productVariantId` int NOT NULL, `channelId` int NOT NULL, INDEX `IDX_beeb2b3cd800e589f2213ae99d` (`productVariantId`), INDEX `IDX_d194bff171b62357688a5d0f55` (`channelId`), PRIMARY KEY (`productVariantId`, `channelId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `order_item_fulfillments_fulfillment` (`orderItemId` int NOT NULL, `fulfillmentId` int NOT NULL, INDEX `IDX_a568a3d5aa7f237edab624960b` (`orderItemId`), INDEX `IDX_8132041a647c28eb27ecc1691f` (`fulfillmentId`), PRIMARY KEY (`orderItemId`, `fulfillmentId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `order_modification_order_items_order_item` (`orderModificationId` int NOT NULL, `orderItemId` int NOT NULL, INDEX `IDX_a48502a38aded69d087a8ec08a` (`orderModificationId`), INDEX `IDX_9d631d7bd3d44af50eca535d72` (`orderItemId`), PRIMARY KEY (`orderModificationId`, `orderItemId`)) ENGINE=InnoDB", undefined);

        // Multiple Fulfillments per OrderItem
        await queryRunner.query("ALTER TABLE `order_item_fulfillments_fulfillment` ADD CONSTRAINT `FK_a568a3d5aa7f237edab624960b9` FOREIGN KEY (`orderItemId`) REFERENCES `order_item`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_item_fulfillments_fulfillment` ADD CONSTRAINT `FK_8132041a647c28eb27ecc1691fa` FOREIGN KEY (`fulfillmentId`) REFERENCES `fulfillment`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        /* add this line */ await queryRunner.query("INSERT INTO `order_item_fulfillments_fulfillment` (`orderItemId`, `fulfillmentId`) SELECT `id`, `fulfillmentId` FROM `order_item` WHERE `fulfillmentId` IS NOT NULL", undefined);
        await queryRunner.query("ALTER TABLE `order_item` DROP COLUMN `fulfillmentId`", undefined);

        // Implement FulfillmentHandlers
        /* add DEFAULT value */ await queryRunner.query("ALTER TABLE `fulfillment` ADD `handlerCode` varchar(255) NOT NULL DEFAULT 'manual-fulfillment'", undefined);
        /* add DEFAULT value */ await queryRunner.query("ALTER TABLE `shipping_method` ADD `fulfillmentHandlerCode` varchar(255) NOT NULL DEFAULT 'manual-fulfillment'", undefined);

        // Split taxes from adjustments: rename OrderItem `pendingAdjustments` to `adjustments`
        await queryRunner.query("ALTER TABLE `order_item` ADD `adjustments` text NOT NULL", undefined);
        /* add this line */ await queryRunner.query("UPDATE `order_item` SET `adjustments` = `pendingAdjustments`", undefined);
        await queryRunner.query("ALTER TABLE `order_item` ADD `taxLines` text NOT NULL", undefined);
        await migrateOrderItemPromotionsAndTaxes(queryRunner);
        await queryRunner.query("ALTER TABLE `order_item` DROP COLUMN `pendingAdjustments`", undefined);
        await queryRunner.query("ALTER TABLE `order_item` DROP COLUMN `taxRate`", undefined);

        // Rename OrderItem unitPrice to listPrice
        await queryRunner.query("ALTER TABLE `order_item` ADD `listPrice` int NOT NULL", undefined);
        await queryRunner.query("ALTER TABLE `order_item` ADD `listPriceIncludesTax` tinyint NOT NULL", undefined);
        /* add this line */ await queryRunner.query("UPDATE `order_item` SET `listPrice` = `unitPrice`", undefined);
        /* add this line */ await queryRunner.query("UPDATE `order_item` SET `listPriceIncludesTax` = false", undefined);
        await queryRunner.query("ALTER TABLE `order_item` DROP COLUMN `unitPrice`", undefined);

        // Rename Order.subTotal -> subTotalWithTax, subTotalBeforeTax -> subTotal
        await queryRunner.query("ALTER TABLE `order` ADD `subTotalWithTax` int NOT NULL", undefined);
        /* add this line */ await queryRunner.query("UPDATE `order` SET `subTotalWithTax` = `subTotal`", undefined);
        /* add this line */ await queryRunner.query("UPDATE `order` SET `subTotal` = `subTotalBeforeTax`", undefined);
        await queryRunner.query("ALTER TABLE `order` DROP COLUMN `subTotalBeforeTax`", undefined);

        // Convert order-level adjustments to Surcharges
        await migrateOrderAdjustmentsToSurcharges(queryRunner);
        await queryRunner.query("ALTER TABLE `order` DROP COLUMN `pendingAdjustments`", undefined);

        // Update default ShippingCalculator arguments
        await migrateDefaultShippingCalculatorArgs(queryRunner);

        // Multiple shipping lines
        await queryRunner.query("ALTER TABLE `shipping_line` ADD CONSTRAINT `FK_e2e7642e1e88167c1dfc827fdf3` FOREIGN KEY (`shippingMethodId`) REFERENCES `shipping_method`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `shipping_line` ADD CONSTRAINT `FK_c9f34a440d490d1b66f6829b86c` FOREIGN KEY (`orderId`) REFERENCES `order`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
        await migrateOrderShippingToShippingLines(queryRunner);
        await queryRunner.query("ALTER TABLE `order` DROP COLUMN `shippingMethodId`", undefined);

        // ChannelAware ProductVariants
        await queryRunner.query("ALTER TABLE `product_variant_channels_channel` ADD CONSTRAINT `FK_beeb2b3cd800e589f2213ae99d6` FOREIGN KEY (`productVariantId`) REFERENCES `product_variant`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `product_variant_channels_channel` ADD CONSTRAINT `FK_d194bff171b62357688a5d0f559` FOREIGN KEY (`channelId`) REFERENCES `channel`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await addProductVariantsToProductChannels(queryRunner);

        await queryRunner.query("ALTER TABLE `product_variant` DROP COLUMN `lastPriceValue`", undefined);
        await queryRunner.query("ALTER TABLE `surcharge` ADD CONSTRAINT `FK_154eb685f9b629033bd266df7fa` FOREIGN KEY (`orderId`) REFERENCES `order`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `surcharge` ADD CONSTRAINT `FK_a49c5271c39cc8174a0535c8088` FOREIGN KEY (`orderModificationId`) REFERENCES `order_modification`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_modification` ADD CONSTRAINT `FK_1df5bc14a47ef24d2e681f45598` FOREIGN KEY (`orderId`) REFERENCES `order`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_modification` ADD CONSTRAINT `FK_ad2991fa2933ed8b7f86a716338` FOREIGN KEY (`paymentId`) REFERENCES `payment`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_modification` ADD CONSTRAINT `FK_cb66b63b6e97613013795eadbd5` FOREIGN KEY (`refundId`) REFERENCES `refund`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_modification_order_items_order_item` ADD CONSTRAINT `FK_a48502a38aded69d087a8ec08ad` FOREIGN KEY (`orderModificationId`) REFERENCES `order_modification`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `order_modification_order_items_order_item` ADD CONSTRAINT `FK_9d631d7bd3d44af50eca535d728` FOREIGN KEY (`orderItemId`) REFERENCES `order_item`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
   }

   public async down(queryRunner: QueryRunner): Promise<any> {
        // omitted
   }

}

example-postgres-migration.ts

import {MigrationInterface, QueryRunner} from "typeorm";
import { addProductVariantsToProductChannels,
     migrateDefaultShippingCalculatorArgs,
     migrateOrderAdjustmentsToSurcharges,
     migrateOrderItemPromotionsAndTaxes, migrateOrderShippingToShippingLines } from '../migration-utils';

export class v0181609246816877 implements MigrationInterface {

   public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "order_item" DROP CONSTRAINT "FK_eed51be48640c21e1c76d3e9fbe"`, undefined);
        await queryRunner.query(`ALTER TABLE "order" DROP CONSTRAINT "FK_4af424d3e7b2c3cb26e075e20fc"`, undefined);
        await queryRunner.query(`DROP INDEX "IDX_42187bb72520a713d625389489"`, undefined);
        await queryRunner.query(`CREATE TABLE "surcharge" ("createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "description" character varying NOT NULL, "listPrice" integer NOT NULL, "listPriceIncludesTax" boolean NOT NULL, "sku" character varying NOT NULL, "taxLines" text NOT NULL, "id" SERIAL NOT NULL, "orderId" integer, "orderModificationId" integer, CONSTRAINT "PK_a62b89257bcc802b5d77346f432" PRIMARY KEY ("id"))`, undefined);
        await queryRunner.query(`CREATE TABLE "order_modification" ("createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "note" character varying NOT NULL, "priceChange" integer NOT NULL, "shippingAddressChange" text, "billingAddressChange" text, "id" SERIAL NOT NULL, "orderId" integer, "paymentId" integer, "refundId" integer, CONSTRAINT "REL_ad2991fa2933ed8b7f86a71633" UNIQUE ("paymentId"), CONSTRAINT "REL_cb66b63b6e97613013795eadbd" UNIQUE ("refundId"), CONSTRAINT "PK_cccf2e1612694eeb1e5b6760ffa" PRIMARY KEY ("id"))`, undefined);
        await queryRunner.query(`CREATE TABLE "shipping_line" ("createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "listPrice" integer NOT NULL, "listPriceIncludesTax" boolean NOT NULL, "adjustments" text NOT NULL, "taxLines" text NOT NULL, "id" SERIAL NOT NULL, "shippingMethodId" integer NOT NULL, "orderId" integer, CONSTRAINT "PK_890522bfc44a4b6eb7cb1e52609" PRIMARY KEY ("id"))`, undefined);
        await queryRunner.query(`CREATE TABLE "product_variant_channels_channel" ("productVariantId" integer NOT NULL, "channelId" integer NOT NULL, CONSTRAINT "PK_1a10ca648c3d73c0f2b455ae191" PRIMARY KEY ("productVariantId", "channelId"))`, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_beeb2b3cd800e589f2213ae99d" ON "product_variant_channels_channel" ("productVariantId") `, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_d194bff171b62357688a5d0f55" ON "product_variant_channels_channel" ("channelId") `, undefined);
        await queryRunner.query(`CREATE TABLE "order_item_fulfillments_fulfillment" ("orderItemId" integer NOT NULL, "fulfillmentId" integer NOT NULL, CONSTRAINT "PK_efd1c390f21a42e083d13c1e36c" PRIMARY KEY ("orderItemId", "fulfillmentId"))`, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_a568a3d5aa7f237edab624960b" ON "order_item_fulfillments_fulfillment" ("orderItemId") `, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_8132041a647c28eb27ecc1691f" ON "order_item_fulfillments_fulfillment" ("fulfillmentId") `, undefined);
        await queryRunner.query(`CREATE TABLE "order_modification_order_items_order_item" ("orderModificationId" integer NOT NULL, "orderItemId" integer NOT NULL, CONSTRAINT "PK_a930dbed390bdf667959c41a2f5" PRIMARY KEY ("orderModificationId", "orderItemId"))`, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_a48502a38aded69d087a8ec08a" ON "order_modification_order_items_order_item" ("orderModificationId") `, undefined);
        await queryRunner.query(`CREATE INDEX "IDX_9d631d7bd3d44af50eca535d72" ON "order_modification_order_items_order_item" ("orderItemId") `, undefined);

        // Multiple Fulfillments per OrderItem
        await queryRunner.query(`ALTER TABLE "order_item_fulfillments_fulfillment" ADD CONSTRAINT "FK_a568a3d5aa7f237edab624960b9" FOREIGN KEY ("orderItemId") REFERENCES "order_item"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_item_fulfillments_fulfillment" ADD CONSTRAINT "FK_8132041a647c28eb27ecc1691fa" FOREIGN KEY ("fulfillmentId") REFERENCES "fulfillment"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        /* add this line */ await queryRunner.query(`INSERT INTO "order_item_fulfillments_fulfillment" ("orderItemId", "fulfillmentId") SELECT "id", "fulfillmentId" FROM "order_item" WHERE "fulfillmentId" IS NOT NULL`, undefined);
        await queryRunner.query(`ALTER TABLE "order_item" DROP COLUMN "fulfillmentId"`, undefined);

        // Implement FulfillmentHandlers
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "fulfillment" ADD "handlerCode" character varying NOT NULL DEFAULT 'manual-fulfillment'`, undefined);
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "shipping_method" ADD "fulfillmentHandlerCode" character varying NOT NULL DEFAULT 'manual-fulfillment'`, undefined);

        // Split taxes from adjustments: rename OrderItem `pendingAdjustments` to `adjustments`
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "order_item" ADD "adjustments" text NOT NULL DEFAULT '[]'`, undefined);
        /* add this line */ await queryRunner.query(`UPDATE "order_item" SET "adjustments" = "pendingAdjustments"`, undefined);
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "order_item" ADD "taxLines" text NOT NULL DEFAULT '[]'`, undefined);
        await migrateOrderItemPromotionsAndTaxes(queryRunner);
        await queryRunner.query(`ALTER TABLE "order_item" DROP COLUMN "pendingAdjustments"`, undefined);
        await queryRunner.query(`ALTER TABLE "order_item" DROP COLUMN "taxRate"`, undefined);

        // Rename OrderItem unitPrice to listPrice
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "order_item" ADD "listPrice" integer NOT NULL DEFAULT 0`, undefined);
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "order_item" ADD "listPriceIncludesTax" boolean NOT NULL DEFAULT false`, undefined);
        /* add this line */ await queryRunner.query(`UPDATE "order_item" SET "listPrice" = "unitPrice"`, undefined);
        await queryRunner.query(`ALTER TABLE "order_item" DROP COLUMN "unitPrice"`, undefined);

        // Rename Order.subTotal -> subTotalWithTax, subTotalBeforeTax -> subTotal
        /* add DEFAULT value */ await queryRunner.query(`ALTER TABLE "order" ADD "subTotalWithTax" integer NOT NULL DEFAULT 0`, undefined);
        /* add this line */ await queryRunner.query(`UPDATE "order" SET "subTotalWithTax" = "subTotal", "subTotal" = "subTotalBeforeTax"`, undefined);
        await queryRunner.query(`ALTER TABLE "order" DROP COLUMN "subTotalBeforeTax"`, undefined);

        // Convert order-level adjustments to Surcharges
        await migrateOrderAdjustmentsToSurcharges(queryRunner);
        await queryRunner.query(`ALTER TABLE "order" DROP COLUMN "pendingAdjustments"`, undefined);

        // Update default ShippingCalculator arguments
        await migrateDefaultShippingCalculatorArgs(queryRunner);

        // Multiple shipping lines
        await queryRunner.query(`ALTER TABLE "shipping_line" ADD CONSTRAINT "FK_e2e7642e1e88167c1dfc827fdf3" FOREIGN KEY ("shippingMethodId") REFERENCES "shipping_method"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "shipping_line" ADD CONSTRAINT "FK_c9f34a440d490d1b66f6829b86c" FOREIGN KEY ("orderId") REFERENCES "order"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`, undefined);
        await migrateOrderShippingToShippingLines(queryRunner);
        await queryRunner.query(`ALTER TABLE "order" DROP COLUMN "shippingMethodId"`, undefined);

        // ChannelAware ProductVariants
        await queryRunner.query(`ALTER TABLE "product_variant_channels_channel" ADD CONSTRAINT "FK_beeb2b3cd800e589f2213ae99d6" FOREIGN KEY ("productVariantId") REFERENCES "product_variant"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "product_variant_channels_channel" ADD CONSTRAINT "FK_d194bff171b62357688a5d0f559" FOREIGN KEY ("channelId") REFERENCES "channel"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await addProductVariantsToProductChannels(queryRunner);

        await queryRunner.query(`ALTER TABLE "product_variant" DROP COLUMN "lastPriceValue"`, undefined);
        await queryRunner.query(`ALTER TABLE "surcharge" ADD CONSTRAINT "FK_154eb685f9b629033bd266df7fa" FOREIGN KEY ("orderId") REFERENCES "order"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "surcharge" ADD CONSTRAINT "FK_a49c5271c39cc8174a0535c8088" FOREIGN KEY ("orderModificationId") REFERENCES "order_modification"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_modification" ADD CONSTRAINT "FK_1df5bc14a47ef24d2e681f45598" FOREIGN KEY ("orderId") REFERENCES "order"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_modification" ADD CONSTRAINT "FK_ad2991fa2933ed8b7f86a716338" FOREIGN KEY ("paymentId") REFERENCES "payment"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_modification" ADD CONSTRAINT "FK_cb66b63b6e97613013795eadbd5" FOREIGN KEY ("refundId") REFERENCES "refund"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_modification_order_items_order_item" ADD CONSTRAINT "FK_a48502a38aded69d087a8ec08ad" FOREIGN KEY ("orderModificationId") REFERENCES "order_modification"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
        await queryRunner.query(`ALTER TABLE "order_modification_order_items_order_item" ADD CONSTRAINT "FK_9d631d7bd3d44af50eca535d728" FOREIGN KEY ("orderItemId") REFERENCES "order_item"("id") ON DELETE CASCADE ON UPDATE NO ACTION`, undefined);
   }

   public async down(queryRunner: QueryRunner): Promise<any> {
        // omitted
   }

}

migration-utils.ts

import { QueryRunner } from 'typeorm';

export async function migrateOrderItemPromotionsAndTaxes(queryRunner: QueryRunner) {
    const items = await q(
        queryRunner,
        {
            mysql: 'SELECT `id`, `adjustments`, `taxRate` from `order_item`',
            postgres: 'SELECT "id", "adjustments", "taxRate" from "order_item"',
        },
        [],
    );
    for (const item of items) {
        const adjustments = JSON.parse(item.adjustments);
        const promotions = [];
        const taxLines = [];
        for (const adjustment of adjustments) {
            if (adjustment.type === 'TAX') {
                taxLines.push({ description: 'tax', taxRate: item.taxRate });
            }
            if (adjustment.type === 'PROMOTION') {
                promotions.push(adjustment);
            }
        }
        await q(
            queryRunner,
            {
                mysql: 'UPDATE `order_item` SET `adjustments` = ? WHERE `id` = ?',
                postgres: 'UPDATE "order_item" SET "adjustments" = $1 WHERE "id" = $2',
            },
            [JSON.stringify(promotions), item.id],
        );

        await q(
            queryRunner,
            {
                mysql: 'UPDATE `order_item` SET `taxLines` = ? WHERE `id` = ?',
                postgres: 'UPDATE "order_item" SET "taxLines" = $1 WHERE "id" = $2',
            },
            [JSON.stringify(taxLines), item.id],
        );
    }
}

export async function migrateOrderShippingToShippingLines(queryRunner: QueryRunner) {
    const orders = await q(
        queryRunner,
        {
            mysql:
                'SELECT `id`, `shipping`, `shippingWithTax`, `shippingMethodId` from `order` WHERE `shippingMethodId` IS NOT NULL',
            postgres:
                'SELECT "id", "shipping", "shippingWithTax", "shippingMethodId" from "order" WHERE "shippingMethodId" IS NOT NULL',
        },
        [],
    );
    for (const order of orders) {
        const shippingTax = (order.shippingWithTax / order.shipping) * 100 - 100;
        const taxLines = JSON.stringify([{ description: 'shipping tax', taxRate: shippingTax.toFixed(2) }]);
        await q(
            queryRunner,
            {
                mysql:
                    'INSERT INTO `shipping_line` (`listPrice`, `listPriceIncludesTax`, `adjustments`, `taxLines`, `shippingMethodId`, `orderId`) ' +
                    ' VALUES (?, ?, ?, ?, ?, ?)',
                postgres:
                    'INSERT INTO "shipping_line" ("listPrice", "listPriceIncludesTax", "adjustments", "taxLines", "shippingMethodId", "orderId") ' +
                    ' VALUES ($1, $2, $3, $4, $5, $6)',
            },
            [order.shipping, false, '[]', taxLines, order.shippingMethodId, order.id],
        );
    }
}

export async function addProductVariantsToProductChannels(queryRunner: QueryRunner) {
    const variants = await q(
        queryRunner,
        {
            mysql:
                'SELECT `v`.`id` AS productVariantId, `pcc`.`productId` AS productId, `pcc`.`channelId` as channelId FROM ' +
                '`product_variant` AS `v` INNER JOIN `product_channels_channel` AS `pcc` ON `pcc`.`productId` = `v`.`productId`',
            postgres:
                'SELECT "v"."id" AS "productVariantId", "pcc"."productId" AS "productId", "pcc"."channelId" as "channelId" FROM ' +
                '"product_variant" AS "v" INNER JOIN "product_channels_channel" AS "pcc" ON "pcc"."productId" = "v"."productId"',
        },
        [],
    );
    for (const variant of variants) {
        await q(
            queryRunner,
            {
                mysql:
                    'INSERT INTO `product_variant_channels_channel` (`productVariantId`, `channelId`) VALUES (?, ?)',
                postgres:
                    'INSERT INTO "product_variant_channels_channel" ("productVariantId", "channelId") VALUES ($1, $2)',
            },
            [variant.productVariantId, variant.channelId],
        );
    }
}

export async function migrateOrderAdjustmentsToSurcharges(queryRunner: QueryRunner) {
    const orders = await q(queryRunner, {
        mysql: 'SELECT `id`, `pendingAdjustments` from `order`',
        postgres: 'SELECT "id", "pendingAdjustments" from "order"',
    });
    for (const order of orders) {
        const adjustments = JSON.parse(order.pendingAdjustments);
        for (const adjustment of adjustments) {
            await q(
                queryRunner,
                {
                    mysql:
                        'INSERT INTO `surcharge` (`description`, `sku`, `listPrice`, `listPriceIncludesTax`, `taxLines`, `orderId`) VALUES (?, ?, ?, ?, ?, ?)',
                    postgres:
                        'INSERT INTO "surcharge" ("description", "sku", "listPrice", "listPriceIncludesTax", "taxLines", "orderId") VALUES ($1, $2, $3, $4, $5, $6)',
                },
                [adjustment.description, '', adjustment.amount, true, '[]', order.id],
            );
            await q(
                queryRunner,
                {
                    mysql:
                        'UPDATE `order` SET `subTotal` = `subTotal` + ?, `subTotalWithTax` = `subTotalWithTax` + ? WHERE `id` = ?',
                    postgres:
                        'UPDATE "order" SET "subTotal" = "subTotal" + $1, "subTotalWithTax" = "subTotalWithTax" + $2 WHERE "id" = $3',
                },
                [adjustment.amount, adjustment.amount, order.id],
            );
        }
    }
}

export async function migrateDefaultShippingCalculatorArgs(queryRunner: QueryRunner) {
    const methods = await q(queryRunner, {
        mysql: 'SELECT `id`, `calculator` from `shipping_method`',
        postgres: 'SELECT "id", "calculator" from "shipping_method"',
    });
    for (const method of methods) {
        const calculator = JSON.parse(method.calculator);
        if (calculator.code === 'default-shipping-calculator') {
            calculator.args.push({ name: 'includesTax', value: 'auto' });
            await q(
                queryRunner,
                {
                    mysql: 'UPDATE `shipping_method` SET `calculator` = ? WHERE `id` = ?',
                    postgres: 'UPDATE "shipping_method" SET "calculator" = $1 WHERE "id" = $2',
                },
                [JSON.stringify(calculator), method.id],
            );
        }
    }
}

function q(queryRunner: QueryRunner, query: { mysql: string; postgres: string }, params: any[] = []) {
    return queryRunner.query(isPostgres(queryRunner) ? query.postgres : query.mysql, params);
}

function isPostgres(queryRunner: QueryRunner): boolean {
    const { type } = queryRunner.connection.options;
    return type === 'postgres' || type === 'aurora-data-api-pg' || type === 'cockroachdb';
}