create table if not exists invoice_applications (
  id uuid primary key default gen_random_uuid(),
  invoice_no text unique,
  order_id uuid not null references orders(id) on delete cascade,
  title text not null,
  tax_number text,
  email text not null,
  amount integer not null default 0,
  status text not null default 'pending',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table if exists invoice_applications
  add column if not exists provider text not null default 'manual',
  add column if not exists provider_invoice_id text not null default '',
  add column if not exists provider_payload jsonb not null default '{}'::jsonb,
  add column if not exists pdf_file_id uuid references uploaded_files(id) on delete set null,
  add column if not exists failure_reason text not null default '',
  add column if not exists issued_at timestamptz;

create index if not exists idx_invoice_applications_provider_status
  on invoice_applications(provider, status, created_at desc);

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

create index if not exists idx_expert_withdrawals_paid_at
  on expert_withdrawals(paid_at desc);

alter table if exists uploaded_files
  add column if not exists bucket text not null default '',
  add column if not exists mime_hash text not null default '',
  add column if not exists checksum text not null default '',
  add column if not exists visibility text not null default 'private',
  add column if not exists owner_type text not null default '',
  add column if not exists owner_id text not null default '';

create index if not exists idx_uploaded_files_owner
  on uploaded_files(owner_type, owner_id, created_at desc);

create table if not exists sms_send_logs (
  id uuid primary key default gen_random_uuid(),
  phone text not null,
  scene text not null default 'auth',
  provider text not null default 'aliyun',
  request_id text not null default '',
  status text not null default 'queued' check (status in ('queued', 'sent', 'failed', 'limited', 'skipped')),
  failure_reason text not null default '',
  client_ip text not null default '',
  created_at timestamptz not null default now()
);

create index if not exists idx_sms_send_logs_phone_created
  on sms_send_logs(phone, created_at desc);

create index if not exists idx_sms_send_logs_status_created
  on sms_send_logs(status, created_at desc);

create table if not exists operational_events (
  id uuid primary key default gen_random_uuid(),
  severity text not null default 'info' check (severity in ('info', 'warning', 'error', 'critical')),
  event_type text not null,
  resource_type text not null default '',
  resource_id text not null default '',
  message text not null default '',
  payload jsonb not null default '{}'::jsonb,
  resolved_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists idx_operational_events_open
  on operational_events(severity, event_type, created_at desc)
  where resolved_at is null;
