-- ════════════════════════════════════════════════════════════════
-- six_hype_schema.sql · Schema de insights do Six Hype
-- Roda no command_center_postgres (db command_center).
-- Idempotente — pode rodar várias vezes sem efeito colateral.
-- ════════════════════════════════════════════════════════════════

-- ── Schema ──
CREATE SCHEMA IF NOT EXISTS six_hype;
COMMENT ON SCHEMA six_hype IS 'Six Hype · insights de performance e demanda · alimentado por n8n (Meta + Vista Social via uni-mcp)';


-- ── 1. Personas (replicação minimal do app, pra joins) ──
CREATE TABLE IF NOT EXISTS personas (
  id            TEXT PRIMARY KEY,           -- ex: six-jaqueline, six-vinicius
  name          TEXT NOT NULL,
  persona_name  TEXT,
  specialty     TEXT,
  ig_username   TEXT,                       -- @sixestetica, @drajaquelinemonezi, etc.
  ig_user_id    TEXT,                       -- ID Meta da conta IG
  fb_page_id    TEXT,                       -- ID da Page Facebook vinculada
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── 2. Posts publicados (espelho do feed Meta + VS) ──
CREATE TABLE IF NOT EXISTS media_posts (
  id              TEXT PRIMARY KEY,         -- "meta:17891..." | "vs:abc123"
  source          TEXT NOT NULL,            -- 'meta' | 'vista_social'
  persona_id      TEXT REFERENCES personas(id) ON DELETE CASCADE,
  ig_user_id      TEXT,                     -- denormalizado pra query rápida
  format          TEXT,                     -- 'reel' | 'carousel' | 'image' | 'story'
  posted_at       TIMESTAMPTZ NOT NULL,
  caption         TEXT,
  permalink       TEXT,
  thumbnail_url   TEXT,
  hashtags        TEXT[],
  collected_at    TIMESTAMPTZ DEFAULT NOW(),
  raw             JSONB,                    -- payload original pra reprocessar
  UNIQUE (source, id)
);
CREATE INDEX IF NOT EXISTS idx_media_posts_persona_date ON media_posts(persona_id, posted_at DESC);
CREATE INDEX IF NOT EXISTS idx_media_posts_format ON media_posts(format);

-- ── 3. Métricas (snapshot diário, série temporal) ──
CREATE TABLE IF NOT EXISTS media_insights (
  post_id         TEXT NOT NULL REFERENCES media_posts(id) ON DELETE CASCADE,
  snapshot_date   DATE NOT NULL,
  impressions     INTEGER DEFAULT 0,
  reach           INTEGER DEFAULT 0,
  engagement      INTEGER DEFAULT 0,
  likes           INTEGER DEFAULT 0,
  comments        INTEGER DEFAULT 0,
  saves           INTEGER DEFAULT 0,
  shares          INTEGER DEFAULT 0,
  video_views     INTEGER,
  link_clicks     INTEGER,
  profile_visits  INTEGER,
  collected_at    TIMESTAMPTZ DEFAULT NOW(),
  raw             JSONB,
  PRIMARY KEY (post_id, snapshot_date)
);
CREATE INDEX IF NOT EXISTS idx_media_insights_date ON media_insights(snapshot_date DESC);

-- ── 4. Inbox topics (perguntas/menções dos pacientes) ──
CREATE TABLE IF NOT EXISTS inbox_topics (
  id              BIGSERIAL PRIMARY KEY,
  persona_id      TEXT REFERENCES personas(id) ON DELETE CASCADE,
  source          TEXT NOT NULL,            -- 'vista_social' | 'clint' | 'meta_dm' | 'meta_comment'
  external_id     TEXT,                     -- id do thread/msg na fonte
  channel         TEXT,                     -- 'dm' | 'comment' | 'whatsapp' | 'webchat'
  keyword         TEXT,                     -- termo extraído (lavieen, botox, preenchimento...)
  topic_category  TEXT,                     -- 'bioestimulador' | 'preenchimento' | 'compliance' | ...
  message_preview TEXT,                     -- primeiros 200 chars (sem PII no display)
  received_at     TIMESTAMPTZ NOT NULL,
  collected_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (source, external_id)
);
CREATE INDEX IF NOT EXISTS idx_inbox_topics_persona_keyword ON inbox_topics(persona_id, keyword);
CREATE INDEX IF NOT EXISTS idx_inbox_topics_received ON inbox_topics(received_at DESC);

-- ── 5. View: performance por formato (últimos 30d) ──
CREATE OR REPLACE VIEW performance_by_format AS
SELECT
  mp.persona_id,
  mp.format,
  COUNT(DISTINCT mp.id)                                  AS n_posts,
  COALESCE(AVG(mi.impressions), 0)::INTEGER              AS avg_impressions,
  COALESCE(AVG(mi.reach), 0)::INTEGER                    AS avg_reach,
  COALESCE(AVG(mi.engagement), 0)::INTEGER               AS avg_engagement,
  COALESCE(AVG(NULLIF(mi.impressions, 0) * 1.0
              / NULLIF(mi.reach, 0)), 0)::NUMERIC(6,3)    AS avg_density,
  MAX(mi.snapshot_date)                                   AS last_snapshot
FROM media_posts mp
LEFT JOIN LATERAL (
  SELECT * FROM media_insights
  WHERE post_id = mp.id
  ORDER BY snapshot_date DESC LIMIT 1
) mi ON TRUE
WHERE mp.posted_at >= NOW() - INTERVAL '30 days'
GROUP BY mp.persona_id, mp.format;

-- ── 6. View: top posts (últimos 30d, ordenado por engajamento) ──
CREATE OR REPLACE VIEW top_posts_30d AS
SELECT
  mp.id,
  mp.persona_id,
  mp.format,
  mp.posted_at,
  mp.caption,
  mp.permalink,
  mp.thumbnail_url,
  mi.impressions,
  mi.reach,
  mi.engagement,
  mi.saves,
  mi.shares,
  (COALESCE(mi.engagement, 0) + COALESCE(mi.saves, 0) * 3 + COALESCE(mi.shares, 0) * 5) AS score
FROM media_posts mp
JOIN LATERAL (
  SELECT * FROM media_insights
  WHERE post_id = mp.id
  ORDER BY snapshot_date DESC LIMIT 1
) mi ON TRUE
WHERE mp.posted_at >= NOW() - INTERVAL '30 days'
ORDER BY score DESC;

-- ── 7. View: keywords demandados (últimos 30d) ──
CREATE OR REPLACE VIEW topics_demanded_30d AS
SELECT
  persona_id,
  keyword,
  topic_category,
  COUNT(*)                          AS mentions,
  COUNT(DISTINCT source)            AS n_sources,
  MAX(received_at)                  AS last_mention,
  array_agg(DISTINCT channel)       AS channels
FROM inbox_topics
WHERE received_at >= NOW() - INTERVAL '30 days'
  AND keyword IS NOT NULL
GROUP BY persona_id, keyword, topic_category
ORDER BY mentions DESC;

-- ── 8. Audit log de coleta (debug n8n) ──
CREATE TABLE IF NOT EXISTS collection_runs (
  id              BIGSERIAL PRIMARY KEY,
  source          TEXT NOT NULL,            -- 'meta_pull' | 'vs_snapshot' | 'vs_inbox'
  started_at      TIMESTAMPTZ DEFAULT NOW(),
  finished_at     TIMESTAMPTZ,
  status          TEXT,                     -- 'ok' | 'partial' | 'error'
  rows_inserted   INTEGER DEFAULT 0,
  rows_updated    INTEGER DEFAULT 0,
  error_message   TEXT,
  meta            JSONB
);

-- ── Permissões ──
-- command_center_app já é dono (criou). Quando criarmos role six_hype_app dedicada,
-- daremos GRANT USAGE + SELECT por aqui.

-- ── Verificação ──
SELECT 'six_hype schema criado' AS status,
       (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'six_hype') AS tabelas,
       (SELECT COUNT(*) FROM information_schema.views  WHERE table_schema = 'six_hype') AS views;
