alter table consulting_requests
  add column if not exists charge_required boolean not null default false,
  add column if not exists quoted_title text not null default '',
  add column if not exists quoted_amount integer not null default 0,
  add column if not exists quoted_note text not null default '',
  add column if not exists quoted_at timestamptz,
  add column if not exists confirmed_at timestamptz,
  add column if not exists order_id uuid unique references orders(id) on delete set null,
  add column if not exists paid_at timestamptz,
  add column if not exists final_summary text not null default '',
  add column if not exists final_file_id uuid references uploaded_files(id) on delete set null,
  add column if not exists internal_note text not null default '';

alter table consulting_requests
  drop constraint if exists consulting_requests_status_check;

alter table consulting_requests
  add constraint consulting_requests_status_check
  check (status in ('pending', 'assigned', 'communicating', 'planning', 'proposal_ready', 'pending_payment', 'in_service', 'completed', 'cancelled'));

create index if not exists idx_consulting_requests_order
  on consulting_requests(order_id);

alter table data_service_requests
  add column if not exists confirmed_at timestamptz,
  add column if not exists solution_summary text not null default '',
  add column if not exists internal_note text not null default '',
  add column if not exists deliverable_file_ids uuid[] not null default '{}';

update data_service_requests
set deliverable_file_ids = array[deliverable_file_id]
where deliverable_file_id is not null
  and cardinality(deliverable_file_ids) = 0;

alter table expert_bookings
  add column if not exists meeting_method text not null default '',
  add column if not exists meeting_platform text not null default '',
  add column if not exists meeting_no text not null default '',
  add column if not exists meeting_password text not null default '',
  add column if not exists meeting_note text not null default '',
  add column if not exists meeting_internal_note text not null default '',
  add column if not exists meeting_updated_at timestamptz;

alter table expert_booking_changes
  add column if not exists requested_by_user_at timestamptz,
  add column if not exists requested_by_expert_at timestamptz,
  add column if not exists user_confirmed_at timestamptz,
  add column if not exists expert_confirmed_at timestamptz,
  add column if not exists admin_note text not null default '',
  add column if not exists applied_at timestamptz;

alter table expert_booking_changes
  drop constraint if exists expert_booking_changes_status_check;

alter table expert_booking_changes
  add constraint expert_booking_changes_status_check
  check (status in ('pending', 'approved', 'requested', 'proposed', 'accepted', 'rejected', 'cancelled'));

insert into products (id, slug, type, title, summary, price_amount, purchase_flow, fulfillment_flow, status)
values (
  'consulting-service-custom',
  'consulting-service-custom',
  'consulting',
  '规划咨询服务',
  '根据用户咨询需求、顾问沟通和方案报价确认形成的规划咨询服务。',
  0,
  'application',
  'consulting',
  'published'
)
on conflict (id) do update set
  slug = excluded.slug,
  type = excluded.type,
  title = excluded.title,
  summary = excluded.summary,
  price_amount = excluded.price_amount,
  purchase_flow = excluded.purchase_flow,
  fulfillment_flow = excluded.fulfillment_flow,
  status = excluded.status,
  updated_at = now();
