-- =============================================
-- UGC HISTORY TABLE
-- Run AFTER schema.sql
-- =============================================

CREATE TABLE IF NOT EXISTS ugc_history (
  id              UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID        NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  product_name    TEXT        NOT NULL,
  product_url     TEXT,
  platform        TEXT        NOT NULL,
  durasi          TEXT        NOT NULL,
  gaya_bahasa     TEXT        NOT NULL DEFAULT 'santai_gaul',
  presenter       TEXT        NOT NULL DEFAULT 'wanita',
  hook_style      TEXT        NOT NULL DEFAULT 'masalah_solusi',
  language        TEXT        NOT NULL DEFAULT 'id'
                  CHECK (language IN ('id', 'en')),
  output          JSONB       NOT NULL,   -- full UGCPackage JSON
  is_favorite     BOOLEAN     NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- RLS
ALTER TABLE ugc_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY "ugc_history: select own"
  ON ugc_history FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "ugc_history: insert own"
  ON ugc_history FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "ugc_history: update own"
  ON ugc_history FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "ugc_history: delete own"
  ON ugc_history FOR DELETE USING (auth.uid() = user_id);

-- Admin can view all
CREATE POLICY "ugc_history: admin view all"
  ON ugc_history FOR SELECT USING (is_admin());

-- Indexes
CREATE INDEX IF NOT EXISTS idx_ugc_history_user_id    ON ugc_history(user_id);
CREATE INDEX IF NOT EXISTS idx_ugc_history_created_at ON ugc_history(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ugc_history_platform   ON ugc_history(platform);
CREATE INDEX IF NOT EXISTS idx_ugc_history_favorite   ON ugc_history(is_favorite) WHERE is_favorite = TRUE;
