-- =============================================
-- Prompt Store schema draft
-- 用途：独立 prompt Supabase 项目建表脚本
-- =============================================

create extension if not exists pgcrypto;

create or replace function public.prompt_store_set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create or replace function public.prompt_store_upsert_active_version(
  p_prompt_key text,
  p_module text,
  p_scene text,
  p_content_format text,
  p_description text,
  p_target_env text default 'prod',
  p_provider_scope text default '*',
  p_model_scope text default '*',
  p_version integer default 1,
  p_payload_json jsonb default '{}'::jsonb,
  p_variables_json jsonb default '[]'::jsonb,
  p_notes text default '',
  p_checksum text default '',
  p_created_by text default null
)
returns uuid
language plpgsql
as $$
declare
  v_template_id uuid;
  v_version_id uuid;
  v_target_env text := coalesce(nullif(trim(p_target_env), ''), 'prod');
  v_provider_scope text := coalesce(nullif(trim(p_provider_scope), ''), '*');
  v_model_scope text := coalesce(nullif(trim(p_model_scope), ''), '*');
begin
  if p_prompt_key is null or trim(p_prompt_key) = '' then
    raise exception 'prompt_key 不能为空';
  end if;

  if p_module is null or trim(p_module) = '' then
    raise exception 'module 不能为空';
  end if;

  if p_scene is null or trim(p_scene) = '' then
    raise exception 'scene 不能为空';
  end if;

  if p_content_format is null or trim(p_content_format) = '' then
    raise exception 'content_format 不能为空';
  end if;

  if p_version is null or p_version < 1 then
    raise exception 'version 必须是大于 0 的整数';
  end if;

  insert into public.prompt_templates (
    prompt_key,
    module,
    scene,
    content_format,
    description,
    owner,
    is_enabled
  )
  values (
    trim(p_prompt_key),
    trim(p_module),
    trim(p_scene),
    trim(p_content_format),
    coalesce(p_description, ''),
    p_created_by,
    true
  )
  on conflict (prompt_key) do update set
    module = excluded.module,
    scene = excluded.scene,
    content_format = excluded.content_format,
    description = excluded.description,
    owner = coalesce(public.prompt_templates.owner, excluded.owner),
    is_enabled = true,
    updated_at = now()
  returning id into v_template_id;

  update public.prompt_versions
  set
    status = 'archived',
    archived_at = now()
  where template_id = v_template_id
    and target_env = v_target_env
    and provider_scope = v_provider_scope
    and model_scope = v_model_scope
    and status = 'active'
    and version <> p_version;

  insert into public.prompt_versions (
    template_id,
    target_env,
    provider_scope,
    model_scope,
    version,
    status,
    payload_json,
    variables_json,
    notes,
    checksum,
    created_by,
    activated_at,
    archived_at
  )
  values (
    v_template_id,
    v_target_env,
    v_provider_scope,
    v_model_scope,
    p_version,
    'active',
    coalesce(p_payload_json, '{}'::jsonb),
    coalesce(p_variables_json, '[]'::jsonb),
    coalesce(p_notes, ''),
    coalesce(p_checksum, ''),
    p_created_by,
    now(),
    null
  )
  on conflict (template_id, target_env, provider_scope, model_scope, version)
  do update set
    status = 'active',
    payload_json = excluded.payload_json,
    variables_json = excluded.variables_json,
    notes = excluded.notes,
    checksum = excluded.checksum,
    created_by = coalesce(excluded.created_by, public.prompt_versions.created_by),
    activated_at = coalesce(public.prompt_versions.activated_at, now()),
    archived_at = null
  returning id into v_version_id;

  return v_version_id;
end;
$$;

comment on function public.prompt_store_upsert_active_version(text, text, text, text, text, text, text, text, integer, jsonb, jsonb, text, text, text)
  is '幂等写入并激活一个 prompt 版本；同 scope 下旧 active 版本会自动归档';

