ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS avatar_url text,
  ADD COLUMN IF NOT EXISTS membership_category text NOT NULL DEFAULT 'marketplace',
  ADD COLUMN IF NOT EXISTS member_reference text,
  ADD COLUMN IF NOT EXISTS plan_tier text NOT NULL DEFAULT 'free',
  ADD COLUMN IF NOT EXISTS plan_activated_at timestamptz,
  ADD COLUMN IF NOT EXISTS team_id uuid;

UPDATE public.courses SET price_kes = 0 WHERE credential_level = 'Foundation';

CREATE TABLE IF NOT EXISTS public.plan_upgrade_requests (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid NOT NULL,
  membership_category text NOT NULL,
  plan text NOT NULL,
  amount_kes integer NOT NULL DEFAULT 0,
  payment_method text NOT NULL,
  payment_reference text NOT NULL,
  contact_email text,
  contact_phone text,
  notes text,
  status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.plan_upgrade_requests TO authenticated;
GRANT ALL ON public.plan_upgrade_requests TO service_role;
ALTER TABLE public.plan_upgrade_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users create own upgrade requests" ON public.plan_upgrade_requests
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users read own upgrade requests" ON public.plan_upgrade_requests
  FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE TRIGGER plan_upgrade_requests_updated_at
  BEFORE UPDATE ON public.plan_upgrade_requests
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE TABLE IF NOT EXISTS public.teams (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  owner_id uuid NOT NULL,
  name text NOT NULL,
  seat_limit integer NOT NULL DEFAULT 25,
  plan_tier text NOT NULL DEFAULT 'team',
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.teams TO authenticated;
GRANT ALL ON public.teams TO service_role;
ALTER TABLE public.teams ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Owners manage own team" ON public.teams
  FOR ALL TO authenticated USING (auth.uid() = owner_id) WITH CHECK (auth.uid() = owner_id);

CREATE TABLE IF NOT EXISTS public.team_members (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  team_id uuid NOT NULL REFERENCES public.teams(id) ON DELETE CASCADE,
  user_id uuid,
  invite_email text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (team_id, invite_email)
);
GRANT SELECT, INSERT, DELETE ON public.team_members TO authenticated;
GRANT ALL ON public.team_members TO service_role;
ALTER TABLE public.team_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team owners manage seats" ON public.team_members
  FOR ALL TO authenticated
  USING (EXISTS (SELECT 1 FROM public.teams t WHERE t.id = team_id AND t.owner_id = auth.uid()))
  WITH CHECK (EXISTS (SELECT 1 FROM public.teams t WHERE t.id = team_id AND t.owner_id = auth.uid()));
CREATE POLICY "Members read own seat" ON public.team_members
  FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE POLICY "Users read own avatar" ON storage.objects
  FOR SELECT TO authenticated
  USING (bucket_id = 'avatars' AND (storage.foldername(name))[1] = auth.uid()::text);
CREATE POLICY "Users upload own avatar" ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'avatars' AND (storage.foldername(name))[1] = auth.uid()::text);
CREATE POLICY "Users update own avatar" ON storage.objects
  FOR UPDATE TO authenticated
  USING (bucket_id = 'avatars' AND (storage.foldername(name))[1] = auth.uid()::text);
CREATE POLICY "Users delete own avatar" ON storage.objects
  FOR DELETE TO authenticated
  USING (bucket_id = 'avatars' AND (storage.foldername(name))[1] = auth.uid()::text);