-- ════════════════════════════════════════════════════════════════
-- six_hype_app_state_schema.sql · Backup remoto do estado do app
--
-- Sprint M (2026-05-25). Complementa six_hype_schema.sql.
-- Replica entidades do Zustand local pra Supabase pra que o usuário
-- possa trocar de device, recuperar de wipe do navegador e ter audit log.
--
-- IDs preservam o que o app gera (createId('prefix')) — não tem PK serial
-- aqui porque o app é fonte da verdade até virarmos full multi-user com
-- write/sync conflitando.
--
-- Idempotente. RLS preparado mas não habilitado por padrão.
-- ════════════════════════════════════════════════════════════════


-- ── 1. App users (deduplicação por email — vem do magic link auth) ──
-- Já existe auth.db no VPS, isso aqui é só pro JOIN remoto.
CREATE TABLE IF NOT EXISTS app_users (
  id              TEXT PRIMARY KEY,
  email           TEXT NOT NULL UNIQUE,
  name            TEXT,
  role            TEXT,
  beta_access     BOOLEAN DEFAULT FALSE,
  last_sync_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── 2. Operators (equipe Six — Tiff/Kaique/Dalton etc) ──
CREATE TABLE IF NOT EXISTS operators (
  id              TEXT PRIMARY KEY,
  owner_user_id   TEXT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,
  role            TEXT,                       -- founder/manager/video/design/social/persona
  email           TEXT,
  avatar_seed     TEXT,
  is_owner        BOOLEAN DEFAULT FALSE,
  is_external     BOOLEAN DEFAULT FALSE,
  active          BOOLEAN DEFAULT TRUE,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_operators_owner ON operators(owner_user_id);

-- ── 3. Personagens (Sprint H — elenco rotativo) ──
CREATE TABLE IF NOT EXISTS personagens (
  id                       TEXT PRIMARY KEY,
  owner_user_id            TEXT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  nome                     TEXT NOT NULL,
  categoria                TEXT NOT NULL,    -- medico/esteticista/socio/cliente/permuta/colaborador
  papel_narrativo_padrao   TEXT NOT NULL,
  crm                      TEXT,             -- compliance CFM
  rqe                      TEXT,
  especialidade            TEXT,
  instagram_handle         TEXT,
  foto_url                 TEXT,
  consent_imagem_until     DATE,             -- LGPD
  procedimentos_associados TEXT[],
  tom_editorial            TEXT,
  restricoes               TEXT,
  ativo                    BOOLEAN DEFAULT TRUE,
  created_at               TIMESTAMPTZ DEFAULT NOW(),
  updated_at               TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_personagens_owner ON personagens(owner_user_id);
CREATE INDEX IF NOT EXISTS idx_personagens_categoria ON personagens(categoria);

-- ── 4. Campanhas (Sprint I — arcos narrativos) ──
CREATE TABLE IF NOT EXISTS campanhas (
  id                          TEXT PRIMARY KEY,
  owner_user_id               TEXT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  nome                        TEXT NOT NULL,
  big_idea                    TEXT,
  profile_id                  TEXT,
  product_id                  TEXT,
  product_name                TEXT,
  personagem_principal_id     TEXT REFERENCES personagens(id) ON DELETE SET NULL,
  status                      TEXT DEFAULT 'rascunho',  -- rascunho/ativa/pausada/concluida/arquivada
  data_inicio                 DATE,
  data_fim                    DATE,
  objective                   TEXT,
  atos                        JSONB DEFAULT '[]'::jsonb,  -- CampanhaAto[] inline
  created_at                  TIMESTAMPTZ DEFAULT NOW(),
  updated_at                  TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_campanhas_owner ON campanhas(owner_user_id);
CREATE INDEX IF NOT EXISTS idx_campanhas_status ON campanhas(status);

-- ── 5. Pacotes gerados (GeneratedPackRecord) ──
CREATE TABLE IF NOT EXISTS generated_packs (
  id                  TEXT PRIMARY KEY,
  owner_user_id       TEXT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  date                DATE NOT NULL,
  week                INTEGER,
  year                INTEGER,
  profile_id          TEXT,
  profile_name        TEXT,
  briefing_focus      TEXT,
  themes              JSONB DEFAULT '[]'::jsonb,
  pack                JSONB DEFAULT '[]'::jsonb,
  briefing            JSONB,
  campanha_id         TEXT REFERENCES campanhas(id) ON DELETE SET NULL,
  ato_id              TEXT,
  etapa_funil         TEXT,                  -- tofu/mofu/bofu/pos
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_packs_owner_date ON generated_packs(owner_user_id, date DESC);
CREATE INDEX IF NOT EXISTS idx_packs_campanha ON generated_packs(campanha_id) WHERE campanha_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_packs_funil ON generated_packs(etapa_funil) WHERE etapa_funil IS NOT NULL;

-- ── 6. Sync log (audit trail por user_id) ──
CREATE TABLE IF NOT EXISTS sync_log (
  id              SERIAL PRIMARY KEY,
  user_id         TEXT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  direction       TEXT NOT NULL,            -- 'push' | 'pull'
  entities        TEXT[],                   -- ['operators', 'campanhas', ...]
  counts          JSONB,                    -- { operators: 3, campanhas: 1, ... }
  status          TEXT NOT NULL,            -- 'ok' | 'error'
  error_message   TEXT,
  duration_ms     INTEGER,
  client_ip       TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sync_log_user ON sync_log(user_id, created_at DESC);

-- ── 7. Trigger updated_at automático ──
CREATE OR REPLACE FUNCTION six_hype.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN
    SELECT unnest(ARRAY['app_users', 'operators', 'personagens', 'campanhas', 'generated_packs'])
  LOOP
    EXECUTE format(
      'DROP TRIGGER IF EXISTS trg_%I_updated ON six_hype.%I; '
      'CREATE TRIGGER trg_%I_updated BEFORE UPDATE ON six_hype.%I '
      'FOR EACH ROW EXECUTE FUNCTION six_hype.set_updated_at();',
      t, t, t, t
    );
  END LOOP;
END
$$;

-- ── 8. RLS prep (desligado por enquanto — service_role faz tudo) ──
-- Quando ligar multi-user com chave anon, descomentar:
--
-- ALTER TABLE operators       ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE personagens     ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE campanhas       ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE generated_packs ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE sync_log        ENABLE ROW LEVEL SECURITY;
--
-- CREATE POLICY p_owner ON operators       FOR ALL USING (owner_user_id = current_setting('request.jwt.claims', true)::jsonb->>'sub');
-- CREATE POLICY p_owner ON personagens     FOR ALL USING (owner_user_id = current_setting('request.jwt.claims', true)::jsonb->>'sub');
-- CREATE POLICY p_owner ON campanhas       FOR ALL USING (owner_user_id = current_setting('request.jwt.claims', true)::jsonb->>'sub');
-- CREATE POLICY p_owner ON generated_packs FOR ALL USING (owner_user_id = current_setting('request.jwt.claims', true)::jsonb->>'sub');
-- CREATE POLICY p_owner ON sync_log        FOR SELECT USING (user_id = current_setting('request.jwt.claims', true)::jsonb->>'sub');
