-- ============ PODCAST ============
CREATE TABLE public.podcast_schedule (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  topic TEXT NOT NULL DEFAULT '',
  starts_at TIMESTAMPTZ NOT NULL,
  duration_minutes INT NOT NULL DEFAULT 60,
  status TEXT NOT NULL DEFAULT 'scheduled',
  stream_url TEXT,
  published BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.podcast_schedule TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.podcast_schedule TO authenticated;
GRANT ALL ON public.podcast_schedule TO service_role;
ALTER TABLE public.podcast_schedule ENABLE ROW LEVEL SECURITY;
CREATE POLICY "schedule public read" ON public.podcast_schedule FOR SELECT TO anon, authenticated USING (published);
CREATE POLICY "schedule admin all" ON public.podcast_schedule FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.podcast_episodes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  summary TEXT NOT NULL DEFAULT '',
  show_notes TEXT NOT NULL DEFAULT '',
  video_url TEXT,
  audio_url TEXT,
  cover_image_url TEXT,
  duration_minutes INT NOT NULL DEFAULT 45,
  published BOOLEAN NOT NULL DEFAULT true,
  published_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.podcast_episodes TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.podcast_episodes TO authenticated;
GRANT ALL ON public.podcast_episodes TO service_role;
ALTER TABLE public.podcast_episodes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "episodes public read" ON public.podcast_episodes FOR SELECT TO anon, authenticated USING (published);
CREATE POLICY "episodes admin read" ON public.podcast_episodes FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "episodes admin write" ON public.podcast_episodes FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.podcast_guests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  role_title TEXT NOT NULL DEFAULT '',
  bio TEXT NOT NULL DEFAULT '',
  avatar_url TEXT,
  link_url TEXT,
  episode_id UUID REFERENCES public.podcast_episodes(id) ON DELETE SET NULL,
  published BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.podcast_guests TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.podcast_guests TO authenticated;
GRANT ALL ON public.podcast_guests TO service_role;
ALTER TABLE public.podcast_guests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "guests public read" ON public.podcast_guests FOR SELECT TO anon, authenticated USING (published);
CREATE POLICY "guests admin all" ON public.podcast_guests FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.podcast_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  episode_id UUID REFERENCES public.podcast_episodes(id) ON DELETE SET NULL,
  display_name TEXT NOT NULL,
  question TEXT NOT NULL,
  answer TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.podcast_questions TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.podcast_questions TO authenticated;
GRANT ALL ON public.podcast_questions TO service_role;
ALTER TABLE public.podcast_questions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "questions approved read" ON public.podcast_questions FOR SELECT TO anon, authenticated USING (status IN ('approved','answered'));
CREATE POLICY "questions public insert" ON public.podcast_questions FOR INSERT TO anon, authenticated WITH CHECK (status = 'pending' AND length(question) BETWEEN 5 AND 600 AND length(display_name) BETWEEN 2 AND 60);
CREATE POLICY "questions admin all" ON public.podcast_questions FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.podcast_subscribers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  browser_push BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT ALL ON public.podcast_subscribers TO service_role;
ALTER TABLE public.podcast_subscribers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "subscribers admin read" ON public.podcast_subscribers FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
GRANT SELECT ON public.podcast_subscribers TO authenticated;

-- ============ STUDIO / E-LEARNING ============
CREATE TABLE public.studio_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  kind TEXT NOT NULL,
  title TEXT NOT NULL,
  details TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'queued',
  output_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.studio_requests TO authenticated;
GRANT ALL ON public.studio_requests TO service_role;
ALTER TABLE public.studio_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "own studio requests" ON public.studio_requests FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "admin studio requests" ON public.studio_requests FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.studio_generations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  kind TEXT NOT NULL,
  prompt TEXT NOT NULL,
  output TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, DELETE ON public.studio_generations TO authenticated;
GRANT ALL ON public.studio_generations TO service_role;
ALTER TABLE public.studio_generations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "own generations" ON public.studio_generations FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE TABLE public.courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  summary TEXT NOT NULL DEFAULT '',
  category TEXT NOT NULL DEFAULT 'general',
  level TEXT NOT NULL DEFAULT 'Beginner',
  duration_hours INT NOT NULL DEFAULT 6,
  published BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.courses TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.courses TO authenticated;
GRANT ALL ON public.courses TO service_role;
ALTER TABLE public.courses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "courses public read" ON public.courses FOR SELECT TO anon, authenticated USING (published);
CREATE POLICY "courses admin all" ON public.courses FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.lessons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL DEFAULT '',
  position INT NOT NULL DEFAULT 1,
  duration_minutes INT NOT NULL DEFAULT 20
);
GRANT SELECT ON public.lessons TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.lessons TO authenticated;
GRANT ALL ON public.lessons TO service_role;
ALTER TABLE public.lessons ENABLE ROW LEVEL SECURITY;
CREATE POLICY "lessons public read" ON public.lessons FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "lessons admin all" ON public.lessons FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.course_enrollments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  completed_lessons JSONB NOT NULL DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.course_enrollments TO authenticated;
GRANT ALL ON public.course_enrollments TO service_role;
ALTER TABLE public.course_enrollments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "own enrollments" ON public.course_enrollments FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- ============ SEED ============
INSERT INTO public.podcast_episodes (slug, title, summary, show_notes, video_url, duration_minutes, published_at) VALUES
('building-ai-in-kampala','Building AI in Kampala','How Gatavase ships production AI from East Africa — data, talent and infrastructure realities.','Topics: local data pipelines, GPU access, hiring engineers in Uganda, shipping LLM features for banks.','https://www.youtube.com/embed/aircAruvnKk',52,now() - interval '7 days'),
('fintech-rails-for-africa','Fintech Rails for Africa','Mobile money, settlement and fraud detection across Uganda, Kenya and the Gulf.','Topics: MoMo integrations, KYC automation, real-time fraud scoring, regulatory sandboxes.','https://www.youtube.com/embed/2ePf9rue1Ao',47,now() - interval '14 days'),
('founders-table-doha','Founders Table: Doha Edition','Scaling an African engineering company into Gulf enterprise markets.','Topics: enterprise procurement, pricing, building trust, remote delivery teams.','https://www.youtube.com/embed/kJQP7kiw5Fk',61,now() - interval '21 days'),
('creative-frequency-sound-of-uganda','Creative Frequency: The Sound of Uganda','Music, media and technology with Lil Gazman Music.','Topics: home studio production, AI mastering, distribution and rights.','https://www.youtube.com/embed/9bZkp7q19f0',44,now() - interval '28 days');

