{"-- Update subscription_tier enum to include AI-Premium
ALTER TYPE subscription_tier ADD VALUE IF NOT EXISTS 'ai-premium'","-- Create subscribers table for Stripe integration and billing
CREATE TABLE IF NOT EXISTS public.subscribers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE,
  stripe_customer_id TEXT,
  subscribed BOOLEAN NOT NULL DEFAULT false,
  subscription_tier subscription_tier DEFAULT 'basic',
  subscription_end TIMESTAMPTZ,
  usage_analytics JSONB DEFAULT '{}',
  billing_cycle TEXT DEFAULT 'monthly',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)","-- Enable RLS on subscribers table
ALTER TABLE public.subscribers ENABLE ROW LEVEL SECURITY","-- Create policies for subscribers
CREATE POLICY \"Users can view their own subscription\" ON public.subscribers
FOR SELECT USING (user_id = auth.uid())","CREATE POLICY \"Users can update their own subscription\" ON public.subscribers
FOR UPDATE USING (user_id = auth.uid())","CREATE POLICY \"Service role can manage all subscriptions\" ON public.subscribers
FOR ALL USING (true)","-- Create user preferences table for multi-language support
CREATE TABLE IF NOT EXISTS public.user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
  language TEXT DEFAULT 'en' CHECK (language IN ('en', 'ar', 'uk', 'vi')),
  timezone TEXT DEFAULT 'UTC',
  theme TEXT DEFAULT 'system',
  notifications JSONB DEFAULT '{\"email\": true, \"push\": true, \"sms\": false}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)","-- Enable RLS on user_preferences
ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY","-- Create policies for user_preferences
CREATE POLICY \"Users can manage their own preferences\" ON public.user_preferences
FOR ALL USING (user_id = auth.uid())","-- Update organizations table with subscription limits
ALTER TABLE public.organizations 
ADD COLUMN IF NOT EXISTS subscription_limits JSONB DEFAULT '{
  \"cameras\": 5,
  \"storage_gb\": 10,
  \"ai_detections_per_month\": 1000,
  \"users\": 3,
  \"branches\": 1
}'","-- Create trigger to update updated_at for subscribers
CREATE TRIGGER update_subscribers_updated_at
BEFORE UPDATE ON public.subscribers
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()","-- Create trigger to update updated_at for user_preferences
CREATE TRIGGER update_user_preferences_updated_at
BEFORE UPDATE ON public.user_preferences
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()","-- Update handle_new_user function to create user preferences
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
  new_org_id UUID;
BEGIN
  -- Create the organization first
  INSERT INTO public.organizations (name, subscription_tier)
  VALUES (
    COALESCE(new.raw_user_meta_data->>'organization_name', 'Default Organization'),
    'basic'
  )
  RETURNING id INTO new_org_id;
  
  -- Create default branch for the organization
  INSERT INTO public.branches (organization_id, name, address)
  VALUES (new_org_id, 'Headquarters', 'Main Location');
  
  -- Add user role as admin for the organization
  INSERT INTO public.user_roles (user_id, organization_id, role)
  VALUES (new.id, new_org_id, 'admin');
  
  -- Create user preferences with default language
  INSERT INTO public.user_preferences (user_id, language)
  VALUES (new.id, COALESCE(new.raw_user_meta_data->>'language', 'en'));
  
  -- Create subscriber record
  INSERT INTO public.subscribers (user_id, email, subscription_tier)
  VALUES (new.id, new.email, 'basic');
  
  RETURN new;
END;
$function$"}