-- ============================================================
-- ModelVN – Supabase Schema + RLS Policies
-- Chạy toàn bộ script này trong Supabase SQL Editor
-- ============================================================

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

-- ============================================================
-- TABLE: profiles (extends auth.users)
-- ============================================================
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  full_name TEXT,
  email TEXT,
  avatar TEXT,
  phone TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'user')),
  vip_level TEXT DEFAULT 'bronze' CHECK (vip_level IN ('bronze', 'silver', 'gold', 'platinum', 'diamond', 'legend')),
  vip_expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, email)
  VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NEW.email);
  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 public.handle_new_user();

-- ============================================================
-- TABLE: models
-- ============================================================
CREATE TABLE IF NOT EXISTS public.models (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  stage_name TEXT,
  avatar TEXT,
  gallery TEXT[] DEFAULT '{}',
  region TEXT NOT NULL CHECK (region IN ('mien_bac', 'mien_trung', 'mien_nam')),
  city TEXT,
  height NUMERIC,
  weight NUMERIC,
  measurements TEXT,
  booking_price NUMERIC NOT NULL DEFAULT 0,
  booking_price_day NUMERIC,
  categories TEXT[] DEFAULT '{}',
  experience TEXT,
  bio TEXT,
  is_featured BOOLEAN DEFAULT FALSE,
  is_verified BOOLEAN DEFAULT FALSE,
  rating NUMERIC DEFAULT 0,
  total_bookings INTEGER DEFAULT 0,
  status TEXT DEFAULT 'available' CHECK (status IN ('available', 'busy', 'inactive')),
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TABLE: bookings
-- ============================================================
CREATE TABLE IF NOT EXISTS public.bookings (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  model_id UUID REFERENCES public.models(id) ON DELETE SET NULL,
  model_name TEXT,
  event_type TEXT NOT NULL,
  event_date DATE NOT NULL,
  duration_hours NUMERIC,
  location TEXT,
  note TEXT,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'cancelled')),
  total_price NUMERIC,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TABLE: comments
-- ============================================================
CREATE TABLE IF NOT EXISTS public.comments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  model_id UUID REFERENCES public.models(id) ON DELETE CASCADE NOT NULL,
  content TEXT NOT NULL,
  rating NUMERIC CHECK (rating >= 1 AND rating <= 5),
  parent_id UUID REFERENCES public.comments(id) ON DELETE CASCADE,
  likes INTEGER DEFAULT 0,
  liked_by TEXT[] DEFAULT '{}',
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TABLE: site_settings
-- ============================================================
CREATE TABLE IF NOT EXISTS public.site_settings (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  key TEXT UNIQUE NOT NULL,
  value TEXT,
  group_name TEXT CHECK (group_name IN ('seo', 'general', 'contact', 'social')),
  label TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_models_region ON public.models(region);
CREATE INDEX IF NOT EXISTS idx_models_status ON public.models(status);
CREATE INDEX IF NOT EXISTS idx_models_is_featured ON public.models(is_featured);
CREATE INDEX IF NOT EXISTS idx_bookings_created_by ON public.bookings(created_by);
CREATE INDEX IF NOT EXISTS idx_bookings_model_id ON public.bookings(model_id);
CREATE INDEX IF NOT EXISTS idx_bookings_status ON public.bookings(status);
CREATE INDEX IF NOT EXISTS idx_comments_model_id ON public.comments(model_id);
CREATE INDEX IF NOT EXISTS idx_site_settings_key ON public.site_settings(key);

-- ============================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.models ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.site_settings ENABLE ROW LEVEL SECURITY;

-- Helper: check if current user is admin
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid() AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- ============================================================
-- RLS: profiles
-- ============================================================
-- Mọi người có thể xem profile công khai
CREATE POLICY "profiles_select_all" ON public.profiles
  FOR SELECT USING (true);

-- Chỉ chủ sở hữu hoặc admin mới được update
CREATE POLICY "profiles_update_own" ON public.profiles
  FOR UPDATE USING (auth.uid() = id OR public.is_admin());

-- Chỉ admin mới được xóa
CREATE POLICY "profiles_delete_admin" ON public.profiles
  FOR DELETE USING (public.is_admin());

-- ============================================================
-- RLS: models
-- ============================================================
-- Mọi người (kể cả anonymous) có thể xem model available
CREATE POLICY "models_select_public" ON public.models
  FOR SELECT USING (status != 'inactive' OR public.is_admin());

-- Chỉ admin mới được thêm model
CREATE POLICY "models_insert_admin" ON public.models
  FOR INSERT WITH CHECK (public.is_admin());

-- Chỉ admin mới được sửa model
CREATE POLICY "models_update_admin" ON public.models
  FOR UPDATE USING (public.is_admin());

-- Chỉ admin mới được xóa model
CREATE POLICY "models_delete_admin" ON public.models
  FOR DELETE USING (public.is_admin());

-- ============================================================
-- RLS: bookings
-- ============================================================
-- User chỉ xem booking của mình, admin xem tất cả
CREATE POLICY "bookings_select" ON public.bookings
  FOR SELECT USING (auth.uid() = created_by OR public.is_admin());

-- User đã đăng nhập mới được tạo booking
CREATE POLICY "bookings_insert_auth" ON public.bookings
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = created_by);

