//! Introduce product variants as the purchasable unit.
//!
//! A product becomes a presentation grouping (name, description, images,
//! category, tags, percentage discount profiles). Each product owns one or more
//! `product_variants`, and the variant is what carries the things that actually
//! differ between options: a free-text `label` (e.g. "rolovaná 90cm x 10m",
//! "5ml"), its own `sku`, `stock`, regular `price_cents`, and its own optional
//! public/business quick-sale prices.
//!
//! This migration:
//!   1. creates `product_variants`,
//!   2. backfills one variant per existing product from the product's current
//!      price/stock/sku/sale columns,
//!   3. moves the per-account negotiated price and collision-resolution tables
//!      from keying on `product_id` to `variant_id`,
//!   4. snapshots the variant onto `order_items`,
//!   5. drops the now-moved purchasable columns from `products`.

use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, m: &SchemaManager) -> Result<(), DbErr> {
        let db = m.get_connection();

        // 1. The variants table.
        db.execute_unprepared(
            r#"
            CREATE TABLE product_variants (
                id          SERIAL PRIMARY KEY,
                created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
                product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
                label       VARCHAR NOT NULL DEFAULT '',
                position    INTEGER NOT NULL DEFAULT 0,
                sku         VARCHAR,
                stock       INTEGER NOT NULL DEFAULT 0,
                price_cents BIGINT NOT NULL,
                sale_price_cents          BIGINT,
                business_sale_price_cents BIGINT
            );
            CREATE INDEX idx_product_variants_product ON product_variants (product_id);
            "#,
        )
        .await?;

        // 2. One variant per existing product, carrying its current pricing.
        db.execute_unprepared(
            r#"
            INSERT INTO product_variants
                (product_id, label, position, sku, stock,
                 price_cents, sale_price_cents, business_sale_price_cents)
            SELECT id, '', 0, sku, stock,
                   price_cents, sale_price_cents, business_sale_price_cents
            FROM products;
            "#,
        )
        .await?;

        // 3a. Negotiated prices: product_id -> variant_id.
        db.execute_unprepared(
            r#"
            ALTER TABLE account_product_prices ADD COLUMN variant_id INTEGER;
            UPDATE account_product_prices a
                SET variant_id = pv.id
                FROM product_variants pv
                WHERE pv.product_id = a.product_id;
            DROP INDEX IF EXISTS idx_account_product_prices_user_product_unique;
            ALTER TABLE account_product_prices DROP COLUMN product_id;
            ALTER TABLE account_product_prices ALTER COLUMN variant_id SET NOT NULL;
            ALTER TABLE account_product_prices
                ADD CONSTRAINT fk_account_product_prices_variant
                FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE;
            CREATE UNIQUE INDEX idx_account_product_prices_user_variant_unique
                ON account_product_prices (user_id, variant_id);
            "#,
        )
        .await?;

        // 3b. Collision resolutions: product_id -> variant_id.
        db.execute_unprepared(
            r#"
            ALTER TABLE account_product_resolutions ADD COLUMN variant_id INTEGER;
            UPDATE account_product_resolutions a
                SET variant_id = pv.id
                FROM product_variants pv
                WHERE pv.product_id = a.product_id;
            DROP INDEX IF EXISTS idx_account_product_resolutions_unique;
            ALTER TABLE account_product_resolutions DROP COLUMN product_id;
            ALTER TABLE account_product_resolutions ALTER COLUMN variant_id SET NOT NULL;
            ALTER TABLE account_product_resolutions
                ADD CONSTRAINT fk_account_product_resolutions_variant
                FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE;
            CREATE UNIQUE INDEX idx_account_product_resolutions_unique
                ON account_product_resolutions (user_id, variant_id);
            "#,
        )
        .await?;

        // 4. Snapshot the variant on order lines (label is frozen at order time;
        //    the FK is nullable + SET NULL so deleting a variant keeps history).
        db.execute_unprepared(
            r#"
            ALTER TABLE order_items ADD COLUMN variant_label VARCHAR NOT NULL DEFAULT '';
            ALTER TABLE order_items ADD COLUMN variant_id INTEGER
                REFERENCES product_variants(id) ON DELETE SET NULL;
            "#,
        )
        .await?;

        // 5. Drop the purchasable columns now owned by the variant.
        db.execute_unprepared(
            r#"
            ALTER TABLE products DROP COLUMN price_cents;
            ALTER TABLE products DROP COLUMN sale_price_cents;
            ALTER TABLE products DROP COLUMN business_sale_price_cents;
            ALTER TABLE products DROP COLUMN sku;
            ALTER TABLE products DROP COLUMN stock;
            "#,
        )
        .await?;

        Ok(())
    }

    async fn down(&self, m: &SchemaManager) -> Result<(), DbErr> {
        let db = m.get_connection();

        // Restore product columns from each product's first variant.
        db.execute_unprepared(
            r#"
            ALTER TABLE products ADD COLUMN price_cents BIGINT NOT NULL DEFAULT 0;
            ALTER TABLE products ADD COLUMN sale_price_cents BIGINT;
            ALTER TABLE products ADD COLUMN business_sale_price_cents BIGINT;
            ALTER TABLE products ADD COLUMN sku VARCHAR;
            ALTER TABLE products ADD COLUMN stock INTEGER NOT NULL DEFAULT 0;
            UPDATE products p SET
                price_cents = pv.price_cents,
                sale_price_cents = pv.sale_price_cents,
                business_sale_price_cents = pv.business_sale_price_cents,
                sku = pv.sku,
                stock = pv.stock
            FROM (
                SELECT DISTINCT ON (product_id) product_id, price_cents,
                       sale_price_cents, business_sale_price_cents, sku, stock
                FROM product_variants ORDER BY product_id, position, id
            ) pv
            WHERE pv.product_id = p.id;
            "#,
        )
        .await?;

        db.execute_unprepared(
            r#"
            ALTER TABLE order_items DROP COLUMN variant_id;
            ALTER TABLE order_items DROP COLUMN variant_label;
            "#,
        )
        .await?;

        db.execute_unprepared(
            r#"
            ALTER TABLE account_product_resolutions ADD COLUMN product_id INTEGER;
            UPDATE account_product_resolutions a
                SET product_id = pv.product_id
                FROM product_variants pv WHERE pv.id = a.variant_id;
            DROP INDEX IF EXISTS idx_account_product_resolutions_unique;
            ALTER TABLE account_product_resolutions DROP COLUMN variant_id;
            ALTER TABLE account_product_resolutions ALTER COLUMN product_id SET NOT NULL;
            ALTER TABLE account_product_resolutions
                ADD CONSTRAINT fk_account_product_resolutions_product
                FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE;
            CREATE UNIQUE INDEX idx_account_product_resolutions_unique
                ON account_product_resolutions (user_id, product_id);
            "#,
        )
        .await?;

        db.execute_unprepared(
            r#"
            ALTER TABLE account_product_prices ADD COLUMN product_id INTEGER;
            UPDATE account_product_prices a
                SET product_id = pv.product_id
                FROM product_variants pv WHERE pv.id = a.variant_id;
            DROP INDEX IF EXISTS idx_account_product_prices_user_variant_unique;
            ALTER TABLE account_product_prices DROP COLUMN variant_id;
            ALTER TABLE account_product_prices ALTER COLUMN product_id SET NOT NULL;
            ALTER TABLE account_product_prices
                ADD CONSTRAINT fk_account_product_prices_product
                FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE;
            CREATE UNIQUE INDEX idx_account_product_prices_user_product_unique
                ON account_product_prices (user_id, product_id);
            "#,
        )
        .await?;

        db.execute_unprepared("DROP TABLE product_variants;").await?;

        Ok(())
    }
}
