-- ============================================================
-- PromptMotion AI — COMPLETE DATABASE SCHEMA
-- Run this single file in Supabase SQL Editor to set up everything.
--
-- Execution order (sudah digabung di file ini):
--   1. Core tables (profiles, history, templates, products, usage_logs)
--   2. User settings table
--   3. Encrypted env vars table
--   4. Admin role, global API keys, admin views
--   5. Atomic usage count RPC (granular per-variasi)
--   6. Migration Fix3: all_variations column + token/cost tracking
--   7. Migration AgentRouter: provider constraint update
-- ============================================================

-- ==========================================
-- SECTION 1: CORE SCHEMA
-- ==========================================

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

-- =====================
-- 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')),
  role            TEXT        NOT NULL DEFAULT 'user'  CHECK (role IN ('user', 'admin')),
  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()
);

CREATE INDEX IF NOT EXISTS idx_profiles_role ON profiles(role);

-- =====================
-- 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,
  -- Fix3-A: Simpan semua variasi (1-5) sebagai array JSONB
  -- Kolom output_text/output_json tetap ada untuk backward-compatibility (berisi variasi[0])
  all_variations      JSONB,
  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()
);

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]).';

-- =====================
-- 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
-- Fix3-C: Kolom token usage dan estimasi biaya langsung di tabel ini
-- =====================
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,
  prompt_tokens       INTEGER,
  completion_tokens   INTEGER,
  total_tokens        INTEGER,
  estimated_cost_usd  NUMERIC(10, 6),
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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';

-- =====================
-- ROW LEVEL SECURITY — CORE
-- =====================
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 (admin dapat lihat semua)
CREATE POLICY "Users can view own profile"
  ON profiles FOR SELECT USING (auth.uid() = id OR is_admin());
CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE USING (auth.uid() = id OR is_admin());

-- Prompt history policies
CREATE POLICY "Users can view own history"
  ON prompt_history FOR SELECT USING (auth.uid() = user_id OR is_admin());
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 — CORE
-- =====================

-- 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();

-- 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();

-- =====================
-- INDEXES — CORE
-- =====================
CREATE INDEX IF NOT EXISTS idx_prompt_history_user_id    ON prompt_history(user_id);
CREATE INDEX IF NOT EXISTS idx_prompt_history_platform   ON prompt_history(platform);
CREATE INDEX IF NOT EXISTS idx_prompt_history_created_at ON prompt_history(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_prompt_history_is_favorite ON prompt_history(is_favorite) WHERE is_favorite = TRUE;
CREATE INDEX IF NOT EXISTS idx_prompt_templates_platform ON prompt_templates(platform);
CREATE INDEX IF NOT EXISTS idx_prompt_templates_public   ON prompt_templates(is_public) WHERE is_public = TRUE;
CREATE INDEX IF NOT EXISTS idx_products_user_id          ON products(user_id);
-- Fix3-C: 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);


-- ==========================================
-- SECTION 2: USER SETTINGS
-- ==========================================

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,
  -- DEPRECATED: simpan API key di sini dalam plaintext sudah digantikan
  -- oleh tabel env_vars (AES-256-GCM). Kolom ini dipertahankan untuk
  -- backward-compatibility tapi JANGAN diisi untuk user baru.
  -- Gunakan /api/env-vars untuk menyimpan key secara aman.
  openai_api_key          TEXT,
  anthropic_api_key       TEXT,
  google_ai_api_key       TEXT,
  supabase_url            TEXT,
  supabase_anon_key       TEXT,
  -- Migration AgentRouter: preferred_ai_provider sekarang mendukung 'agentrouter'
  preferred_ai_provider   TEXT        NOT NULL DEFAULT 'openai'
    CHECK (preferred_ai_provider IN ('openai', 'anthropic', 'google', 'agentrouter', '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)),
  email_notifications     BOOLEAN     NOT NULL DEFAULT TRUE,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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);

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

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();

CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings(user_id);


-- ==========================================
-- SECTION 3: ENCRYPTED ENV VARS
-- ==========================================
-- Ini adalah cara YANG BENAR untuk menyimpan API key.
-- Semua value dienkripsi AES-256-GCM server-side sebelum disimpan.
-- Lihat src/lib/crypto.ts untuk implementasi enkripsi.

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             TEXT        NOT NULL,
  encrypted_value TEXT        NOT NULL,
  category        TEXT        NOT NULL DEFAULT 'ai_provider'
                  CHECK (category IN ('ai_provider', 'supabase', 'other')),
  description     TEXT,
  is_active       BOOLEAN     NOT NULL DEFAULT TRUE,
  last_used_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, key)
);

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

