alter table if exists source_files
  add column if not exists extraction_status text not null default 'uploaded',
  add column if not exists extraction_model text,
  add column if not exists extraction_summary text,
  add column if not exists extraction_error text,
  add column if not exists extracted_material_count integer,
  add column if not exists extracted_at timestamptz;

do $$
begin
  if to_regclass('public.source_files') is null then
    return;
  end if;

  if not exists (
    select 1
    from pg_constraint
    where conname = 'source_files_extraction_status_check'
      and conrelid = 'public.source_files'::regclass
  ) then
    alter table source_files
      add constraint source_files_extraction_status_check
      check (extraction_status in ('uploaded', 'local_parsed', 'llm_processing', 'llm_succeeded', 'llm_failed'));
  end if;

  if not exists (
    select 1
    from pg_constraint
    where conname = 'source_files_extracted_material_count_check'
      and conrelid = 'public.source_files'::regclass
  ) then
    alter table source_files
      add constraint source_files_extracted_material_count_check
      check (extracted_material_count is null or extracted_material_count >= 0);
  end if;

  create index if not exists idx_source_files_extraction_status
    on source_files(extraction_status);

  create index if not exists idx_source_files_extracted_at
    on source_files(extracted_at desc);
end $$;
