-- =============================================
-- Supabase 建表脚本
-- 在 Supabase SQL Editor（https://supabase.com/dashboard → SQL Editor）中执行
-- =============================================

create table if not exists task_code_sequences (
  batch_id              text not null,
  task_type             text not null,
  last_value            integer not null default 0,
  updated_at            timestamptz not null default now(),
  primary key (batch_id, task_type),
  check (task_type in ('ss', 'bz'))
);

comment on table task_code_sequences is '按批次和任务类型维护任务编码流水号';

create or replace function reserve_task_code(target_batch_id text, target_task_type text)
returns text
language plpgsql
as $$
declare
  next_value integer;
begin
  if coalesce(trim(target_batch_id), '') = '' then
    raise exception 'batch_id 不能为空';
  end if;

  if target_task_type not in ('ss', 'bz') then
    raise exception '不支持的 task_type: %', target_task_type;
  end if;

  insert into task_code_sequences (batch_id, task_type, last_value, updated_at)
  values (target_batch_id, target_task_type, 1, now())
  on conflict (batch_id, task_type)
  do update
    set last_value = task_code_sequences.last_value + 1,
        updated_at = now()
  returning last_value into next_value;

  return target_batch_id || '-' || target_task_type || '-' || lpad(next_value::text, 3, '0');
end;
$$;

create or replace function assign_standardization_task_code()
returns trigger
language plpgsql
as $$
begin
  if coalesce(trim(new.id), '') = '' then
    new.id := reserve_task_code(new.batch_id, 'bz');
  end if;

  return new;
end;
$$;

create or replace function assign_search_task_code()
returns trigger
language plpgsql
as $$
begin
  if coalesce(trim(new.id), '') = '' then
    new.id := reserve_task_code(new.batch_id, 'ss');
  end if;

  return new;
end;
$$;

-- ──────────────────────────────────────────
-- standardization_tasks：标准化任务头表
-- ──────────────────────────────────────────
create table if not exists standardization_tasks (
  id                  text primary key,
  batch_id            text not null default '',
  execution_mode      text not null default 'online',
  status              text not null default 'pending',
  total_rows          integer not null default 0,
  processed_rows      integer not null default 0,
  success_rows        integer not null default 0,
  failed_rows         integer not null default 0,
  current_item        text not null default '',
  current_phase       text not null default '',
  error_message       text not null default '',
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),
  started_at          timestamptz,
  finished_at         timestamptz
);

comment on table standardization_tasks is '标准化任务头表：记录采购清单发起的标准化任务运行状态与聚合进度';
comment on column standardization_tasks.status is '任务运行状态：pending/processing/completed/failed';
comment on column standardization_tasks.execution_mode is '任务执行模式：online/offline';
comment on column standardization_tasks.current_phase is '当前阶段，如分析中/写入成功结果/等待人工补充';

create index if not exists idx_standardization_tasks_batch_id on standardization_tasks(batch_id);
create index if not exists idx_standardization_tasks_status on standardization_tasks(status);
create index if not exists idx_standardization_tasks_updated_at on standardization_tasks(updated_at desc);

-- ──────────────────────────────────────────
-- standardization_task_items：标准化任务明细表
-- ──────────────────────────────────────────
create table if not exists standardization_task_items (
  id                    uuid primary key default gen_random_uuid(),
  task_id               text not null references standardization_tasks(id) on delete cascade,
  batch_id              text not null default '',
  source_material_id    text not null default '',
  status                text not null default 'pending',
  source_snapshot       jsonb not null default '{}',
  analysis_result       jsonb not null default '{}',
  keyword_analysis      jsonb not null default '{}',
  standardized_name     text not null default '',
  suggested_keyword     text not null default '',
  can_search_1688       boolean,
  missing_required_fields jsonb not null default '[]',
  failure_reason        text not null default '',
  human_fix_hint        text not null default '',
  patched_fields        jsonb not null default '{}',
  llm_model             text not null default '',
  retry_of_item_id      uuid references standardization_task_items(id),
  resolved_by_task_id   text references standardization_tasks(id),
  resolved_at           timestamptz,
  created_at            timestamptz not null default now(),
  updated_at            timestamptz not null default now()
);

