-- migrations/002_niche_topics.sql
-- Niche builder + topic approval flow

-- ─── NICHE PROFILES (more precise than basic profile) ─────────────────────
CREATE TABLE IF NOT EXISTS niche_profiles (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,                        -- "My Fitness Channel"
  is_active       BOOLEAN NOT NULL DEFAULT false,

  -- Core niche definition
  industry        TEXT NOT NULL,                        -- "Health & Fitness"
  sub_niche       TEXT NOT NULL,                        -- "Weight loss for busy moms"
  content_angle   TEXT NOT NULL,                        -- "Science-based, no BS"
  unique_value    TEXT,                                 -- "I lost 40kg myself"

  -- Audience
  audience_age    TEXT,                                 -- "28-45"
  audience_gender TEXT,                                 -- "Primarily women"
  audience_level  TEXT,                                 -- "Beginner / Intermediate / Expert"
  audience_goals  TEXT[],                               -- ["lose weight", "build habits"]
  audience_pains  TEXT[],                               -- ["no time", "tried everything"]

  -- Content preferences
  platforms       TEXT[],                               -- ["youtube", "instagram", "x"]
  content_formats TEXT[],                               -- ["long-form", "shorts", "blog"]
  topics_to_avoid TEXT[],                               -- ["supplements", "keto"]
  competitor_urls TEXT[],                               -- for style reference

  -- Keywords for Intel Agent
  seed_keywords   TEXT[],                               -- ["weight loss", "HIIT", "meal prep"]
  trending_sources TEXT[],                              -- ["reddit.com/r/fitness", "examine.com"]

  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_niche_active ON niche_profiles(user_id) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_niche_user ON niche_profiles(user_id);

-- ─── TOPIC PROPOSALS ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS topic_proposals (
  id              UUID PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  niche_id        UUID NOT NULL REFERENCES niche_profiles(id) ON DELETE CASCADE,
  status          TEXT NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','approved','rejected','modified')),

  -- AI proposed
  proposed_title  TEXT NOT NULL,
  proposed_angle  TEXT,
  proposed_hook   TEXT,
  search_intent   TEXT,                                 -- informational/commercial/navigational
  difficulty      TEXT,                                 -- easy/medium/hard
  why_now         TEXT,                                 -- why this topic is timely

  -- Client approved/modified
  final_title     TEXT,
  client_notes    TEXT,

  -- Tracking
  intel_report_id UUID,
  used_in_run_id  UUID REFERENCES pipeline_runs(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  approved_at     TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_proposals_user ON topic_proposals(user_id, status, created_at DESC);

-- ─── PIPELINE RUN: link to approved topics ────────────────────────────────
ALTER TABLE pipeline_runs ADD COLUMN IF NOT EXISTS niche_id UUID REFERENCES niche_profiles(id);
ALTER TABLE pipeline_runs ADD COLUMN IF NOT EXISTS approved_topic_ids UUID[];

-- Onboarding tracking
ALTER TABLE users ADD COLUMN IF NOT EXISTS onboarding_completed BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE users ADD COLUMN IF NOT EXISTS onboarding_completed_at TIMESTAMPTZ;