create table if not exists business_number_sequences (
  prefix text not null,
  biz_date date not null,
  current_value integer not null default 0,
  updated_at timestamptz not null default now(),
  primary key (prefix, biz_date)
);

create or replace function next_business_no(
  p_prefix text,
  p_biz_date date default ((now() at time zone 'Asia/Shanghai')::date)
)
returns text
language plpgsql
volatile
as $$
declare
  clean_prefix text := upper(regexp_replace(coalesce(p_prefix, ''), '[^A-Z0-9]', '', 'g'));
  next_value integer;
begin
  if clean_prefix = '' then
    raise exception 'business number prefix is required';
  end if;

  insert into business_number_sequences (prefix, biz_date, current_value, updated_at)
  values (clean_prefix, p_biz_date, 1, now())
  on conflict (prefix, biz_date) do update
    set current_value = business_number_sequences.current_value + 1,
        updated_at = now()
  returning current_value into next_value;

  return clean_prefix || to_char(p_biz_date, 'YYYYMMDD') || lpad(next_value::text, 6, '0');
end;
$$;

alter table orders
  add column if not exists settlement_no text,
  add column if not exists legacy_order_no text;

do $$
declare
  rec record;
begin
  for rec in
    select id, created_at
    from orders
    where order_no !~ '^ORD[0-9]{14}$'
    order by created_at, id
  loop
    update orders
    set legacy_order_no = coalesce(legacy_order_no, order_no),
        order_no = next_business_no('ORD', (rec.created_at at time zone 'Asia/Shanghai')::date)
    where id = rec.id;
  end loop;

  for rec in
    select id, created_at
    from orders
    where settlement_no is null or settlement_no = ''
    order by created_at, id
  loop
    update orders
    set settlement_no = next_business_no('CKO', (rec.created_at at time zone 'Asia/Shanghai')::date)
    where id = rec.id;
  end loop;
end;
$$;

alter table orders
  alter column settlement_no set not null;

create unique index if not exists idx_orders_settlement_no
  on orders(settlement_no);

do $$
declare
  rec record;