comment on table standardization_task_items is '标准化任务明细表：记录每条物料的标准化成功/失败结果、失败原因与人工补充重试链路';
comment on column standardization_task_items.status is '行级处理状态：pending/processing/success/failed';
comment on column standardization_task_items.missing_required_fields is '关键缺失字段数组，如材质要求/容量/接口标准';
comment on column standardization_task_items.source_snapshot is '发起任务时的物料快照，失败后可直接人工补充并重试';

create index if not exists idx_standardization_task_items_task_id on standardization_task_items(task_id);
create index if not exists idx_standardization_task_items_batch_id on standardization_task_items(batch_id);
create index if not exists idx_standardization_task_items_status on standardization_task_items(status);
create index if not exists idx_standardization_task_items_material_id on standardization_task_items(source_material_id);
create index if not exists idx_standardization_task_items_updated_at on standardization_task_items(updated_at desc);

-- ──────────────────────────────────────────
-- search_tasks：1688 自动化搜索任务头表
-- ──────────────────────────────────────────
create table if not exists search_tasks (
  id                    text primary key,
  batch_id              text not null default '',
  account_id            uuid not null references procurement_accounts_1688(id) on delete restrict,
  pipeline_config       jsonb not null default '{}',
  contact_limit         integer not null default 0,
  headless              boolean not null default false,
  status                text not null default 'pending',
  total_rows            integer not null default 0,
  processed_rows        integer not null default 0,
  completed_rows        integer not null default 0,
  failed_rows           integer not null default 0,
  stopped_rows          integer not null default 0,
  current_material_id   uuid references procurement_materials(id) on delete set null,
  current_item          text not null default '',
  error_message         text not null default '',
  errors                jsonb not null default '[]',
  result                jsonb not null default '{}',
  created_at            timestamptz not null default now(),
  updated_at            timestamptz not null default now(),
  started_at            timestamptz,
  paused_at             timestamptz,
  finished_at           timestamptz
);

comment on table search_tasks is '1688 自动化搜索任务头表：记录一批搜索任务的执行状态、计数器以及暂停/停止结果';
comment on column search_tasks.status is '任务运行状态：pending/processing/waiting_for_manual/pausing/paused/stopping/stopped/completed/failed';
comment on column search_tasks.pipeline_config is '启动任务时冻结的搜索管道配置快照';
comment on column search_tasks.errors is '任务级错误列表，按时间顺序保留最近若干条';
comment on column search_tasks.result is '任务最终摘要，如 completed/stopped 的聚合返回值';

create index if not exists idx_search_tasks_batch_id on search_tasks(batch_id);
create index if not exists idx_search_tasks_status on search_tasks(status);
create index if not exists idx_search_tasks_updated_at on search_tasks(updated_at desc);

-- ──────────────────────────────────────────
-- search_task_items：1688 自动化搜索任务明细表
-- ──────────────────────────────────────────
create table if not exists search_task_items (
  id                    uuid primary key default gen_random_uuid(),
  task_id               text not null references search_tasks(id) on delete cascade,
  batch_id              text not null default '',
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  sort_order            integer not null default 0,
  material_name         text not null default '',
  status                text not null default 'pending',
  item_snapshot         jsonb not null default '{}',
  error_message         text not null default '',
  created_at            timestamptz not null default now(),
  updated_at            timestamptz not null default now(),
  started_at            timestamptz,
  finished_at           timestamptz,
  unique (task_id, material_id),
  unique (task_id, sort_order)
);

comment on table search_task_items is '1688 自动化搜索任务明细表：记录每条物料在任务中的顺序、状态和错误';
comment on column search_task_items.status is '行级处理状态：pending/processing/completed/failed/stopped';
comment on column search_task_items.item_snapshot is '发起搜索任务时冻结的物料快照，便于恢复与审计';

create index if not exists idx_search_task_items_task_id on search_task_items(task_id);
create index if not exists idx_search_task_items_batch_id on search_task_items(batch_id);
create index if not exists idx_search_task_items_status on search_task_items(status);
create index if not exists idx_search_task_items_material_id on search_task_items(material_id);
create index if not exists idx_search_task_items_updated_at on search_task_items(updated_at desc);