ALTER TABLE env_vars ENABLE ROW LEVEL SECURITY;

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);

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

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;
$$;

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;
$$;

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);


-- ==========================================
-- SECTION 4: ADMIN SCHEMA
-- ==========================================

-- is_admin() helper — dipakai di semua policy admin
-- Didefinisikan sebelum tabel yang bergantung padanya
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');
$$;

CREATE TABLE IF NOT EXISTS global_api_keys (
  id              UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  key_name        TEXT        NOT NULL UNIQUE,
  encrypted_value TEXT        NOT NULL,
  provider        TEXT        NOT NULL,
  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()
);

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());

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

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);

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
  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;
$$;

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;

-- Untuk promote user ke admin, jalankan:
-- UPDATE profiles SET role = 'admin' WHERE email = 'your-admin@email.com';


-- ==========================================
-- SECTION 5: ATOMIC USAGE RPC
-- Fix3-B: Menerima p_amount (default 1) agar 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;


-- ==========================================
-- SECTION 6: STORYBOARD HISTORY
-- Run AFTER schema-all core (table ini bergantung pada profiles)
-- ==========================================

CREATE TABLE IF NOT EXISTS storyboard_history (
  id              UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID        NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  source_url      TEXT        NOT NULL,
  source_platform TEXT        NOT NULL DEFAULT 'website',
  source_title    TEXT,
  -- 'custom' ditambahkan untuk mendukung length = "custom" dari StoryboardRequest
  style           TEXT        NOT NULL DEFAULT 'cinematic'
                  CHECK (style IN ('cinematic','documentary','social_media','ugc','explainer','emotional','humorous')),
  length          TEXT        NOT NULL DEFAULT 'medium'
                  CHECK (length IN ('short','medium','long','custom')),
  language        TEXT        NOT NULL DEFAULT 'id'
                  CHECK (language IN ('id','en')),
  output          JSONB       NOT NULL,
  is_favorite     BOOLEAN     NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE storyboard_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY "storyboard: select own"
  ON storyboard_history FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "storyboard: insert own"
  ON storyboard_history FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "storyboard: update own"
  ON storyboard_history FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "storyboard: delete own"
  ON storyboard_history FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "storyboard: admin view all"
  ON storyboard_history FOR SELECT USING (is_admin());

CREATE INDEX IF NOT EXISTS idx_storyboard_user_id    ON storyboard_history(user_id);
CREATE INDEX IF NOT EXISTS idx_storyboard_created_at ON storyboard_history(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_storyboard_platform   ON storyboard_history(source_platform);
CREATE INDEX IF NOT EXISTS idx_storyboard_favorite   ON storyboard_history(is_favorite) WHERE is_favorite = TRUE;


-- ==========================================
-- SECTION 7: STORAGE BUCKETS
-- Jalankan di Supabase Dashboard > Storage, atau uncomment di bawah:
-- ==========================================
--
-- 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:
-- 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]);
-- CREATE POLICY "Users can upload product images" ON storage.objects FOR INSERT
--   WITH CHECK (bucket_id = 'product-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Users can view own product images" ON storage.objects FOR SELECT
--   USING (bucket_id = 'product-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Users can delete own product images" ON storage.objects FOR DELETE
--   USING (bucket_id = 'product-images' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Public avatar access" ON storage.objects FOR SELECT
--   USING (bucket_id = 'avatars');
-- CREATE POLICY "Users can upload own avatar" ON storage.objects FOR INSERT
--   WITH CHECK (bucket_id = 'avatars' AND auth.uid()::text = (storage.foldername(name))[1]);
