//! Broaden product search to the whole purchasable surface.
//!
//! The `product_search` migration could only index columns living on `products`
//! itself (name, description), because a STORED generated column may not read
//! other tables. To also match by tag, variant label and SKU, `search_vector`
//! becomes a plain column maintained by triggers:
//!
//!   * `kompress_build_product_search(name, description, id)` builds the weighted
//!     vector for one product, pulling tags + variant labels + SKUs by id
//!     (name = A, tags + labels = B, description + SKU = C).
//!   * a BEFORE trigger on `products` keeps a product's own row in sync, and
//!   * AFTER triggers on `product_variants`, `product_product_tags` and tag
//!     renames refresh the affected product(s).
//!
//! The result is one `products.search_vector` that every search query can reuse,
//! always consistent with the catalog.

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();

        // Swap the generated column (name + description only) for a plain column
        // the triggers can own. Dropping it takes its GIN index with it.
        db.execute_unprepared(
            r#"
            ALTER TABLE products DROP COLUMN IF EXISTS search_vector;
            ALTER TABLE products ADD COLUMN search_vector tsvector;
            "#,
        )
        .await?;

        // Single source of truth for a product's search document.
        db.execute_unprepared(
            r#"
            CREATE OR REPLACE FUNCTION kompress_build_product_search(
                p_name text, p_description text, p_id integer
            ) RETURNS tsvector
            LANGUAGE sql STABLE AS $func$
                SELECT
                    setweight(to_tsvector('sk_unaccent', COALESCE(p_name, '')), 'A')
                  || setweight(to_tsvector('sk_unaccent', COALESCE((
                        SELECT string_agg(t.name, ' ')
                        FROM product_product_tags ppt
                        JOIN product_tags t ON t.id = ppt.product_tag_id
                        WHERE ppt.product_id = p_id
                     ), '')), 'B')
                  || setweight(to_tsvector('sk_unaccent', COALESCE((
                        SELECT string_agg(v.label, ' ')
                        FROM product_variants v
                        WHERE v.product_id = p_id
                     ), '')), 'B')
                  || setweight(to_tsvector('sk_unaccent', COALESCE(p_description, '')), 'C')
                  || setweight(to_tsvector('sk_unaccent', COALESCE((
                        SELECT string_agg(v.sku, ' ')
                        FROM product_variants v
                        WHERE v.product_id = p_id AND v.sku IS NOT NULL
                     ), '')), 'C');
            $func$;

            -- Refresh one product's stored vector (used by the satellite triggers).
            CREATE OR REPLACE FUNCTION kompress_refresh_product_search(p_id integer)
            RETURNS void LANGUAGE sql AS $func$
                UPDATE products
                   SET search_vector = kompress_build_product_search(name, description, id)
                 WHERE id = p_id;
            $func$;
            "#,
        )
        .await?;

        // BEFORE trigger on products: recompute on its own writes. When a refresh
        // only touches search_vector (name + description unchanged) it skips the
        // recompute and keeps the supplied value — which also breaks recursion.
        db.execute_unprepared(
            r#"
            CREATE OR REPLACE FUNCTION kompress_products_search_tg() RETURNS trigger
            LANGUAGE plpgsql AS $func$
            BEGIN
                IF TG_OP = 'UPDATE'
                   AND NEW.name IS NOT DISTINCT FROM OLD.name
                   AND NEW.description IS NOT DISTINCT FROM OLD.description
                   AND NEW.search_vector IS DISTINCT FROM OLD.search_vector THEN
                    RETURN NEW;
                END IF;
                NEW.search_vector :=
                    kompress_build_product_search(NEW.name, NEW.description, NEW.id);
                RETURN NEW;
            END;
            $func$;

            DROP TRIGGER IF EXISTS products_search_tg ON products;
            CREATE TRIGGER products_search_tg
                BEFORE INSERT OR UPDATE ON products
                FOR EACH ROW EXECUTE FUNCTION kompress_products_search_tg();
            "#,
        )
        .await?;

        // Variants: any change refreshes the owning product (both, on reparent).
        db.execute_unprepared(
            r#"
            CREATE OR REPLACE FUNCTION kompress_variants_search_tg() RETURNS trigger
            LANGUAGE plpgsql AS $func$
            BEGIN
                IF TG_OP = 'DELETE' THEN
                    PERFORM kompress_refresh_product_search(OLD.product_id);
                    RETURN OLD;
                END IF;
                PERFORM kompress_refresh_product_search(NEW.product_id);
                IF TG_OP = 'UPDATE' AND NEW.product_id IS DISTINCT FROM OLD.product_id THEN
                    PERFORM kompress_refresh_product_search(OLD.product_id);
                END IF;
                RETURN NEW;
            END;
            $func$;

            DROP TRIGGER IF EXISTS product_variants_search_tg ON product_variants;
            CREATE TRIGGER product_variants_search_tg
                AFTER INSERT OR UPDATE OR DELETE ON product_variants
                FOR EACH ROW EXECUTE FUNCTION kompress_variants_search_tg();
            "#,
        )
        .await?;

        // Tag links: attaching/detaching a tag refreshes the product.
        db.execute_unprepared(
            r#"
            CREATE OR REPLACE FUNCTION kompress_product_tags_link_search_tg() RETURNS trigger
            LANGUAGE plpgsql AS $func$
            BEGIN
                IF TG_OP = 'DELETE' THEN
                    PERFORM kompress_refresh_product_search(OLD.product_id);
                    RETURN OLD;
                END IF;
                PERFORM kompress_refresh_product_search(NEW.product_id);
                RETURN NEW;
            END;
            $func$;

            DROP TRIGGER IF EXISTS product_product_tags_search_tg ON product_product_tags;
            CREATE TRIGGER product_product_tags_search_tg
                AFTER INSERT OR UPDATE OR DELETE ON product_product_tags
                FOR EACH ROW EXECUTE FUNCTION kompress_product_tags_link_search_tg();
            "#,
        )
        .await?;

        // Renaming a tag refreshes every product carrying it.
        db.execute_unprepared(
            r#"
            CREATE OR REPLACE FUNCTION kompress_tag_rename_search_tg() RETURNS trigger
            LANGUAGE plpgsql AS $func$
            BEGIN
                UPDATE products p
                   SET search_vector =
                       kompress_build_product_search(p.name, p.description, p.id)
                 WHERE p.id IN (
                     SELECT ppt.product_id FROM product_product_tags ppt
                     WHERE ppt.product_tag_id = NEW.id
                 );
                RETURN NEW;
            END;
            $func$;

            DROP TRIGGER IF EXISTS product_tags_rename_search_tg ON product_tags;
            CREATE TRIGGER product_tags_rename_search_tg
                AFTER UPDATE OF name ON product_tags
                FOR EACH ROW EXECUTE FUNCTION kompress_tag_rename_search_tg();
            "#,
        )
        .await?;

        // Backfill existing rows, then (re)create the GIN index for `@@`.
        db.execute_unprepared(
            r#"
            UPDATE products
               SET search_vector = kompress_build_product_search(name, description, id);

            CREATE INDEX idx_products_search_vector
                ON products USING GIN (search_vector);
            "#,
        )
        .await?;

        Ok(())
    }

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

        db.execute_unprepared(
            r#"
            DROP TRIGGER IF EXISTS product_tags_rename_search_tg ON product_tags;
            DROP TRIGGER IF EXISTS product_product_tags_search_tg ON product_product_tags;
            DROP TRIGGER IF EXISTS product_variants_search_tg ON product_variants;
            DROP TRIGGER IF EXISTS products_search_tg ON products;
            DROP FUNCTION IF EXISTS kompress_tag_rename_search_tg();
            DROP FUNCTION IF EXISTS kompress_product_tags_link_search_tg();
            DROP FUNCTION IF EXISTS kompress_variants_search_tg();
            DROP FUNCTION IF EXISTS kompress_products_search_tg();
            DROP FUNCTION IF EXISTS kompress_refresh_product_search(integer);
            DROP FUNCTION IF EXISTS kompress_build_product_search(text, text, integer);
            ALTER TABLE products DROP COLUMN IF EXISTS search_vector;
            "#,
        )
        .await?;

        // Restore the name + description generated column from the prior migration.
        db.execute_unprepared(
            r#"
            ALTER TABLE products
                ADD COLUMN search_vector tsvector
                GENERATED ALWAYS AS (
                    setweight(to_tsvector('sk_unaccent', COALESCE(name, '')), 'A') ||
                    setweight(to_tsvector('sk_unaccent', COALESCE(description, '')), 'B')
                ) STORED;

            CREATE INDEX idx_products_search_vector
                ON products USING GIN (search_vector);
            "#,
        )
        .await?;

        Ok(())
    }
}