-- =============================================
-- 以下为 row-pipeline 各阶段产物表
-- =============================================

-- ──────────────────────────────────────────
-- keyword_process：阶段 1 关键词 LLM 分析全报告
-- ──────────────────────────────────────────
create table if not exists keyword_process (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  row_name              text not null default '',
  row_spec              text not null default '',
  row_brand             text not null default '',
  llm_model             text not null default '',
  ai_keyword_analysis   jsonb not null default '{}',
  full_report           jsonb not null default '{}',
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_keyword_process_material_id on keyword_process(material_id);
create index if not exists idx_keyword_process_batch_id on keyword_process(batch_id);

-- ──────────────────────────────────────────
-- search_rounds：阶段 2 + 5 文字搜索各轮次执行结果
-- ──────────────────────────────────────────
create table if not exists search_rounds (
  id                    uuid primary key default gen_random_uuid(),
  task_id               text,
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  search_round          smallint not null default 1,
  run_type              text not null default 'initial',   -- 'initial' | 'followup'
  primary_keyword       text not null default '',
  search_strategy       text not null default '',
  stage_order           smallint,
  stage_label           text not null default '',
  stage_kind            text not null default '',
  stage_main_keyword    text not null default '',
  brand_keyword         text not null default '',
  brand_keyword_hit_rate real,
  is_valid_search       boolean not null default false,
  visual_confidence     real,
  visual_reason         text not null default '',
  stop_reason           text not null default '',
  results_count         integer not null default 0,
  results               jsonb not null default '[]',
  screenshot_url        text not null default '',
  keyword_hit_rate      real,                              -- 采购商品级语义命中率（0~1），LLM 判定匹配候选数 / LLM 已判定候选数；无已判定候选时为 null
  source_candidate_count integer,
  qualified_result_count integer,
  rejected_no_phone_count integer,
  target_result_count integer,
  shortage_reason      text not null default '',
  require_phone_contact boolean not null default true,
  contact_enrichment_attempted boolean not null default false,
  contact_enrichment_skip_reason text not null default '',
  keyword_hit_rate_source text not null default 'local',
  keyword_hit_rate_reason text not null default '',
  keyword_hit_rate_sample_count integer,
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id, search_round, run_type)
);

create index if not exists idx_search_rounds_material_id on search_rounds(material_id);
create index if not exists idx_search_rounds_batch_id on search_rounds(batch_id);
create index if not exists idx_search_rounds_task_id on search_rounds(task_id);

