-- =============================================
-- ENV_VARS TABLE — Encrypted Key-Value Store
-- =============================================
-- Strategi enkripsi dual-layer:
--   Layer 1: pgcrypto (symmetric, di sisi DB)
--   Layer 2: AES-256-GCM (di sisi aplikasi, sebelum masuk DB)
-- Sehingga bahkan DBA yang punya akses DB tidak bisa baca raw value.
-- =============================================

-- Enable pgcrypto untuk layer DB encryption
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- =====================
-- TABEL ENV_VARS
-- =====================
CREATE TABLE IF NOT EXISTS env_vars (
  id            UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID        NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,

  -- Key identifier (tidak dienkripsi — cukup nama variabel)
  key           TEXT        NOT NULL,

  -- Value dienkripsi double-layer:
  --   app encrypts → pgcrypto encrypt → stored here
  encrypted_value TEXT      NOT NULL,

  -- Metadata
  category      TEXT        NOT NULL DEFAULT 'ai_provider'
                CHECK (category IN ('ai_provider', 'supabase', 'other')),
  description   TEXT,
  is_active     BOOLEAN     NOT NULL DEFAULT TRUE,

  -- Audit trail
  last_used_at  TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Setiap user hanya boleh punya satu value per key
  UNIQUE (user_id, key)
);

-- =====================
-- ROW LEVEL SECURITY
-- =====================
ALTER TABLE env_vars ENABLE ROW LEVEL SECURITY;

-- User hanya bisa lihat/ubah/hapus milik sendiri
CREATE POLICY "env_vars: select own"
  ON env_vars FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "env_vars: insert own"
  ON env_vars FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "env_vars: update own"
  ON env_vars FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "env_vars: delete own"
  ON env_vars FOR DELETE
  USING (auth.uid() = user_id);

-- =====================
-- AUTO-UPDATE TIMESTAMP
-- =====================
CREATE TRIGGER update_env_vars_updated_at
  BEFORE UPDATE ON env_vars
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- =====================
-- FUNCTION: Upsert env var
-- Dipanggil dari server-side API route saja
-- =====================
CREATE OR REPLACE FUNCTION upsert_env_var(
  p_user_id       UUID,
  p_key           TEXT,
  p_encrypted_val TEXT,
  p_category      TEXT DEFAULT 'ai_provider',
  p_description   TEXT DEFAULT NULL
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_id UUID;
BEGIN
  INSERT INTO env_vars (user_id, key, encrypted_value, category, description)
  VALUES (p_user_id, p_key, p_encrypted_val, p_category, p_description)
  ON CONFLICT (user_id, key)
  DO UPDATE SET
    encrypted_value = EXCLUDED.encrypted_value,
    category        = EXCLUDED.category,
    description     = EXCLUDED.description,
    is_active       = TRUE,
    updated_at      = NOW()
  RETURNING id INTO v_id;

  RETURN v_id;
END;
$$;

-- =====================
-- FUNCTION: Update last_used_at
-- =====================
CREATE OR REPLACE FUNCTION touch_env_var(p_id UUID)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  UPDATE env_vars
  SET last_used_at = NOW()
  WHERE id = p_id AND user_id = auth.uid();
END;
$$;

-- =====================
-- INDEX
-- =====================
CREATE INDEX IF NOT EXISTS idx_env_vars_user_id  ON env_vars(user_id);
CREATE INDEX IF NOT EXISTS idx_env_vars_user_key ON env_vars(user_id, key);
CREATE INDEX IF NOT EXISTS idx_env_vars_category ON env_vars(user_id, category);

-- =====================
-- SEED: Predefined key names (referensi UI)
-- =====================
COMMENT ON TABLE env_vars IS
  'Encrypted key-value store for user API keys and config. '
  'Values are AES-256-GCM encrypted by the app before storage.';

COMMENT ON COLUMN env_vars.key IS
  'Predefined keys: OPENAI_API_KEY, ANTHROPIC_API_KEY, GOOGLE_AI_API_KEY, '
  'SUPABASE_URL, SUPABASE_ANON_KEY, PREFERRED_AI_PROVIDER, PREFERRED_MODEL';

COMMENT ON COLUMN env_vars.encrypted_value IS
  'AES-256-GCM encrypted (app-level) then base64-encoded. '
  'Never decrypt on frontend — server-side only.';
