-- PromptMotion AI Database Schema
-- Run this in Supabase SQL Editor

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================
-- PROFILES
-- =====================
CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  plan TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
  usage_count INTEGER NOT NULL DEFAULT 0,
  usage_limit INTEGER NOT NULL DEFAULT 50,
  usage_reset_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '30 days'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =====================
-- PROMPT HISTORY
-- =====================
CREATE TABLE IF NOT EXISTS prompt_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  platform TEXT NOT NULL CHECK (platform IN ('seedance', 'gemini_omni', 'kling_motion', 'higgsfield', 'tiktok_ugc')),
  mode TEXT NOT NULL DEFAULT 'simple' CHECK (mode IN ('simple', 'advanced')),
  input_data JSONB NOT NULL,
  output_text TEXT,
  output_json JSONB,
  output_shot_list TEXT,
  output_script TEXT,
  variations INTEGER NOT NULL DEFAULT 1,
  is_favorite BOOLEAN NOT NULL DEFAULT FALSE,
  title TEXT,
  tags TEXT[],
  reference_image_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =====================
-- TEMPLATES
-- =====================
CREATE TABLE IF NOT EXISTS prompt_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  platform TEXT NOT NULL CHECK (platform IN ('seedance', 'gemini_omni', 'kling_motion', 'higgsfield', 'tiktok_ugc', 'all')),
  name TEXT NOT NULL,
  description TEXT,
  input_data JSONB NOT NULL,
  is_public BOOLEAN NOT NULL DEFAULT FALSE,
  is_official BOOLEAN NOT NULL DEFAULT FALSE,
  use_count INTEGER NOT NULL DEFAULT 0,
  tags TEXT[],
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =====================
-- PRODUCT LIBRARY
-- =====================
CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT,
  image_url TEXT,
  attributes JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =====================
-- USAGE LOGS
-- =====================
CREATE TABLE IF NOT EXISTS usage_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  action TEXT NOT NULL,
  platform TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =====================
-- ROW LEVEL SECURITY
-- =====================
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE prompt_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE prompt_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_logs ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Users can view own profile" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own profile" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Prompt history policies
CREATE POLICY "Users can view own history" ON prompt_history FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own history" ON prompt_history FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own history" ON prompt_history FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own history" ON prompt_history FOR DELETE USING (auth.uid() = user_id);

-- Templates policies
CREATE POLICY "Users can view own and public templates" ON prompt_templates FOR SELECT USING (auth.uid() = user_id OR is_public = TRUE);
CREATE POLICY "Users can insert own templates" ON prompt_templates FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own templates" ON prompt_templates FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own templates" ON prompt_templates FOR DELETE USING (auth.uid() = user_id);

-- Products policies
CREATE POLICY "Users can view own products" ON products FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own products" ON products FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own products" ON products FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own products" ON products FOR DELETE USING (auth.uid() = user_id);

-- Usage logs policies
CREATE POLICY "Users can view own usage logs" ON usage_logs FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own usage logs" ON usage_logs FOR INSERT WITH CHECK (auth.uid() = user_id);

-- =====================
-- FUNCTIONS & TRIGGERS
-- =====================

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profiles_updated_at BEFORE UPDATE ON profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_templates_updated_at BEFORE UPDATE ON prompt_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- =====================
-- INDEXES
-- =====================
CREATE INDEX idx_prompt_history_user_id ON prompt_history(user_id);
CREATE INDEX idx_prompt_history_platform ON prompt_history(platform);
CREATE INDEX idx_prompt_history_created_at ON prompt_history(created_at DESC);
CREATE INDEX idx_prompt_history_is_favorite ON prompt_history(is_favorite) WHERE is_favorite = TRUE;
CREATE INDEX idx_prompt_templates_platform ON prompt_templates(platform);
CREATE INDEX idx_prompt_templates_public ON prompt_templates(is_public) WHERE is_public = TRUE;
CREATE INDEX idx_products_user_id ON products(user_id);

-- =====================
-- STORAGE BUCKETS
-- =====================
-- Run these in Supabase Dashboard > Storage
-- INSERT INTO storage.buckets (id, name, public) VALUES ('reference-images', 'reference-images', false);
-- INSERT INTO storage.buckets (id, name, public) VALUES ('product-images', 'product-images', false);
-- INSERT INTO storage.buckets (id, name, public) VALUES ('avatars', 'avatars', true);

-- Storage policies (run after creating buckets)
-- CREATE POLICY "Users can upload reference images" ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'reference-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Users can view own reference images" ON storage.objects FOR SELECT USING (bucket_id = 'reference-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Users can delete own reference images" ON storage.objects FOR DELETE USING (bucket_id = 'reference-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- =====================
-- USER SETTINGS TABLE
-- (tambahkan setelah schema.sql)
-- =====================

CREATE TABLE IF NOT EXISTS user_settings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE UNIQUE,

  -- AI Provider keys (disimpan di server, tidak pernah dikirim ke frontend)
  openai_api_key TEXT,
  anthropic_api_key TEXT,
  google_ai_api_key TEXT,

  -- Supabase config (untuk multi-tenant / user bawa sendiri)
  supabase_url TEXT,
  supabase_anon_key TEXT,

  -- Preferences
  preferred_ai_provider TEXT NOT NULL DEFAULT 'openai'
    CHECK (preferred_ai_provider IN ('openai', 'anthropic', 'google', 'auto')),
  preferred_model TEXT,
  default_language TEXT NOT NULL DEFAULT 'id'
    CHECK (default_language IN ('id', 'en')),
  default_mode TEXT NOT NULL DEFAULT 'simple'
    CHECK (default_mode IN ('simple', 'advanced')),
  default_variations INTEGER NOT NULL DEFAULT 1
    CHECK (default_variations IN (1, 3, 5)),

  -- Notification preferences
  email_notifications BOOLEAN NOT NULL DEFAULT TRUE,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- RLS
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own settings"
  ON user_settings FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own settings"
  ON user_settings FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own settings"
  ON user_settings FOR UPDATE USING (auth.uid() = user_id);

-- Auto update timestamp
CREATE TRIGGER update_user_settings_updated_at
  BEFORE UPDATE ON user_settings
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Auto-create empty settings on profile creation
CREATE OR REPLACE FUNCTION handle_new_user_settings()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_settings (user_id)
  VALUES (NEW.id)
  ON CONFLICT (user_id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_profile_created_create_settings
  AFTER INSERT ON profiles
  FOR EACH ROW EXECUTE FUNCTION handle_new_user_settings();

-- Index
CREATE INDEX idx_user_settings_user_id ON user_settings(user_id);
-- =============================================
-- 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.';