-- ──────────────────────────────────────────
-- best_match_images：阶段 2 最佳匹配商品图
-- ──────────────────────────────────────────
create table if not exists best_match_images (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  image_url             text not null default '',
  offer_url             text not null default '',
  title                 text not null default '',
  merchant_name         text not null default '',
  wangwang_url          text not null default '',
  selected_from_round   smallint,
  selected_from_keyword text not null default '',
  method                text not null default '',
  reason                text not null default '',
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_best_match_images_material_id on best_match_images(material_id);
create index if not exists idx_best_match_images_batch_id on best_match_images(batch_id);

-- ──────────────────────────────────────────
-- extended_best_matches：阶段 2 扩展候选商品报告
-- ──────────────────────────────────────────
create table if not exists extended_best_matches (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  report                jsonb not null default '{}',
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_extended_best_matches_material_id on extended_best_matches(material_id);
create index if not exists idx_extended_best_matches_batch_id on extended_best_matches(batch_id);

-- ──────────────────────────────────────────
-- image_search_results：阶段 3 图搜结果
-- ──────────────────────────────────────────
create table if not exists image_search_results (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  is_valid_image_search boolean not null default true,
  screenshot_url        text not null default '',
  top_titles            jsonb not null default '[]',
  similar_products      jsonb not null default '[]',
  text_rule             jsonb not null default '{}',
  image_similarity      jsonb not null default '{}',
  full_report           jsonb not null default '{}',
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_image_search_results_material_id on image_search_results(material_id);
create index if not exists idx_image_search_results_batch_id on image_search_results(batch_id);

-- ──────────────────────────────────────────
-- keyword_selections：阶段 4 主关键词选择结果
-- ──────────────────────────────────────────
create table if not exists keyword_selections (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  main_keyword          text not null default '',
  judgment_reason       text not null default '',
  debug_data            jsonb not null default '{}',
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_keyword_selections_material_id on keyword_selections(material_id);
create index if not exists idx_keyword_selections_batch_id on keyword_selections(batch_id);

-- ──────────────────────────────────────────
-- final_results：最终商品列表
-- ──────────────────────────────────────────
create table if not exists final_results (
  id                    uuid primary key default gen_random_uuid(),
  task_id               text,
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  source                text not null default '',
  main_keyword          text not null default '',
  product_count         integer not null default 0,
  products              jsonb not null default '[]',
  finished_at           timestamptz,
  created_at            timestamptz not null default now(),
  unique (batch_id, material_id)
);

create index if not exists idx_final_results_material_id on final_results(material_id);
create index if not exists idx_final_results_batch_id on final_results(batch_id);
create index if not exists idx_final_results_task_id on final_results(task_id);

-- ──────────────────────────────────────────
-- final_result_products：最终商品列表展开（每行一条商品）
-- 父表字段（source/main_keyword/finished_at）在每行重复，方便直接按字段查询
-- ──────────────────────────────────────────
create table if not exists final_result_products (
  id                    uuid primary key default gen_random_uuid(),
  task_id               text,
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  source                text not null default '',
  main_keyword          text not null default '',
  stage_order           smallint,
  stage_label           text not null default '',
  stage_kind            text not null default '',
  stage_main_keyword    text not null default '',
  stage_keyword_hit_rate real,
  is_placeholder        boolean not null default false,
  placeholder_reason    text not null default '',
  finished_at           timestamptz,
  rank                  smallint not null default 1,      -- 来自 products[].index
  title                 text not null default '',
  offer_url             text not null default '',
  image_url             text not null default '',
  merchant_name         text not null default '',
  merchant_url          text not null default '',
  wangwang_url          text not null default '',
  offer_screenshot_url  text not null default '',
  contact_page_url      text not null default '',
  detected_contacts     jsonb not null default '[]',
  score                 real,
  is_match              boolean,
  reason                text not null default '',
  has_phone_contact     boolean not null default false,
  qualification_status  text not null default 'qualified',
  contact_collection_reason text not null default '',
  created_at            timestamptz not null default now()
);

create index if not exists idx_final_result_products_material_id on final_result_products(material_id);
create index if not exists idx_final_result_products_batch_id on final_result_products(batch_id);
create index if not exists idx_final_result_products_task_id on final_result_products(task_id);

drop trigger if exists trg_assign_standardization_task_code on standardization_tasks;
create trigger trg_assign_standardization_task_code
before insert on standardization_tasks
for each row execute function assign_standardization_task_code();

drop trigger if exists trg_assign_search_task_code on search_tasks;
create trigger trg_assign_search_task_code
before insert on search_tasks
for each row execute function assign_search_task_code();

-- ──────────────────────────────────────────
-- extended_best_match_records：扩展候选商品展开（每行一条记录）
-- 父表字段（generated_at）在每行重复，方便直接按字段查询
-- ──────────────────────────────────────────
create table if not exists extended_best_match_records (
  id                    uuid primary key default gen_random_uuid(),
  batch_id              text not null default '',          -- 所属批次 ID
  material_id           uuid not null references procurement_materials(id) on delete cascade,
  generated_at          timestamptz,
  rank                  smallint not null default 1,
  title                 text not null default '',
  offer_url             text not null default '',
  image_url             text not null default '',
  merchant_name         text not null default '',
  merchant_url          text not null default '',
  wangwang_url          text not null default '',
  offer_screenshot_url  text not null default '',
  score                 real,
  selected_from_round   smallint,
  selected_from_keyword text not null default '',
  detected_contacts     jsonb not null default '[]',
  contact_page_url      text not null default '',
  created_at            timestamptz not null default now()
);

create index if not exists idx_extended_best_match_records_material_id on extended_best_match_records(material_id);
create index if not exists idx_extended_best_match_records_batch_id on extended_best_match_records(batch_id);
