app/projects/migrations/0002_project_resources.sql

-- Minimum authoritative Project -> Asset / durable Generation Job links.
-- Apply after projects/0001 and security/0003_generation_domain_postgres.sql.

begin;

alter table media_assets add column if not exists project_id text;

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

create index if not exists ix_media_assets_workspace_project_created
  on media_assets(workspace_id, project_id, created_at desc);

create or replace function mediarouter_assert_media_asset_project_workspace()
returns trigger language plpgsql as $$
declare project_workspace text;
declare project_status text;
begin
  if tg_op = 'UPDATE' and new.workspace_id is distinct from old.workspace_id then
    raise exception 'canonical asset workspace 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 'project asset must belong to its project workspace'
        using errcode = '23503';
    end if;
    if project_status is distinct from 'active' then
      raise exception 'canonical assets cannot be attached to an archived project'
        using errcode = '23514';
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists mediarouter_media_asset_project_workspace on media_assets;
create trigger mediarouter_media_asset_project_workspace
before insert or update of workspace_id, project_id on media_assets
for each row execute function mediarouter_assert_media_asset_project_workspace();

create table if not exists project_generation_jobs (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  project_id text not null references projects(id) on delete restrict,
  generation_job_id text not null references generation_jobs(id) on delete restrict,
  attached_by text not null references users(id) on delete restrict,
  created_at timestamptz not null default now(),
  constraint uq_project_generation_job unique (generation_job_id)
);

create index if not exists ix_project_generation_jobs_workspace_project_created
  on project_generation_jobs(workspace_id, project_id, created_at desc);

create or replace function mediarouter_assert_project_generation_job_workspace()
returns trigger language plpgsql as $$
declare project_workspace text;
declare project_status text;
declare job_workspace text;
begin
  if tg_op = 'UPDATE' and (
    new.workspace_id is distinct from old.workspace_id
    or new.project_id is distinct from old.project_id
    or new.generation_job_id is distinct from old.generation_job_id
    or new.attached_by is distinct from old.attached_by
  ) then
    raise exception 'project generation job ownership is immutable'
      using errcode = '23514';
  end if;

  select workspace_id, status into project_workspace, project_status
    from projects where id = new.project_id;
  select workspace_id into job_workspace
    from generation_jobs where id = new.generation_job_id;

  if project_workspace is null
     or job_workspace is null
     or project_workspace is distinct from new.workspace_id
     or job_workspace is distinct from new.workspace_id then
    raise exception 'project generation job resources must share a workspace'
      using errcode = '23503';
  end if;
  if project_status is distinct from 'active' then
    raise exception 'generation jobs cannot be attached to an archived project'
      using errcode = '23514';
  end if;
  if not exists (
    select 1 from workspace_memberships m
    where m.workspace_id = new.workspace_id
      and m.user_id = new.attached_by
      and m.status = 'active'
  ) then
    raise exception 'generation job attachment requires active workspace membership'
      using errcode = '23503';
  end if;
  return new;
end;
$$;

drop trigger if exists mediarouter_project_generation_job_workspace
  on project_generation_jobs;
create trigger mediarouter_project_generation_job_workspace
before insert or update on project_generation_jobs
for each row execute function mediarouter_assert_project_generation_job_workspace();

alter table project_generation_jobs enable row level security;
alter table project_generation_jobs force row level security;

drop policy if exists project_generation_jobs_select on project_generation_jobs;
create policy project_generation_jobs_select on project_generation_jobs for select using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = project_generation_jobs.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
  )
);

drop policy if exists project_generation_jobs_insert on project_generation_jobs;
create policy project_generation_jobs_insert on project_generation_jobs for insert with check (
  workspace_id = current_setting('app.workspace_id', true)
  and attached_by = current_setting('app.user_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = project_generation_jobs.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
  )
);

drop policy if exists project_generation_jobs_delete on project_generation_jobs;
create policy project_generation_jobs_delete on project_generation_jobs for delete using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = project_generation_jobs.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
  )
);

commit;