alter table if exists expert_bookings
  add column if not exists hold_expires_at timestamptz,
  add column if not exists cancelled_at timestamptz,
  add column if not exists cancelled_by text not null default '',
  add column if not exists completion_submitted_at timestamptz,
  add column if not exists user_confirmed_at timestamptz,
  add column if not exists disputed_at timestamptz,
  add column if not exists dispute_reason text not null default '';

alter table if exists expert_bookings
  drop constraint if exists expert_bookings_status_check;

alter table if exists expert_bookings
  add constraint expert_bookings_status_check
  check (status in (
    'pending_payment',
    'pending_expert',
    'confirmed',
    'rejected',
    'cancelled',
    'pending_user_confirm',
    'disputed',
    'completed'
  ));

update expert_bookings
set hold_expires_at = created_at + interval '15 minutes'
where status = 'pending_payment'
  and hold_expires_at is null;

alter table if exists settlement_records
  add column if not exists available_amount integer not null default 0,
  add column if not exists locked_amount integer not null default 0,
  add column if not exists withdrawn_amount integer not null default 0,
  add column if not exists available_at timestamptz;

update settlement_records sr
set status = 'available',
    available_amount = greatest(sr.expert_share_amount - sr.locked_amount - sr.withdrawn_amount, 0),
    available_at = coalesce(sr.available_at, now()),
    updated_at = now()
from expert_bookings b
where sr.order_id = b.order_id
  and sr.expert_id = b.expert_id
  and b.status = 'completed'
  and sr.status in ('pending_settlement', 'available')
  and sr.available_amount = 0;

create table if not exists expert_withdrawal_items (
  id uuid primary key default gen_random_uuid(),
  withdrawal_id uuid not null references expert_withdrawals(id) on delete cascade,
  settlement_record_id uuid not null references settlement_records(id) on delete restrict,
  amount integer not null check (amount > 0),
  created_at timestamptz not null default now(),
  unique (withdrawal_id, settlement_record_id)
);

create index if not exists idx_expert_withdrawal_items_withdrawal
  on expert_withdrawal_items(withdrawal_id);

create index if not exists idx_expert_withdrawal_items_settlement
  on expert_withdrawal_items(settlement_record_id);

alter table if exists expert_withdrawals
  add column if not exists paid_at timestamptz,
  add column if not exists paid_bank text not null default '',
  add column if not exists payment_serial_no text not null default '',
  add column if not exists payment_proof_file_id uuid references uploaded_files(id) on delete set null,
  add column if not exists payout_note text not null default '';

update staff_roles
set permissions = array(
  select distinct unnest(permissions || array[
    'expert_wallet:read',
    'expert_dispute:update'
  ])
)
where code in ('super_admin', 'finance_admin', 'operations_admin');
