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'))
);

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;
$$;

alter table if exists standardization_task_items
  drop constraint if exists standardization_task_items_task_id_fkey,
  drop constraint if exists standardization_task_items_resolved_by_task_id_fkey;

alter table if exists search_task_items
  drop constraint if exists search_task_items_task_id_fkey;

alter table if exists standardization_tasks
  alter column id drop default;

alter table if exists search_tasks
  alter column id drop default;

alter table if exists standardization_tasks
  alter column id type text using id::text;

alter table if exists standardization_task_items
  alter column task_id type text using task_id::text,
  alter column resolved_by_task_id type text using resolved_by_task_id::text;

alter table if exists search_tasks
  alter column id type text using id::text;

alter table if exists search_task_items
  alter column task_id type text using task_id::text;

alter table if exists standardization_tasks
  add column if not exists execution_mode text not null default 'online';

alter table if exists search_rounds
  add column if not exists task_id text;

alter table if exists final_results
  add column if not exists task_id text;

alter table if exists final_result_products
  add column if not exists task_id text;

create index if not exists idx_search_rounds_task_id on search_rounds(task_id);
create index if not exists idx_final_results_task_id on final_results(task_id);
create index if not exists idx_final_result_products_task_id on final_result_products(task_id);

alter table if exists standardization_task_items
  add constraint standardization_task_items_task_id_fkey
  foreign key (task_id) references standardization_tasks(id) on delete cascade;

alter table if exists standardization_task_items
  add constraint standardization_task_items_resolved_by_task_id_fkey
  foreign key (resolved_by_task_id) references standardization_tasks(id);

alter table if exists search_task_items
  add constraint search_task_items_task_id_fkey
  foreign key (task_id) references search_tasks(id) on delete cascade;

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();