36 lines
1.5 KiB
SQL
36 lines
1.5 KiB
SQL
alter table products add column if not exists canonical_name text;
|
|
update products set canonical_name = trim(brand || ' ' || model) where canonical_name is null;
|
|
alter table products alter column canonical_name set not null;
|
|
|
|
create table if not exists product_source_specs (
|
|
id uuid primary key default gen_random_uuid(),
|
|
product_id text not null references products(id) on delete cascade,
|
|
source text not null check (source in ('yandex_market')),
|
|
source_url text not null,
|
|
source_product_id text,
|
|
raw_group text,
|
|
raw_label text not null,
|
|
raw_value jsonb not null,
|
|
normalized_key text,
|
|
normalized_value jsonb,
|
|
value_hash text not null,
|
|
fetched_at timestamptz not null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (product_id, source, source_url, raw_label)
|
|
);
|
|
|
|
create index if not exists product_source_specs_product_key_idx on product_source_specs(product_id, normalized_key);
|
|
|
|
create table if not exists moderation_conflicts (
|
|
id uuid primary key default gen_random_uuid(),
|
|
product_id text not null references products(id) on delete cascade,
|
|
conflict_type text not null check (conflict_type in ('spec_value')),
|
|
key text not null,
|
|
values_json jsonb not null,
|
|
state text not null default 'pending' check (state in ('pending', 'resolved', 'dismissed')),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (product_id, conflict_type, key)
|
|
);
|