-- ================================================
-- SiteGenie — Database Schema
-- تشغّل هذا في Supabase SQL Editor
-- ================================================

-- جدول الخطط والمستخدمين (يمتد على auth.users)
CREATE TABLE public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  plan TEXT NOT NULL DEFAULT 'free', -- 'free' | 'pro' | 'agency'
  stripe_customer_id TEXT UNIQUE,
  stripe_subscription_id TEXT,
  subscription_status TEXT DEFAULT 'inactive', -- 'active' | 'inactive' | 'canceled'
  sites_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- جدول المواقع
CREATE TABLE public.sites (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  slug TEXT UNIQUE NOT NULL, -- yoursite.sitegenie.io
  custom_domain TEXT UNIQUE,
  custom_domain_verified BOOLEAN DEFAULT FALSE,
  status TEXT DEFAULT 'draft', -- 'draft' | 'published'
  style TEXT DEFAULT 'modern',
  language TEXT DEFAULT 'arabic',
  -- SEO
  seo_title TEXT,
  seo_description TEXT,
  google_analytics_id TEXT,
  -- Features
  whatsapp_number TEXT,
  google_maps_embed TEXT,
  -- Meta
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- جدول الصفحات (كل موقع يمكن أن يحتوي على عدة صفحات)
CREATE TABLE public.pages (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  site_id UUID REFERENCES public.sites(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  slug TEXT NOT NULL, -- 'home' | 'about' | 'services' | 'contact'
  html_content TEXT, -- الكود الكامل للصفحة
  is_home BOOLEAN DEFAULT FALSE,
  order_index INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(site_id, slug)
);

-- جدول محادثات التعديل (chat history لكل صفحة)
CREATE TABLE public.chat_messages (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  page_id UUID REFERENCES public.pages(id) ON DELETE CASCADE NOT NULL,
  role TEXT NOT NULL, -- 'user' | 'assistant'
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- جدول نقل المواقع للعملاء
CREATE TABLE public.site_transfers (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  site_id UUID REFERENCES public.sites(id) ON DELETE CASCADE NOT NULL,
  from_user_id UUID REFERENCES public.profiles(id) NOT NULL,
  to_email TEXT NOT NULL,
  to_user_id UUID REFERENCES public.profiles(id),
  status TEXT DEFAULT 'pending', -- 'pending' | 'accepted' | 'rejected'
  token TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '7 days'
);

-- ================================================
-- RLS Policies (أمان — كل مستخدم يرى بياناته فقط)
-- ================================================

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sites ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.pages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.chat_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.site_transfers ENABLE ROW LEVEL SECURITY;

-- Profiles
CREATE POLICY "users_own_profile" ON public.profiles
  FOR ALL USING (auth.uid() = id);

-- Sites
CREATE POLICY "users_own_sites" ON public.sites
  FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "public_published_sites" ON public.sites
  FOR SELECT USING (status = 'published');

-- Pages
CREATE POLICY "users_own_pages" ON public.pages
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.sites WHERE id = pages.site_id AND user_id = auth.uid())
  );

CREATE POLICY "public_published_pages" ON public.pages
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.sites WHERE id = pages.site_id AND status = 'published')
  );

-- Chat Messages
CREATE POLICY "users_own_messages" ON public.chat_messages
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.pages p
      JOIN public.sites s ON s.id = p.site_id
      WHERE p.id = chat_messages.page_id AND s.user_id = auth.uid()
    )
  );

-- Site Transfers
CREATE POLICY "transfer_access" ON public.site_transfers
  FOR ALL USING (
    from_user_id = auth.uid() OR to_user_id = auth.uid()
  );

-- ================================================
-- Functions & Triggers
-- ================================================

-- إنشاء profile تلقائياً عند التسجيل
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.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 TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- تحديث updated_at تلقائياً
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_sites_updated_at BEFORE UPDATE ON public.sites
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER update_pages_updated_at BEFORE UPDATE ON public.pages
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

CREATE TRIGGER update_profiles_updated_at BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();