-- Reconcile durable generation ORM fields with the production PostgreSQL schema.
-- Apply after 0003_generation_domain_postgres.sql and the project foundation migration.
begin;

alter table generation_requests
  add column if not exists project_id text,
  add column if not exists product_surface text not null default 'generation';

alter table generation_jobs
  add column if not exists brand_kit_version_id text,
  add column if not exists progress_percent integer
    check (progress_percent is null or progress_percent between 0 and 100);

alter table generation_job_attempts
  add column if not exists error_code text;

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'fk_generation_requests_project'
      and conrelid = 'generation_requests'::regclass
  ) then
    alter table generation_requests
      add constraint fk_generation_requests_project
      foreign key (project_id) references projects(id) on delete restrict;
  end if;

  if not exists (
    select 1 from pg_constraint
    where conname = 'ck_generation_requests_product_surface'
      and conrelid = 'generation_requests'::regclass
  ) then
    alter table generation_requests
      add constraint ck_generation_requests_product_surface
      check (product_surface in ('generation', 'ai_studio'));
  end if;
end $$;

create index if not exists ix_generation_requests_workspace_project_created
  on generation_requests(workspace_id, project_id, created_at desc);

create or replace function mediarouter_assert_generation_request_project_workspace()
returns trigger language plpgsql as $$
declare project_workspace text;
begin
  if new.project_id is not null then
    select workspace_id into project_workspace
      from projects where id = new.project_id;
    if project_workspace is null or project_workspace is distinct from new.workspace_id then
      raise exception 'generation project must belong to its request workspace'
        using errcode = '23503';
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists mediarouter_generation_request_project_workspace on generation_requests;
create trigger mediarouter_generation_request_project_workspace
before insert or update of workspace_id, project_id on generation_requests
for each row execute function mediarouter_assert_generation_request_project_workspace();

commit;