-- =============================================================
-- MIGRATION: Fix 3 — Variasi History + Granular Usage + Token Tracking
-- Run AFTER schema.sql di Supabase SQL Editor
-- =============================================================

-- ─────────────────────────────────────────
-- Fix A: Simpan semua variasi di history
-- ─────────────────────────────────────────
-- Tambah kolom all_variations ke prompt_history
-- untuk menyimpan array semua variasi (1-5) sebagai JSONB
ALTER TABLE prompt_history
  ADD COLUMN IF NOT EXISTS all_variations JSONB;

COMMENT ON COLUMN prompt_history.all_variations IS
  'Array JSONB semua variasi yang di-generate (index 0-4). '
  'Kolom output_text/output_json tetap ada untuk backward-compatibility (berisi variasi[0]).';


-- ─────────────────────────────────────────
-- Fix B: Granular usage count via RPC
-- ─────────────────────────────────────────
-- Update RPC agar menerima p_amount (default 1)
-- sehingga generate 3 variasi = kurangi 3 kuota
CREATE OR REPLACE FUNCTION increment_usage_count(p_user_id uuid, p_amount integer DEFAULT 1)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  UPDATE profiles
  SET
    usage_count = usage_count + p_amount,
    updated_at  = now()
  WHERE id = p_user_id;
$$;

GRANT EXECUTE ON FUNCTION increment_usage_count(uuid, integer) TO authenticated;


-- ─────────────────────────────────────────
-- Fix C: Token & cost tracking di usage_logs
-- ─────────────────────────────────────────
-- Tambah kolom token usage dan estimasi biaya
ALTER TABLE usage_logs
  ADD COLUMN IF NOT EXISTS prompt_tokens      INTEGER,
  ADD COLUMN IF NOT EXISTS completion_tokens  INTEGER,
  ADD COLUMN IF NOT EXISTS total_tokens       INTEGER,
  ADD COLUMN IF NOT EXISTS estimated_cost_usd NUMERIC(10, 6);

COMMENT ON COLUMN usage_logs.prompt_tokens      IS 'Token input yang dikirim ke AI provider';
COMMENT ON COLUMN usage_logs.completion_tokens  IS 'Token output yang dikembalikan AI provider';
COMMENT ON COLUMN usage_logs.total_tokens       IS 'Total token (prompt + completion)';
COMMENT ON COLUMN usage_logs.estimated_cost_usd IS 'Estimasi biaya dalam USD berdasarkan model pricing';

-- Index untuk query cost dashboard admin
CREATE INDEX IF NOT EXISTS idx_usage_logs_user_created
  ON usage_logs(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_usage_logs_action
  ON usage_logs(action, created_at DESC);
