-- Create courses table
CREATE TABLE IF NOT EXISTS public.courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL,
  modality TEXT NOT NULL,
  duration TEXT NOT NULL,
  shift TEXT NOT NULL,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create hero slides table
CREATE TABLE IF NOT EXISTS public.hero_slides (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  subtitle TEXT NOT NULL,
  button_text TEXT NOT NULL,
  button_link TEXT NOT NULL,
  background_image TEXT,
  order_index INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create site settings table (for contact info, social links, etc.)
CREATE TABLE IF NOT EXISTS public.site_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key TEXT UNIQUE NOT NULL,
  value TEXT NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE public.courses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.hero_slides ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.site_settings ENABLE ROW LEVEL SECURITY;

-- Create policies for public read access
CREATE POLICY "Allow public to view courses" ON public.courses FOR SELECT USING (true);
CREATE POLICY "Allow public to view active hero slides" ON public.hero_slides FOR SELECT USING (is_active = true);
CREATE POLICY "Allow public to view site settings" ON public.site_settings FOR SELECT USING (true);

-- Create policies for admin write access (checking is_admin from user metadata)
CREATE POLICY "Allow admins to insert courses" ON public.courses FOR INSERT 
  WITH CHECK (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to update courses" ON public.courses FOR UPDATE 
  USING (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to delete courses" ON public.courses FOR DELETE 
  USING (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to insert hero slides" ON public.hero_slides FOR INSERT 
  WITH CHECK (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to update hero slides" ON public.hero_slides FOR UPDATE 
  USING (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to delete hero slides" ON public.hero_slides FOR DELETE 
  USING (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to insert settings" ON public.site_settings FOR INSERT 
  WITH CHECK (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

CREATE POLICY "Allow admins to update settings" ON public.site_settings FOR UPDATE 
  USING (
    (SELECT (auth.jwt()->>'user_metadata')::jsonb->>'is_admin')::boolean = true
  );

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_courses_category ON public.courses(category);
CREATE INDEX IF NOT EXISTS idx_courses_modality ON public.courses(modality);
CREATE INDEX IF NOT EXISTS idx_hero_slides_order ON public.hero_slides(order_index);
CREATE INDEX IF NOT EXISTS idx_site_settings_key ON public.site_settings(key);