create table if not exists public.prompt_templates (
  id              uuid primary key default gen_random_uuid(),
  prompt_key      text not null unique,
  module          text not null,
  scene           text not null,
  content_format  text not null check (content_format in ('text', 'text_template', 'chat_bundle', 'multimodal_bundle', 'round_map')),
  description     text not null default '',
  owner           text,
  is_enabled      boolean not null default true,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

comment on table public.prompt_templates is '稳定的 prompt 模板元信息，代码只通过 prompt_key 查询';
comment on column public.prompt_templates.prompt_key is '全局唯一 prompt 键，例如 automation.search.page_review.bundle';
comment on column public.prompt_templates.content_format is '决定 payload_json 的结构解析方式';

create table if not exists public.prompt_versions (
  id              uuid primary key default gen_random_uuid(),
  template_id     uuid not null references public.prompt_templates(id) on delete cascade,
  target_env      text not null default 'prod',
  provider_scope  text not null default '*',
  model_scope     text not null default '*',
  version         integer not null,
  status          text not null default 'draft' check (status in ('draft', 'active', 'archived')),
  payload_json    jsonb not null default '{}'::jsonb,
  variables_json  jsonb not null default '[]'::jsonb,
  notes           text not null default '',
  checksum        text not null default '',
  created_by      text,
  created_at      timestamptz not null default now(),
  activated_at    timestamptz,
  archived_at     timestamptz,
  constraint prompt_versions_payload_object check (jsonb_typeof(payload_json) = 'object'),
  constraint prompt_versions_variables_array check (jsonb_typeof(variables_json) = 'array'),
  constraint prompt_versions_unique_version unique (template_id, target_env, provider_scope, model_scope, version)
);

comment on table public.prompt_versions is 'prompt 实际内容版本表，支持环境、模型和供应商范围匹配';
comment on column public.prompt_versions.target_env is '运行环境，例如 prod、staging、dev';
comment on column public.prompt_versions.provider_scope is '模型供应商匹配范围，默认 *';
comment on column public.prompt_versions.model_scope is '模型匹配范围，默认 *';
comment on column public.prompt_versions.payload_json is '真正的 prompt 内容，统一存 object';
comment on column public.prompt_versions.variables_json is '变量定义数组，例如 [{"name":"keyword","required":true}]';

alter table public.prompt_templates enable row level security;
alter table public.prompt_versions enable row level security;

create index if not exists idx_prompt_templates_module on public.prompt_templates(module);
create index if not exists idx_prompt_templates_enabled on public.prompt_templates(is_enabled);

create index if not exists idx_prompt_versions_template on public.prompt_versions(template_id);
create index if not exists idx_prompt_versions_status on public.prompt_versions(status);
create index if not exists idx_prompt_versions_env on public.prompt_versions(target_env);
create index if not exists idx_prompt_versions_scope on public.prompt_versions(provider_scope, model_scope);
create index if not exists idx_prompt_versions_created_at on public.prompt_versions(created_at desc);

create unique index if not exists idx_prompt_versions_active_unique
  on public.prompt_versions(template_id, target_env, provider_scope, model_scope)
  where status = 'active';

drop trigger if exists trg_prompt_templates_updated_at on public.prompt_templates;
create trigger trg_prompt_templates_updated_at
before update on public.prompt_templates
for each row
execute function public.prompt_store_set_updated_at();

create or replace function public.prompt_store_resolve_active_version(
  p_prompt_key text,
  p_target_env text default 'prod',
  p_provider text default '*',
  p_model text default '*'
)
returns table (
  template_id uuid,
  version_id uuid,
  prompt_key text,
  module text,
  scene text,
  content_format text,
  description text,
  target_env text,
  provider_scope text,
  model_scope text,
  version integer,
  payload_json jsonb,
  variables_json jsonb,
  checksum text,
  activated_at timestamptz
)
language sql
stable
as $$
with requested as (
  select
    coalesce(nullif(trim(p_target_env), ''), 'prod') as target_env,
    coalesce(nullif(trim(p_provider), ''), '*') as provider_name,
    coalesce(nullif(trim(p_model), ''), '*') as model_name
), candidates as (
  select
    t.id as template_id,
    v.id as version_id,
    t.prompt_key,
    t.module,
    t.scene,
    t.content_format,
    t.description,
    v.target_env,
    v.provider_scope,
    v.model_scope,
    v.version,
    v.payload_json,
    v.variables_json,
    v.checksum,
    v.activated_at,
    case
      when v.provider_scope = requested.provider_name and v.model_scope = requested.model_name then 1
      when v.provider_scope = requested.provider_name and v.model_scope = '*' then 2
      when v.provider_scope = '*' and v.model_scope = requested.model_name then 3
      when v.provider_scope = '*' and v.model_scope = '*' then 4
      else 100
    end as match_rank
  from requested
  join public.prompt_templates t
    on t.prompt_key = trim(p_prompt_key)
   and t.is_enabled = true
  join public.prompt_versions v
    on v.template_id = t.id
   and v.status = 'active'
   and v.target_env = requested.target_env
  where v.provider_scope in (requested.provider_name, '*')
    and v.model_scope in (requested.model_name, '*')
)
select
  template_id,
  version_id,
  prompt_key,
  module,
  scene,
  content_format,
  description,
  target_env,
  provider_scope,
  model_scope,
  version,
  payload_json,
  variables_json,
  checksum,
  activated_at
from candidates
where match_rank < 100
order by match_rank asc, version desc
limit 1;
$$;

comment on function public.prompt_store_resolve_active_version(text, text, text, text)
  is '按 prompt_key、target_env、provider、model 解析当前生效版本';

create or replace function public.prompt_store_get_payload(
  p_prompt_key text,
  p_target_env text default 'prod',
  p_provider text default '*',
  p_model text default '*'
)
returns jsonb
language sql
stable
as $$
select resolved.payload_json
from public.prompt_store_resolve_active_version(
  p_prompt_key,
  p_target_env,
  p_provider,
  p_model
) as resolved;
$$;

comment on function public.prompt_store_get_payload(text, text, text, text)
  is '只返回匹配到的 prompt payload_json，供服务端按 key 直接读取';

create or replace view public.prompt_active_versions as
select
  t.id as template_id,
  t.prompt_key,
  t.module,
  t.scene,
  t.content_format,
  v.id as version_id,
  v.target_env,
  v.provider_scope,
  v.model_scope,
  v.version,
  v.payload_json,
  v.variables_json,
  v.checksum,
  v.created_at,
  v.activated_at
from public.prompt_templates t
join public.prompt_versions v on v.template_id = t.id
where t.is_enabled = true
  and v.status = 'active';

comment on view public.prompt_active_versions is '供服务端读取当前 active prompt 版本的只读视图';

revoke all on table public.prompt_templates from anon, authenticated;
revoke all on table public.prompt_versions from anon, authenticated;
revoke all on public.prompt_active_versions from anon, authenticated;

grant select, insert, update, delete on table public.prompt_templates to service_role;
grant select, insert, update, delete on table public.prompt_versions to service_role;
grant select on public.prompt_active_versions to service_role;

revoke all on function public.prompt_store_upsert_active_version(text, text, text, text, text, text, text, text, integer, jsonb, jsonb, text, text, text) from public;
revoke all on function public.prompt_store_resolve_active_version(text, text, text, text) from public;
revoke all on function public.prompt_store_get_payload(text, text, text, text) from public;

grant execute on function public.prompt_store_upsert_active_version(text, text, text, text, text, text, text, text, integer, jsonb, jsonb, text, text, text) to service_role;
grant execute on function public.prompt_store_resolve_active_version(text, text, text, text) to service_role;
grant execute on function public.prompt_store_get_payload(text, text, text, text) to service_role;

insert into public.prompt_templates (prompt_key, module, scene, content_format, description)
values
  ('automation.standardization.combined.system', 'automation.standardization', 'combined_standardization', 'text', '标准化与 1688 可搜索性联合判断 system prompt'),
  ('automation.standardization.keyword_semantic.system', 'automation.standardization', 'keyword_semantic', 'text', '关键词语义分析 system prompt'),
  ('automation.document_extraction.materials.system', 'automation.document_extraction', 'extract_materials', 'text', '文档提取采购物料 system prompt'),
  ('automation.search.keyword_fusion.bundle', 'automation.search', 'keyword_fusion', 'multimodal_bundle', '图片、标题与 ai_keyword_analysis 融合判断'),
  ('automation.search.semantic_title_validation.bundle', 'automation.search', 'semantic_title_validation', 'chat_bundle', '标题语义校验，识别干扰项'),
  ('automation.search.keyword_regeneration.bundle', 'automation.search', 'keyword_regeneration', 'chat_bundle', '搜索失败后重写关键词'),
  ('automation.search.hit_rate_fallback.bundle', 'automation.search', 'hit_rate_fallback', 'chat_bundle', '搜索命中率语义复核'),
  ('automation.search.page_review.bundle', 'automation.search', 'page_review', 'multimodal_bundle', '搜索结果页视觉有效性审查'),
  ('automation.search.candidate_filter.bundle', 'automation.search', 'candidate_filter', 'chat_bundle', '候选商品与 ai_keyword_analysis 对齐过滤'),
  ('automation.search.best_image_selection.bundle', 'automation.search', 'best_image_selection', 'multimodal_bundle', '候选图中挑选最佳图搜参考图'),
  ('chatbot.intent.round_1.system', 'chatbot.intent', 'round_1', 'text_template', '第 1 轮现货判断 system prompt'),
  ('chatbot.intent.round_2.system', 'chatbot.intent', 'round_2', 'text_template', '第 2 轮规格与起订量判断 system prompt'),
  ('chatbot.intent.round_3.system', 'chatbot.intent', 'round_3', 'text_template', '第 3 轮价格与交期提取 system prompt'),
  ('chatbot.intent.round_4.system', 'chatbot.intent', 'round_4', 'text_template', '第 4 轮国标与专票判断 system prompt'),
  ('chatbot.intent.generic_round.system', 'chatbot.intent', 'generic_round', 'text_template', '自定义漏斗轮次通用 system prompt')
on conflict (prompt_key) do update set
  module = excluded.module,
  scene = excluded.scene,
  content_format = excluded.content_format,
  description = excluded.description,
  updated_at = now();
