//! Product descriptions are now authored as rich text (Quill) and stored as
//! HTML. The product search vector (see m20260622_000005) tokenizes the raw
//! description, so without this the markup itself (`p`, `strong`, `li`, `href`,
//! `class`, `ql`, …) would land in the index and pollute matches.
//!
//! Redefine `kompress_build_product_search` so the description is run through a
//! tag-stripping `regexp_replace` before `to_tsvector`, then backfill every
//! product's stored vector. Everything else about the function is unchanged.

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();
        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',
                        regexp_replace(COALESCE(p_description, ''), '<[^>]+>', ' ', 'g')
                     ), '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$;

            -- Backfill: recompute every product's vector with the new definition.
            UPDATE products
               SET search_vector = kompress_build_product_search(name, description, id);
            "#,
        )
        .await?;
        Ok(())
    }

    async fn down(&self, m: &SchemaManager) -> Result<(), DbErr> {
        // Restore the prior definition (raw, un-stripped description).
        let db = m.get_connection();
        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$;

            UPDATE products
               SET search_vector = kompress_build_product_search(name, description, id);
            "#,
        )
        .await?;
        Ok(())
    }
}