-- Chỉ admin mới được cập nhật trạng thái booking
CREATE POLICY "bookings_update_admin" ON public.bookings
  FOR UPDATE USING (public.is_admin());

-- Chỉ admin hoặc chủ booking mới được hủy
CREATE POLICY "bookings_delete" ON public.bookings
  FOR DELETE USING (auth.uid() = created_by OR public.is_admin());

-- ============================================================
-- RLS: comments
-- ============================================================
-- Mọi người đều xem được comment
CREATE POLICY "comments_select_all" ON public.comments
  FOR SELECT USING (true);

-- User đã đăng nhập mới được bình luận
CREATE POLICY "comments_insert_auth" ON public.comments
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = created_by);

-- Chỉ chủ comment hoặc admin mới được sửa
CREATE POLICY "comments_update_own" ON public.comments
  FOR UPDATE USING (auth.uid() = created_by OR public.is_admin());

-- Chỉ chủ comment hoặc admin mới được xóa
CREATE POLICY "comments_delete_own" ON public.comments
  FOR DELETE USING (auth.uid() = created_by OR public.is_admin());

-- ============================================================
-- RLS: site_settings
-- ============================================================
-- Mọi người xem được settings (cần cho SEO meta tags)
CREATE POLICY "settings_select_all" ON public.site_settings
  FOR SELECT USING (true);

-- Chỉ admin mới được thêm/sửa/xóa settings
CREATE POLICY "settings_insert_admin" ON public.site_settings
  FOR INSERT WITH CHECK (public.is_admin());

CREATE POLICY "settings_update_admin" ON public.site_settings
  FOR UPDATE USING (public.is_admin());

CREATE POLICY "settings_delete_admin" ON public.site_settings
  FOR DELETE USING (public.is_admin());

-- ============================================================
-- AUTO-UPDATE updated_at trigger
-- ============================================================
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_models_updated_at BEFORE UPDATE ON public.models FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER set_bookings_updated_at BEFORE UPDATE ON public.bookings FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER set_comments_updated_at BEFORE UPDATE ON public.comments FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER set_settings_updated_at BEFORE UPDATE ON public.site_settings FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER set_profiles_updated_at BEFORE UPDATE ON public.profiles FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ============================================================
-- Grant quyền cho anon và authenticated roles
-- ============================================================
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON public.models TO anon;
GRANT SELECT ON public.comments TO anon;
GRANT SELECT ON public.site_settings TO anon;
GRANT ALL ON public.profiles TO authenticated;
GRANT ALL ON public.models TO authenticated;
GRANT ALL ON public.bookings TO authenticated;
GRANT ALL ON public.comments TO authenticated;
GRANT ALL ON public.site_settings TO authenticated;

-- ============================================================
-- DONE! Chạy xong là Supabase đã sẵn sàng cho ModelVN
-- ============================================================