-- ============================================================================
-- SAG Manager — 0004 content + marketing + mail + agents + research
-- ============================================================================
-- - documents       : docs vault (VaultDoc)
-- - social_posts    : social calendar (SocialPost)
-- - social_accounts : OAuth + linked platform accounts
-- - music_releases  : Music workspace (MusicRelease)
-- - music_bookings  : Music workspace (MusicBooking)
-- - music_contacts  : Music workspace (MusicContact)
-- - music_streaming : streaming snapshots (StreamingSnapshot)
-- - gmail_threads   : cached Gmail thread metadata for the inbox
-- - gmail_thread_classifications : MA2 secretary-agent classifications
-- - mail_calendar_suggestions    : MA3 "auto-schedule" suggestions
-- - agent_threads   : AI2 per-agent chat threads
-- - research_findings : AI4 autoresearch findings
-- ============================================================================

create extension if not exists "pgcrypto";

-- ─────────────────────────────────────────────────────────────────────────
-- documents — docs vault (mirrors VaultDoc in document-vault.tsx)
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.documents (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  doc_type             text not null,
  name                 text not null,
  notes                text,
  external_url         text,
  inline_data          text,                          -- base64-inlined small docs (<= 500KB)
  inline_mime_type     text,
  file_size            integer,
  ai_summary           text,
  extracted_text       text,                          -- OCR result
  ocr_at               timestamptz,
  ocr_model            text,
  uploaded_at          timestamptz not null default now(),
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists documents_entity_idx on public.documents (entity_slug);
create index if not exists documents_doc_type_idx on public.documents (doc_type);
create index if not exists documents_uploaded_idx on public.documents (uploaded_at desc);

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

-- ─────────────────────────────────────────────────────────────────────────
-- social_accounts — connected platforms per entity
-- Per-platform OAuth credentials encrypted at the app layer before insert.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.social_accounts (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  platform             text not null
    check (platform in ('instagram','facebook','tiktok','x','youtube','linkedin','threads','pinterest')),
  handle               text not null,
  display_name         text,
  oauth_token_encrypted text,                         -- ciphertext, encrypt app-side
  oauth_refresh_encrypted text,
  oauth_expires_at     timestamptz,
  follower_count       integer,
  follower_count_at    timestamptz,
  metadata             jsonb not null default '{}'::jsonb,
  active               boolean not null default true,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),
  unique (entity_slug, platform)
);

