schema.sql

create extension if not exists pgcrypto;

create table if not exists public.tenants (
  id uuid primary key default gen_random_uuid(),
  business_name text not null,
  brand_logo_url text,
  menu_with_prices text,
  business_description text not null,
  goal text not null check (goal in ('leads', 'awareness', 'engagement', 'sales')),
  platforms text[] not null default '{}',
  posting_frequency text not null default 'daily' check (posting_frequency in ('daily', 'weekly')),
  onboarding_meta jsonb not null default '{}'::jsonb,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.content_posts (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  platform text not null,
  post_date date not null,
  goal text not null,
  caption text not null,
  hashtags text[] not null default '{}',
  image_url text,
  drive_file_id text,
  status text not null default 'draft',
  error_message text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  unique (tenant_id, platform, post_date)
);

create table if not exists public.daily_runs (
  id bigint generated always as identity primary key,
  run_date date not null,
  triggered_by text not null,
  status text not null default 'started',
  total_tenants integer not null default 0,
  processed_tenants integer not null default 0,
  failed_tenants integer not null default 0,
  details jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists public.platform_connections (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  platform text not null,
  account_label text,
  token_ref text,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  unique (tenant_id, platform)
);

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

drop trigger if exists trg_tenants_updated_at on public.tenants;
create trigger trg_tenants_updated_at
before update on public.tenants
for each row
execute function public.set_updated_at();