begin
  if to_regclass('payment_orders') is not null then
    alter table payment_orders
      add column if not exists legacy_payment_no text;

    for rec in
      select id, created_at
      from payment_orders
      where payment_no !~ '^PAY[0-9]{14}$'
      order by created_at, id
    loop
      update payment_orders
      set legacy_payment_no = coalesce(legacy_payment_no, payment_no),
          payment_no = next_business_no('PAY', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table fulfillment_tasks
  add column if not exists task_no text;

do $$
declare
  rec record;
begin
  for rec in
    select id, created_at
    from fulfillment_tasks
    where task_no is null or task_no = ''
    order by created_at, id
  loop
    update fulfillment_tasks
    set task_no = next_business_no('TSK', (rec.created_at at time zone 'Asia/Shanghai')::date)
    where id = rec.id;
  end loop;
end;
$$;

alter table fulfillment_tasks
  alter column task_no set not null;

create unique index if not exists idx_fulfillment_tasks_task_no
  on fulfillment_tasks(task_no);

alter table if exists consulting_requests
  add column if not exists legacy_request_no text;

do $$
declare
  rec record;
begin
  if to_regclass('consulting_requests') is not null then
    for rec in
      select id, request_no, created_at
      from consulting_requests
      where request_no !~ '^CON[0-9]{14}$'
      order by created_at, id
    loop
      update consulting_requests
      set legacy_request_no = coalesce(legacy_request_no, rec.request_no),
          request_no = next_business_no('CON', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists data_service_requests
  add column if not exists legacy_request_no text;

do $$
declare
  rec record;
begin
  if to_regclass('data_service_requests') is not null then
    for rec in
      select id, request_no, created_at
      from data_service_requests
      where request_no !~ '^DSR[0-9]{14}$'
      order by created_at, id
    loop
      update data_service_requests
      set legacy_request_no = coalesce(legacy_request_no, rec.request_no),
          request_no = next_business_no('DSR', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists cooperation_requests
  add column if not exists legacy_request_no text;

do $$
declare
  rec record;
begin
  if to_regclass('cooperation_requests') is not null then
    for rec in
      select id, request_no, created_at
      from cooperation_requests
      where request_no !~ '^COO[0-9]{14}$'
      order by created_at, id
    loop
      update cooperation_requests
      set legacy_request_no = coalesce(legacy_request_no, rec.request_no),
          request_no = next_business_no('COO', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists expert_bookings
  add column if not exists legacy_booking_no text;

do $$
declare
  rec record;
begin
  if to_regclass('expert_bookings') is not null then
    for rec in
      select id, booking_no, created_at
      from expert_bookings
      where booking_no !~ '^EXB[0-9]{14}$'
      order by created_at, id
    loop
      update expert_bookings
      set legacy_booking_no = coalesce(legacy_booking_no, rec.booking_no),
          booking_no = next_business_no('EXB', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists course_registrations
  add column if not exists registration_no text;

do $$
declare
  rec record;
begin
  if to_regclass('course_registrations') is not null then
    for rec in
      select id, created_at
      from course_registrations
      where registration_no is null or registration_no = ''
      order by created_at, id
    loop
      update course_registrations
      set registration_no = next_business_no('REG', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists course_registrations
  alter column registration_no set not null;

create unique index if not exists idx_course_registrations_registration_no
  on course_registrations(registration_no);

alter table if exists event_registrations
  add column if not exists registration_no text;

do $$
declare
  rec record;
begin
  if to_regclass('event_registrations') is not null then
    for rec in
      select id, created_at
      from event_registrations
      where registration_no is null or registration_no = ''
      order by created_at, id
    loop
      update event_registrations
      set registration_no = next_business_no('REG', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists event_registrations
  alter column registration_no set not null;

create unique index if not exists idx_event_registrations_registration_no
  on event_registrations(registration_no);

alter table if exists settlement_records
  add column if not exists settlement_no text;

do $$
declare
  rec record;
begin
  if to_regclass('settlement_records') is not null then
    for rec in
      select id, created_at
      from settlement_records
      where settlement_no is null or settlement_no = ''
      order by created_at, id
    loop
      update settlement_records
      set settlement_no = next_business_no('STL', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists settlement_records
  alter column settlement_no set not null;

create unique index if not exists idx_settlement_records_settlement_no
  on settlement_records(settlement_no);

alter table if exists expert_withdrawals
  add column if not exists withdrawal_no text;

do $$
declare
  rec record;
begin
  if to_regclass('expert_withdrawals') is not null then
    for rec in
      select id, created_at
      from expert_withdrawals
      where withdrawal_no is null or withdrawal_no = ''
      order by created_at, id
    loop
      update expert_withdrawals
      set withdrawal_no = next_business_no('WDR', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists expert_withdrawals
  alter column withdrawal_no set not null;

create unique index if not exists idx_expert_withdrawals_withdrawal_no
  on expert_withdrawals(withdrawal_no);

alter table if exists expert_applications
  add column if not exists application_no text;

do $$
declare
  rec record;
begin
  if to_regclass('expert_applications') is not null then
    for rec in
      select id, created_at
      from expert_applications
      where application_no is null or application_no = ''
      order by created_at, id
    loop
      update expert_applications
      set application_no = next_business_no('EXA', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists expert_applications
  alter column application_no set not null;

create unique index if not exists idx_expert_applications_application_no
  on expert_applications(application_no);

alter table if exists invoice_applications
  add column if not exists invoice_no text;

do $$
declare
  rec record;
begin
  if to_regclass('invoice_applications') is not null then
    for rec in
      select id, created_at
      from invoice_applications
      where invoice_no is null or invoice_no = ''
      order by created_at, id
    loop
      update invoice_applications
      set invoice_no = next_business_no('INV', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists invoice_applications
  alter column invoice_no set not null;

do $$
begin
  if to_regclass('invoice_applications') is not null then
    create unique index if not exists idx_invoice_applications_invoice_no
      on invoice_applications(invoice_no);
  end if;
end;
$$;

alter table if exists refund_requests
  add column if not exists refund_no text;

do $$
declare
  rec record;
begin
  if to_regclass('refund_requests') is not null then
    for rec in
      select id, created_at
      from refund_requests
      where refund_no is null or refund_no = ''
      order by created_at, id
    loop
      update refund_requests
      set refund_no = next_business_no('RFN', (rec.created_at at time zone 'Asia/Shanghai')::date)
      where id = rec.id;
    end loop;
  end if;
end;
$$;

alter table if exists refund_requests
  alter column refund_no set not null;

do $$
begin
  if to_regclass('refund_requests') is not null then
    create unique index if not exists idx_refund_requests_refund_no
      on refund_requests(refund_no);
  end if;
end;
$$;

insert into business_number_sequences (prefix, biz_date, current_value, updated_at)
select prefix, biz_date, max(seq_value), now()
from (
  select 'ORD' as prefix, to_date(substr(order_no, 4, 8), 'YYYYMMDD') as biz_date, substr(order_no, 12, 6)::integer as seq_value
  from orders
  where order_no ~ '^ORD[0-9]{14}$'
  union all
  select 'CKO', to_date(substr(settlement_no, 4, 8), 'YYYYMMDD'), substr(settlement_no, 12, 6)::integer
  from orders
  where settlement_no ~ '^CKO[0-9]{14}$'
  union all
  select 'PAY', to_date(substr(payment_no, 4, 8), 'YYYYMMDD'), substr(payment_no, 12, 6)::integer
  from payment_orders
  where payment_no ~ '^PAY[0-9]{14}$'
  union all
  select 'TSK', to_date(substr(task_no, 4, 8), 'YYYYMMDD'), substr(task_no, 12, 6)::integer
  from fulfillment_tasks
  where task_no ~ '^TSK[0-9]{14}$'
  union all
  select 'CON', to_date(substr(request_no, 4, 8), 'YYYYMMDD'), substr(request_no, 12, 6)::integer
  from consulting_requests
  where request_no ~ '^CON[0-9]{14}$'
  union all
  select 'DSR', to_date(substr(request_no, 4, 8), 'YYYYMMDD'), substr(request_no, 12, 6)::integer
  from data_service_requests
  where request_no ~ '^DSR[0-9]{14}$'
  union all
  select 'COO', to_date(substr(request_no, 4, 8), 'YYYYMMDD'), substr(request_no, 12, 6)::integer
  from cooperation_requests
  where request_no ~ '^COO[0-9]{14}$'
  union all
  select 'EXB', to_date(substr(booking_no, 4, 8), 'YYYYMMDD'), substr(booking_no, 12, 6)::integer
  from expert_bookings
  where booking_no ~ '^EXB[0-9]{14}$'
  union all
  select 'REG', to_date(substr(registration_no, 4, 8), 'YYYYMMDD'), substr(registration_no, 12, 6)::integer
  from course_registrations
  where registration_no ~ '^REG[0-9]{14}$'
  union all
  select 'REG', to_date(substr(registration_no, 4, 8), 'YYYYMMDD'), substr(registration_no, 12, 6)::integer
  from event_registrations
  where registration_no ~ '^REG[0-9]{14}$'
  union all
  select 'STL', to_date(substr(settlement_no, 4, 8), 'YYYYMMDD'), substr(settlement_no, 12, 6)::integer
  from settlement_records
  where settlement_no ~ '^STL[0-9]{14}$'
  union all
  select 'WDR', to_date(substr(withdrawal_no, 4, 8), 'YYYYMMDD'), substr(withdrawal_no, 12, 6)::integer
  from expert_withdrawals
  where withdrawal_no ~ '^WDR[0-9]{14}$'
  union all
  select 'EXA', to_date(substr(application_no, 4, 8), 'YYYYMMDD'), substr(application_no, 12, 6)::integer
  from expert_applications
  where application_no ~ '^EXA[0-9]{14}$'
) values_by_prefix
group by prefix, biz_date
on conflict (prefix, biz_date) do update
  set current_value = greatest(business_number_sequences.current_value, excluded.current_value),
      updated_at = now();