create index if not exists social_accounts_entity_idx on public.social_accounts (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- social_posts — calendar (mirrors SocialPost in post-types.ts v2)
-- `variants` (per-platform caption overrides), `platforms` (target list),
-- `media` (PostMediaRef[]) and `hashtags` are JSONB so the existing client
-- shape round-trips losslessly.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.social_posts (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  content              text not null,
  variants             jsonb not null default '{}'::jsonb,    -- PostVariants
  platforms            jsonb not null default '[]'::jsonb,    -- SocialPlatformId[]
  scheduled_at         timestamptz,
  media                jsonb not null default '[]'::jsonb,    -- PostMediaRef[]
  hashtags             jsonb not null default '[]'::jsonb,    -- string[]
  status               text not null default 'Draft'
    check (status in ('Draft','Pending Review','Scheduled','Posted','Failed','Cancelled')),
  notes                text,
  approved_by          text,
  approved_at          timestamptz,
  review_requested_at  timestamptz,
  source_release_id    uuid,                          -- music_releases.id if seeded from a release
  posted_at            timestamptz,
  performance          jsonb not null default '{}'::jsonb,    -- engagement metrics if polled back
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists social_posts_entity_scheduled_idx
  on public.social_posts (entity_slug, scheduled_at);
create index if not exists social_posts_status_idx on public.social_posts (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- music_releases — MusicRelease
-- Splits are JSONB (SplitShare[]) since they vary per release and need
-- to sum to 100 — validation lives app-side.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.music_releases (
  id                   uuid primary key default gen_random_uuid(),
  artist_slug          text not null
    check (artist_slug in ('riich-villainz','dj-ricoveli')),
  title                text not null,
  release_type         text not null
    check (release_type in ('Single','EP','Album','Mixtape','Live','Remix','Compilation')),
  status               text not null default 'Concept'
    check (status in ('Concept','Recording','Mixing','Mastering','Distribution Submitted','Released','Postponed','Shelved')),
  release_date         timestamptz,
  platforms            text,                          -- comma-separated platform names
  isrc                 text,
  upc                  text,
  splits               jsonb not null default '[]'::jsonb,    -- SplitShare[]
  notes                text,
  artwork_media_id     text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists music_releases_artist_idx on public.music_releases (artist_slug);
create index if not exists music_releases_release_date_idx on public.music_releases (release_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- music_bookings — MusicBooking
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.music_bookings (
  id                   uuid primary key default gen_random_uuid(),
  artist_slug          text not null
    check (artist_slug in ('riich-villainz','dj-ricoveli')),
  venue                text not null,
  location             text,
  booking_date         timestamptz not null,
  show_time            text,                          -- HH:MM string
  fee                  numeric(14,2) not null default 0,
  deposit              numeric(14,2),
  status               text not null default 'Inquiry'
    check (status in ('Inquiry','Held','Contract Sent','Confirmed','Performed','Cancelled','Paid')),
  booker_name          text,
  booker_email         text,
  booker_phone         text,
  notes                text,
  contract_media_id    text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists music_bookings_artist_idx on public.music_bookings (artist_slug);
create index if not exists music_bookings_date_idx on public.music_bookings (booking_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- music_contacts — MusicContact (industry contacts per artist)
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.music_contacts (
  id                   uuid primary key default gen_random_uuid(),
  artist_slug          text not null
    check (artist_slug in ('riich-villainz','dj-ricoveli')),
  name                 text not null,
  role                 text not null
    check (role in ('Booker','Manager','Agent','Producer','Engineer','Lawyer','Accountant','Distributor','PR','Venue','Other')),
  company              text,
  email                text,
  phone                text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists music_contacts_artist_idx on public.music_contacts (artist_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- music_streaming — StreamingSnapshot (manual or polled platform stats)
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.music_streaming (
  id                   uuid primary key default gen_random_uuid(),
  artist_slug          text not null
    check (artist_slug in ('riich-villainz','dj-ricoveli')),
  as_of                timestamptz not null,
  spotify_monthly_listeners integer,
  spotify_followers    integer,
  spotify_top_track    text,
  apple_monthly_listeners integer,
  apple_top_track      text,
  youtube_subscribers  integer,
  youtube_top_video    text,
  soundcloud_followers integer,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists music_streaming_artist_idx on public.music_streaming (artist_slug, as_of desc);

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

-- ─────────────────────────────────────────────────────────────────────────
-- gmail_threads — cached thread metadata for the inbox
-- Body / full message detail is fetched on demand from Gmail; this table
-- caches the headline fields for fast list rendering and joins to the
-- classifications + calendar-suggestion tables.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.gmail_threads (
  thread_id            text primary key,              -- Gmail's stable thread id
  history_id           text,
  subject              text,
  from_addr            text,
  from_name            text,
  snippet              text,
  internal_date        timestamptz,
  message_count        integer not null default 1,
  labels               jsonb not null default '[]'::jsonb,
  has_attachments      boolean not null default false,
  raw                  jsonb,                         -- optional full message metadata cache
  fetched_at           timestamptz not null default now(),
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists gmail_threads_internal_date_idx
  on public.gmail_threads (internal_date desc);
create index if not exists gmail_threads_fetched_idx on public.gmail_threads (fetched_at desc);

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

-- ─────────────────────────────────────────────────────────────────────────
-- gmail_thread_classifications — MA2 secretary-agent triage output
-- Mirrors ThreadClassification in classify-thread.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.gmail_thread_classifications (
  thread_id            text primary key,              -- same id as gmail_threads.thread_id
  category             text not null,
  urgency              text not null
    check (urgency in ('high','normal','low')),
  confidence           text not null
    check (confidence in ('high','medium','low')),
  rationale            text,
  entity_slug          text,                          -- detected SAG entity, if any
  suggested_actions    jsonb not null default '[]'::jsonb,    -- string[]
  model                text,                          -- e.g. "claude-haiku-4-5-20251001"
  raw                  jsonb,                         -- full classifier response for debugging
  classified_at        timestamptz not null default now(),
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists gmail_classifications_entity_idx
  on public.gmail_thread_classifications (entity_slug);
create index if not exists gmail_classifications_category_idx
  on public.gmail_thread_classifications (category);

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

-- ─────────────────────────────────────────────────────────────────────────
-- mail_calendar_suggestions — MA3 "auto-schedule" suggestions
-- For each detected meeting/event from a Gmail thread, store the proposed
-- title + time(s) the operator can confirm to push to calendar.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.mail_calendar_suggestions (
  id                   uuid primary key default gen_random_uuid(),
  thread_id            text not null,                 -- source Gmail thread
  message_id           text,                          -- specific message that triggered it
  title                text not null,
  description          text,
  proposed_times       jsonb not null default '[]'::jsonb,    -- [{ start: ISO, end: ISO }]
  attendees            jsonb not null default '[]'::jsonb,
  entity_slug          text,
  status               text not null default 'suggested'
    check (status in ('suggested','accepted','dismissed','scheduled')),
  scheduled_event_id   text,                          -- calendar event id if accepted
  model                text,
  raw                  jsonb,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists mail_calendar_thread_idx on public.mail_calendar_suggestions (thread_id);
create index if not exists mail_calendar_status_idx on public.mail_calendar_suggestions (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- agent_threads — AI2 per-agent chat history
-- Messages stored inline as JSONB so the existing AgentThread shape from
-- agents/threads.ts round-trips losslessly. If message volume grows we can
-- split into a separate `agent_messages` child table later — for now this
-- matches the localStorage write pattern (read-modify-write the whole array).
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.agent_threads (
  id                   uuid primary key default gen_random_uuid(),
  agent_slug           text not null,                 -- cfo | compliance | secretary | marketing | hr | ...
  entity_slug          text,                          -- optional entity scoping
  title                text not null,
  messages             jsonb not null default '[]'::jsonb,    -- AgentMessage[]
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists agent_threads_agent_idx on public.agent_threads (agent_slug, updated_at desc);
create index if not exists agent_threads_entity_idx on public.agent_threads (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- research_findings — AI4 autoresearch findings
-- Mirrors ResearchFinding in save-research-finding.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.research_findings (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text,
  title                text not null,
  summary              text not null,
  sources              jsonb not null default '[]'::jsonb,    -- string[] of URLs
  citations            jsonb not null default '[]'::jsonb,    -- { url, quote? }[]
  score                numeric(4,2),                 -- 0-10 relevance
  tags                 jsonb not null default '[]'::jsonb,    -- string[]
  document_id          uuid,                          -- optional ref to documents row if saved to vault
  generated_at         timestamptz not null default now(),
  model                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists research_findings_entity_idx on public.research_findings (entity_slug);
create index if not exists research_findings_generated_idx
  on public.research_findings (generated_at desc);

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