-- =============================================
-- ADMIN SCHEMA — PromptMotion AI
-- Run AFTER schema.sql
-- =============================================

-- 1. Tambah kolom `role` ke profiles
ALTER TABLE profiles
  ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'user'
    CHECK (role IN ('user', 'admin'));

-- Index untuk cepat filter per role
CREATE INDEX IF NOT EXISTS idx_profiles_role ON profiles(role);

-- 2. Helper function: cek apakah user saat ini adalah admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN
LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM profiles
    WHERE id = auth.uid() AND role = 'admin'
  );
$$;

-- 3. Tabel GLOBAL API KEYS — diset oleh admin, dipakai semua user
CREATE TABLE IF NOT EXISTS global_api_keys (
  id            UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  key_name      TEXT        NOT NULL UNIQUE,   -- e.g. OPENAI_API_KEY
  encrypted_value TEXT      NOT NULL,          -- AES-256-GCM app-level encrypted
  provider      TEXT        NOT NULL,          -- openai | anthropic | google
  is_active     BOOLEAN     NOT NULL DEFAULT TRUE,
  description   TEXT,
  last_used_at  TIMESTAMPTZ,
  created_by    UUID        REFERENCES profiles(id) ON DELETE SET NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Only admins can read/write global_api_keys
ALTER TABLE global_api_keys ENABLE ROW LEVEL SECURITY;

CREATE POLICY "admin_global_keys_select"
  ON global_api_keys FOR SELECT
  USING (is_admin());

CREATE POLICY "admin_global_keys_insert"
  ON global_api_keys FOR INSERT
  WITH CHECK (is_admin());

CREATE POLICY "admin_global_keys_update"
  ON global_api_keys FOR UPDATE
  USING (is_admin());

CREATE POLICY "admin_global_keys_delete"
  ON global_api_keys FOR DELETE
  USING (is_admin());

-- Auto timestamp
CREATE TRIGGER update_global_api_keys_updated_at
  BEFORE UPDATE ON global_api_keys
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Index
CREATE INDEX IF NOT EXISTS idx_global_api_keys_name ON global_api_keys(key_name);
CREATE INDEX IF NOT EXISTS idx_global_api_keys_provider ON global_api_keys(provider);

-- 4. Admin-only view of profiles (untuk /admin/users)
--    Admin bisa SELECT semua profiles, user hanya milik sendiri
DROP POLICY IF EXISTS "Users can view own profile" ON profiles;

CREATE POLICY "Users can view own profile"
  ON profiles FOR SELECT
  USING (auth.uid() = id OR is_admin());

DROP POLICY IF EXISTS "Users can update own profile" ON profiles;

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id OR is_admin());

-- 5. Admin bisa lihat semua prompt_history (untuk stats)
CREATE POLICY "Admin can view all history"
  ON prompt_history FOR SELECT
  USING (auth.uid() = user_id OR is_admin());

-- 6. Promote first user to admin (ganti email sesuai kebutuhan)
-- UPDATE profiles SET role = 'admin' WHERE email = 'admin@yourdomain.com';

-- 7. Fungsi upsert global api key (dipanggil dari API route)
CREATE OR REPLACE FUNCTION upsert_global_api_key(
  p_key_name      TEXT,
  p_encrypted_val TEXT,
  p_provider      TEXT,
  p_description   TEXT DEFAULT NULL
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER AS $$
DECLARE
  v_id UUID;
BEGIN
  -- Hanya admin boleh upsert
  IF NOT is_admin() THEN
    RAISE EXCEPTION 'Unauthorized: admin only';
  END IF;

  INSERT INTO global_api_keys (key_name, encrypted_value, provider, description, created_by)
  VALUES (p_key_name, p_encrypted_val, p_provider, p_description, auth.uid())
  ON CONFLICT (key_name)
  DO UPDATE SET
    encrypted_value = EXCLUDED.encrypted_value,
    provider        = EXCLUDED.provider,
    description     = EXCLUDED.description,
    is_active       = TRUE,
    updated_at      = NOW()
  RETURNING id INTO v_id;

  RETURN v_id;
END;
$$;

-- 8. View: admin_stats — aggregate per hari (untuk dashboard)
CREATE OR REPLACE VIEW admin_daily_stats AS
SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*)                       AS total_generations,
  COUNT(DISTINCT user_id)        AS unique_users,
  platform
FROM prompt_history
GROUP BY DATE_TRUNC('day', created_at), platform
ORDER BY day DESC;

-- Only admins can query this view
-- (handled via RLS on prompt_history which admin can fully access)
