25 lines
897 B
SQL
25 lines
897 B
SQL
-- migrate:up
|
|
|
|
-- Nueva tabla para mapeos alias -> múltiples productos con score
|
|
create table if not exists alias_product_mappings (
|
|
tenant_id uuid not null references tenants(id) on delete cascade,
|
|
alias text not null,
|
|
woo_product_id integer not null,
|
|
score numeric(4,2) not null default 1.0,
|
|
created_at timestamptz not null default now(),
|
|
primary key (tenant_id, alias, woo_product_id)
|
|
);
|
|
|
|
create index if not exists alias_product_mappings_alias_idx
|
|
on alias_product_mappings(tenant_id, alias);
|
|
|
|
-- Migrar datos existentes: copiar woo_product_id de product_aliases
|
|
insert into alias_product_mappings (tenant_id, alias, woo_product_id, score)
|
|
select tenant_id, alias, woo_product_id, coalesce(boost, 1.0)
|
|
from product_aliases
|
|
where woo_product_id is not null
|
|
on conflict (tenant_id, alias, woo_product_id) do nothing;
|
|
|
|
-- migrate:down
|
|
drop table if exists alias_product_mappings;
|