app/social/migrations/0003_social_integrity_postgres.sql

-- Social foundation hardening. Apply after 0001 and 0002.
-- This migration is additive: it adds database-side timestamp maintenance and
-- tenant relationship checks without changing or deleting customer data.

begin;

create or replace function mediarouter_social_touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

do $$
declare
  table_name text;
begin
  foreach table_name in array array[
    'social_accounts', 'social_account_tokens', 'social_account_capabilities',
    'social_campaigns', 'social_posts', 'social_post_targets',
    'social_schedules', 'social_jobs'
  ] loop
    execute format(
      'drop trigger if exists mediarouter_social_touch_updated_at on %I',
      table_name
    );
    execute format(
      'create trigger mediarouter_social_touch_updated_at before update on %I for each row execute function mediarouter_social_touch_updated_at()',
      table_name
    );
  end loop;
end;
$$;

-- SQLAlchemy enums are represented as text for compatibility with the
-- existing schema. These constraints keep direct SQL writes within the same
-- state vocabulary enforced by the application state machine.
do $$
begin
  if not exists (select 1 from pg_constraint where conname = 'ck_social_accounts_status') then
    alter table social_accounts add constraint ck_social_accounts_status
      check (status in ('pending', 'connected', 'reauth_required', 'disconnected', 'error'));
  end if;
  if not exists (select 1 from pg_constraint where conname = 'ck_social_posts_status') then
    alter table social_posts add constraint ck_social_posts_status
      check (status in ('draft', 'scheduled', 'queued', 'preparing', 'processing', 'uploading', 'publishing', 'published', 'partial_success', 'retrying', 'failed', 'cancelled'));
  end if;
  if not exists (select 1 from pg_constraint where conname = 'ck_social_post_targets_status') then
    alter table social_post_targets add constraint ck_social_post_targets_status
      check (status in ('draft', 'scheduled', 'queued', 'preparing', 'processing', 'uploading', 'publishing', 'published', 'partial_success', 'retrying', 'failed', 'cancelled'));
  end if;
  if not exists (select 1 from pg_constraint where conname = 'ck_social_schedules_status') then
    alter table social_schedules add constraint ck_social_schedules_status
      check (status in ('scheduled', 'queued', 'cancelled'));
  end if;
  if not exists (select 1 from pg_constraint where conname = 'ck_social_jobs_status') then
    alter table social_jobs add constraint ck_social_jobs_status
      check (status in ('draft', 'scheduled', 'queued', 'preparing', 'processing', 'uploading', 'publishing', 'published', 'retrying', 'failed', 'cancelled'));
  end if;
  if not exists (select 1 from pg_constraint where conname = 'ck_social_jobs_attempt_bounds') then
    alter table social_jobs add constraint ck_social_jobs_attempt_bounds
      check (attempt_count >= 0 and max_attempts >= 0);
  end if;
end;
$$;

create or replace function mediarouter_social_assert_workspace_integrity()
returns trigger
language plpgsql
as $$
declare
  post_workspace text;
  related_workspace text;
  related_provider text;
  related_post_id text;
begin
  if tg_op = 'UPDATE' and tg_table_name in ('social_posts', 'social_jobs')
     and new.workspace_id is distinct from old.workspace_id then
    raise exception 'social workspace cannot be reassigned'
      using errcode = '23514';
  end if;

  if tg_table_name = 'social_posts' then
    if new.campaign_id is not null then
      select workspace_id into related_workspace
      from social_campaigns where id = new.campaign_id;
      if related_workspace is distinct from new.workspace_id then
        raise exception 'social post campaign must belong to the same workspace'
          using errcode = '23503';
      end if;
    end if;
    if new.source_variant_id is not null then
      select workspace_id into related_workspace
      from media_variants where id = new.source_variant_id;
      if related_workspace is distinct from new.workspace_id then
        raise exception 'social post variant must belong to the same workspace'
          using errcode = '23503';
      end if;
    end if;
  elsif tg_table_name = 'social_post_targets' then
    select workspace_id into post_workspace
    from social_posts where id = new.social_post_id;
    select workspace_id, provider into related_workspace, related_provider
    from social_accounts where id = new.social_account_id;
    if post_workspace is null or related_workspace is distinct from post_workspace then
      raise exception 'social post target account must belong to the post workspace'
        using errcode = '23503';
    end if;
    if new.provider is distinct from related_provider then
      raise exception 'social post target provider must match its account'
        using errcode = '23514';
    end if;
  elsif tg_table_name = 'social_post_media' and new.media_variant_id is not null then
    select workspace_id into post_workspace
    from social_posts where id = new.social_post_id;
    select workspace_id into related_workspace
    from media_variants where id = new.media_variant_id;
    if post_workspace is null or related_workspace is distinct from post_workspace then
      raise exception 'social post media variant must belong to the post workspace'
        using errcode = '23503';
    end if;
  elsif tg_table_name = 'social_jobs' then
    select workspace_id into post_workspace
    from social_posts where id = new.social_post_id;
    if post_workspace is distinct from new.workspace_id then
      raise exception 'social job must belong to the post workspace'
        using errcode = '23503';
    end if;
    if new.social_post_target_id is not null then
      select social_post_id, provider into related_post_id, related_provider
      from social_post_targets where id = new.social_post_target_id;
      if related_post_id is distinct from new.social_post_id then
        raise exception 'social job target must belong to the social post'
          using errcode = '23503';
      end if;
      if new.provider is not null and new.provider is distinct from related_provider then
        raise exception 'social job provider must match its target'
          using errcode = '23514';
      end if;
    end if;
  elsif tg_table_name = 'social_post_metrics' then
    if new.social_post_target_id is not null then
      select social_post_id, provider into related_post_id, related_provider
      from social_post_targets where id = new.social_post_target_id;
      if related_post_id is distinct from new.social_post_id then
        raise exception 'social metric target must belong to the social post'
          using errcode = '23503';
      end if;
      if new.provider is distinct from related_provider then
        raise exception 'social metric provider must match its target'
          using errcode = '23514';
      end if;
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists mediarouter_social_post_workspace_integrity on social_posts;
create trigger mediarouter_social_post_workspace_integrity
before insert or update of workspace_id, campaign_id, source_variant_id on social_posts
for each row execute function mediarouter_social_assert_workspace_integrity();

drop trigger if exists mediarouter_social_target_workspace_integrity on social_post_targets;
create trigger mediarouter_social_target_workspace_integrity
before insert or update of social_post_id, social_account_id, provider on social_post_targets
for each row execute function mediarouter_social_assert_workspace_integrity();

drop trigger if exists mediarouter_social_post_media_workspace_integrity on social_post_media;
create trigger mediarouter_social_post_media_workspace_integrity
before insert or update of social_post_id, media_variant_id on social_post_media
for each row execute function mediarouter_social_assert_workspace_integrity();

drop trigger if exists mediarouter_social_job_workspace_integrity on social_jobs;
create trigger mediarouter_social_job_workspace_integrity
before insert or update of workspace_id, social_post_id, social_post_target_id, provider on social_jobs
for each row execute function mediarouter_social_assert_workspace_integrity();

drop trigger if exists mediarouter_social_metric_workspace_integrity on social_post_metrics;
create trigger mediarouter_social_metric_workspace_integrity
before insert or update of social_post_id, social_post_target_id, provider on social_post_metrics
for each row execute function mediarouter_social_assert_workspace_integrity();

commit;