30 lines
1.0 KiB
SQL
30 lines
1.0 KiB
SQL
create table if not exists manufacturers (
|
|
id uuid primary key default gen_random_uuid(),
|
|
slug text not null unique,
|
|
name text not null,
|
|
trust_status text not null default 'unknown' check (trust_status in ('trusted', 'unknown', 'untrusted')),
|
|
notes text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table products add column if not exists manufacturer_id uuid references manufacturers(id) on delete set null;
|
|
|
|
insert into manufacturers (slug, name)
|
|
select
|
|
lower(regexp_replace(trim(brand), '[^[:alnum:]]+', '-', 'g')) as slug,
|
|
brand as name
|
|
from products
|
|
where brand is not null
|
|
on conflict (slug) do update
|
|
set name = excluded.name,
|
|
updated_at = now();
|
|
|
|
update products
|
|
set manufacturer_id = manufacturers.id
|
|
from manufacturers
|
|
where products.manufacturer_id is null
|
|
and manufacturers.slug = lower(regexp_replace(trim(products.brand), '[^[:alnum:]]+', '-', 'g'));
|
|
|
|
create index if not exists products_manufacturer_id_idx on products(manufacturer_id);
|