4ventures-client-portal/sql/creatives_module_migration.sql

-- Migration: Module Creatives (CREATIVOS_BRIEF.md)
-- Multi-tenant schema using tenant_id references public.tenants(id)

-- 1. Extend public.tenants
alter table public.tenants
  add column if not exists meta_ad_account_id text,
  add column if not exists slack_channel_id text,
  add column if not exists drive_folder_id text,
  add column if not exists creatives_enabled boolean not null default false;

-- 2. Adapt/Create public.creatives
-- First, adapt existing table or create if not exists
create table if not exists public.creatives (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  campaign_name text,
  adset_id text,
  meta_ad_id text unique,
  format text not null check (format in ('estatico','carrusel','video')),
  angle text,
  hook text,
  primary_text text,
  headline text,
  cta text,
  asset_url text,
  thumbnail_url text,
  status text not null default 'borrador'
    check (status in ('borrador','pendiente_aprobacion','aprobada','rechazada','publicada','fatigada','retirada')),
  visibility text not null default 'agency' check (visibility in ('client','agency')),
  parent_id uuid references public.creatives(id) on delete set null,
  generation_request_id uuid,
  created_by text not null default 'agency',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Ensure all columns exist and checks are aligned if table already existed
alter table public.creatives add column if not exists campaign_name text;
alter table public.creatives add column if not exists adset_id text;
alter table public.creatives add column if not exists meta_ad_id text;
alter table public.creatives add column if not exists angle text;
alter table public.creatives add column if not exists hook text;
alter table public.creatives add column if not exists asset_url text;
alter table public.creatives add column if not exists thumbnail_url text;
alter table public.creatives add column if not exists visibility text not null default 'agency';
alter table public.creatives add column if not exists parent_id uuid references public.creatives(id) on delete set null;
alter table public.creatives add column if not exists generation_request_id uuid;

-- Change created_by to text if it was uuid
do $$
begin
  if exists (
    select 1 from information_schema.columns 
    where table_schema = 'public' and table_name = 'creatives' and column_name = 'created_by' and data_type = 'uuid'
  ) then
    alter table public.creatives drop constraint if exists creatives_created_by_fkey;
    alter table public.creatives alter column created_by type text using created_by::text;
    alter table public.creatives alter column created_by set default 'agency';
  end if;
end $$;

-- Drop obsolete constraints if present
alter table public.creatives drop constraint if exists creatives_status_check;
alter table public.creatives drop constraint if exists creatives_format_check;
alter table public.creatives drop constraint if exists creatives_visibility_check;
alter table public.creatives drop constraint if exists creatives_meta_ad_id_key;

-- Ensure meta_ad_id unique constraint
alter table public.creatives add constraint creatives_meta_ad_id_key unique (meta_ad_id);

-- Enforce check constraints from brief
alter table public.creatives add constraint creatives_status_check 
  check (status in ('borrador','revision_agencia','pendiente_aprobacion','aprobada','rechazada','publicada','fatigada','retirada'));

alter table public.creatives add constraint creatives_format_check 
  check (format in ('estatico','carrusel','video','image','carousel','reel','story','rsa','pmax','pmax_landscape','pmax_square','pmax_portrait','pmax_vertical','pmax_logo','youtube','other'));

alter table public.creatives add constraint creatives_visibility_check 
  check (visibility in ('client','agency'));

create index if not exists creatives_tenant_status_idx on public.creatives (tenant_id, status);
create index if not exists creatives_meta_ad_id_idx on public.creatives (meta_ad_id);

-- 3. creative_metrics_daily
create table if not exists public.creative_metrics_daily (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  creative_id uuid not null references public.creatives(id) on delete cascade,
  meta_ad_id text not null,
  date date not null,
  impressions integer default 0,
  reach integer default 0,
  frequency numeric(6,2) default 0,
  clicks integer default 0,
  ctr numeric(6,3) default 0,
  cpm numeric(10,2) default 0,
  cpc numeric(10,2) default 0,
  spend numeric(10,2) default 0,
  results integer default 0,
  cpa numeric(10,2),
  raw jsonb,
  created_at timestamptz not null default now(),
  unique (meta_ad_id, date)
);
create index if not exists creative_metrics_daily_tenant_date_idx on public.creative_metrics_daily (tenant_id, date);

-- 4. fatigue_rules
create table if not exists public.fatigue_rules (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null unique references public.tenants(id) on delete cascade,
  lookback_days integer not null default 7,
  min_impressions integer not null default 2000,
  max_frequency numeric(4,2) not null default 3.0,
  ctr_drop_pct numeric(5,2) not null default 30,
  cpa_rise_pct numeric(5,2) not null default 25,
  signals_required integer not null default 2,
  auto_request_relief boolean not null default true,
  updated_at timestamptz not null default now()
);

-- 5. generation_requests
create table if not exists public.generation_requests (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  reason text not null check (reason in ('fatiga','calendario','nueva_campana','manual')),
  source_creative_id uuid references public.creatives(id) on delete set null,
  brief jsonb not null,
  status text not null default 'pendiente'
    check (status in ('pendiente','en_proceso','completada','fallida')),
  result jsonb,
  cost_estimate jsonb,
  error text,
  requested_at timestamptz not null default now(),
  completed_at timestamptz
);
create index if not exists generation_requests_tenant_status_idx on public.generation_requests (tenant_id, status);

-- Foreign key linking creatives.generation_request_id to generation_requests.id
do $$
begin
  if not exists (
    select 1 from information_schema.table_constraints 
    where constraint_name = 'creatives_generation_request_id_fkey'
  ) then
    alter table public.creatives 
      add constraint creatives_generation_request_id_fkey 
      foreign key (generation_request_id) references public.generation_requests(id) on delete set null;
  end if;
end $$;

-- 6. approvals
create table if not exists public.approvals (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  creative_id uuid not null references public.creatives(id) on delete cascade,
  user_id uuid references public.users(id) on delete set null,
  actor_role text not null check (actor_role in ('client','agency')),
  decision text not null check (decision in ('aprobada','rechazada')),
  comment text,
  channel text not null default 'app' check (channel in ('app','slack')),
  created_at timestamptz not null default now()
);
create index if not exists approvals_tenant_creative_idx on public.approvals (tenant_id, creative_id);

-- 7. creative_events
create table if not exists public.creative_events (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  creative_id uuid references public.creatives(id) on delete cascade,
  event_type text not null,
  actor text not null,
  from_status text,
  to_status text,
  payload jsonb,
  visibility text not null default 'agency' check (visibility in ('client','agency')),
  created_at timestamptz not null default now()
);
create index if not exists creative_events_tenant_created_idx on public.creative_events (tenant_id, created_at desc);

-- 8. Seed Blue Parrot School (bps)
insert into public.fatigue_rules (
  tenant_id,
  lookback_days,
  min_impressions,
  max_frequency,
  ctr_drop_pct,
  cpa_rise_pct,
  signals_required,
  auto_request_relief
)
select 
  id,
  7,
  2000,
  3.0,
  30.0,
  25.0,
  2,
  true
from public.tenants
where slug = 'bps'
on conflict (tenant_id) do update set
  updated_at = now();

update public.tenants
set 
  creatives_enabled = true,
  meta_ad_account_id = coalesce(meta_ad_account_id, 'act_1052328112282270')
where slug = 'bps';