-- Procurement dictionary: generalized replacement for model_number_overrides.
create table if not exists procurement_dictionary_entries (
  id                  uuid primary key default gen_random_uuid(),
  term                text not null,
  normalized_term     text generated always as (upper(regexp_replace(btrim(term), '\s+', ' ', 'g'))) stored,
  term_type           text not null default 'parameter',
  product_context     text not null default '',
  normalized_context  text generated always as (lower(regexp_replace(btrim(product_context), '\s+', ' ', 'g'))) stored,
  interpretation      text not null default '',
  suggested_keyword   text not null default '',
  evidence            text not null default '',
  source_batch_id     text not null default '',
  source_material_id  uuid references procurement_materials(id) on delete set null,
  source_llm_model    text not null default '',
  occurrence_count    integer not null default 1,
  note                text,
  created_by          text,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),
  check (btrim(term) <> ''),
  check (term_type in ('unique_model', 'parameter', 'brand', 'standard', 'general')),
  check (occurrence_count > 0)
);

comment on table procurement_dictionary_entries is '采购字典：沉淀标准化阶段识别出的型号、参数、品牌、标准与采购语义解释';
comment on column procurement_dictionary_entries.term is '原始术语，如 GEB371、2R、DN100、ZC-BVR';
comment on column procurement_dictionary_entries.term_type is '术语类型：unique_model/parameter/brand/standard/general';
comment on column procurement_dictionary_entries.product_context is '术语适用的物料上下文，如 丁基防水胶带、防水胶带、断路器';
comment on column procurement_dictionary_entries.interpretation is '采购语义解释，会写入 AI 推理说明';
comment on column procurement_dictionary_entries.suggested_keyword is '可选推荐搜索词；unique_model 继续用于强制成功覆盖';
comment on column procurement_dictionary_entries.evidence is '解释依据，如规范、行业习惯、型号结构或原始字段证据';
comment on column procurement_dictionary_entries.occurrence_count is '该术语被标准化任务识别/写入的累计次数';

create unique index if not exists procurement_dictionary_entries_unique_term_context
  on procurement_dictionary_entries(normalized_term, term_type, normalized_context);

create index if not exists idx_procurement_dictionary_entries_term_type
  on procurement_dictionary_entries(term_type);

create index if not exists idx_procurement_dictionary_entries_updated_at
  on procurement_dictionary_entries(updated_at desc);

-- Backfill existing model-number dictionary rows into the generalized dictionary.
insert into procurement_dictionary_entries (
  term,
  term_type,
  product_context,
  interpretation,
  suggested_keyword,
  evidence,
  source_batch_id,
  source_llm_model,
  occurrence_count,
  note,
  created_by,
  created_at,
  updated_at
)
select
  model_number,
  'unique_model',
  '',
  coalesce(nullif(btrim(note), ''), suggested_keyword),
  suggested_keyword,
  'migrated from model_number_overrides',
  '',
  '',
  1,
  note,
  created_by,
  created_at,
  updated_at
from model_number_overrides
where not exists (
  select 1
  from procurement_dictionary_entries existing
  where existing.normalized_term = upper(regexp_replace(btrim(model_number_overrides.model_number), '\s+', ' ', 'g'))
    and existing.term_type = 'unique_model'
    and existing.normalized_context = ''
);