INSERT INTO public.podcast_guests (name, role_title, bio, link_url, episode_id)
SELECT 'Gazman', 'Founder, Gatavase Corporation', 'Engineer and founder building AI, fintech and creative technology across Kampala and Doha.', 'https://gatavase.com/about', id FROM public.podcast_episodes WHERE slug = 'building-ai-in-kampala';
INSERT INTO public.podcast_guests (name, role_title, bio, episode_id)
SELECT 'Aisha N.', 'Payments Lead', 'Fifteen years across mobile money and card settlement in East Africa.', id FROM public.podcast_episodes WHERE slug = 'fintech-rails-for-africa';

INSERT INTO public.podcast_schedule (title, topic, starts_at, duration_minutes, status) VALUES
('AI & Automation Lab','Build sessions and live Q&A with the Gatavase AI team', date_trunc('hour', now()) + interval '2 days', 60, 'scheduled'),
('Founders Table','African startups and scale-ups in conversation', date_trunc('hour', now()) + interval '4 days', 75, 'scheduled'),
('Creative Frequency','Music, media and storytelling from the Kampala studio', date_trunc('hour', now()) + interval '6 days', 60, 'scheduled');

INSERT INTO public.courses (slug, title, summary, category, level, duration_hours) VALUES
('ai-automation-foundations','AI Automation Foundations','Build practical AI workflows: prompting, retrieval, agents and evaluation.','AI','Beginner',8),
('mobile-app-development','Mobile App Development with Flutter','Ship cross-platform apps from setup to store release.','Apps','Intermediate',14),
('web-platform-engineering','Web Platform Engineering','Modern full-stack web: React, APIs, databases and deployment.','Web','Intermediate',16),
('computer-repair-essentials','Computer Repair Essentials','Hardware diagnostics, board-level repair and virus removal.','Hardware','Beginner',10),
('creative-media-production','Creative Media Production','Graphics, audio and video production with AI-assisted tooling.','Creative','Beginner',9);

INSERT INTO public.lessons (course_id, title, content, position, duration_minutes)
SELECT c.id, l.title, l.content, l.position, l.duration
FROM public.courses c
JOIN (VALUES
  ('ai-automation-foundations','Why automation matters','Understand where AI creates measurable leverage in real organisations.',1,20),
  ('ai-automation-foundations','Prompting that works','Structure, constraints, examples and evaluation of prompts.',2,25),
  ('ai-automation-foundations','Retrieval and knowledge bases','Ground answers in your own documents.',3,30),
  ('ai-automation-foundations','Shipping your first agent','Wire tools, guardrails and monitoring.',4,35),
  ('mobile-app-development','Environment setup','Install the toolchain and run your first build.',1,20),
  ('mobile-app-development','UI fundamentals','Layouts, widgets and navigation.',2,30),
  ('mobile-app-development','Data and APIs','State management and talking to a backend.',3,35),
  ('mobile-app-development','Publishing','Signing, store listings and releases.',4,25),
  ('web-platform-engineering','Front-end foundations','Components, routing and design systems.',1,30),
  ('web-platform-engineering','APIs and databases','Modelling data and building endpoints.',2,35),
  ('web-platform-engineering','Auth and security','Sessions, roles and row-level security.',3,30),
  ('web-platform-engineering','Deploy and observe','CI/CD, logging and performance.',4,25),
  ('computer-repair-essentials','Diagnostics','Isolate faults quickly and safely.',1,25),
  ('computer-repair-essentials','Board-level repair','Tools, soldering and component testing.',2,35),
  ('computer-repair-essentials','Virus removal and recovery','Clean, restore and harden a machine.',3,25),
  ('creative-media-production','Graphics workflow','Brand systems and asset production.',1,25),
  ('creative-media-production','Audio production','Recording, mixing and AI mastering.',2,30),
  ('creative-media-production','Video and motion','Editing, colour and delivery.',3,30)
) AS l(slug,title,content,position,duration) ON l.slug = c.slug;