app/projects/migrations/0004_ai_studio.sql

-- AI Studio project context for the existing generation domain.
-- Apply after projects/0003_editor_persistence_rendering.sql.

begin;

alter table generation_requests add column if not exists project_id text;
alter table generation_requests add column if not exists product_surface text
  not null default 'generation'
  check (product_surface in ('generation', 'ai_studio'));

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;
end $$;

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

create or replace function mediarouter_assert_generation_request_project()
returns trigger language plpgsql as $$
declare project_workspace text;
declare project_status text;
declare asset_workspace text;
declare asset_project text;
begin
  if tg_op = 'UPDATE' and new.project_id is distinct from old.project_id then
    raise exception 'generation project ownership is immutable' using errcode = '23514';
  end if;
  if new.project_id is not null then
    select workspace_id, status into project_workspace, project_status
      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;
    if project_status is distinct from 'active' then
      raise exception 'generation project must be active' using errcode = '23514';
    end if;
    if new.input_asset_id is not null then
      select workspace_id, project_id into asset_workspace, asset_project
        from media_assets where id = new.input_asset_id;
      if asset_workspace is distinct from new.workspace_id
         or asset_project is distinct from new.project_id then
        raise exception 'generation input must belong to its selected project'
          using errcode = '23503';
      end if;
    end if;
  end if;
  return new;
end;
$$;

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

create or replace function mediarouter_assert_generation_job_workspace()
returns trigger language plpgsql as $$
declare request_workspace text;
declare request_project text;
declare asset_workspace text;
declare asset_project text;
begin
  select workspace_id, project_id into request_workspace, request_project
    from generation_requests where id = new.generation_request_id;
  if request_workspace is null or request_workspace is distinct from new.workspace_id then
    raise exception 'generation job must belong to its request workspace'
      using errcode = '23503';
  end if;
  if new.output_asset_id is not null then
    select workspace_id, project_id into asset_workspace, asset_project
      from media_assets where id = new.output_asset_id;
    if asset_workspace is null or asset_workspace is distinct from new.workspace_id then
      raise exception 'generation output asset must belong to its job workspace'
        using errcode = '23503';
    end if;
    if asset_project is distinct from request_project then
      raise exception 'generation output asset must belong to its request project'
        using errcode = '23503';
    end if;
  end if;
  return new;
end;
$$;

commit;