-- Diffusion automatique des événements vers Instagram, Facebook et Wyylde.

ALTER TABLE public.events
  ADD COLUMN publish_instagram BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN publish_facebook BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN publish_wyylde BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN social_caption TEXT;

CREATE TABLE public.event_publications (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  channel TEXT NOT NULL CHECK (channel IN ('instagram', 'facebook', 'wyylde')),
  status TEXT NOT NULL CHECK (status IN ('published', 'manual', 'error', 'skipped')),
  external_id TEXT,
  external_url TEXT,
  error TEXT,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (event_id, channel)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.event_publications TO authenticated;
GRANT ALL ON public.event_publications TO service_role;

ALTER TABLE public.event_publications ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated can view publications" ON public.event_publications FOR SELECT TO authenticated USING (true);
CREATE POLICY "Authenticated can create publications" ON public.event_publications FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Authenticated can update publications" ON public.event_publications FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated can delete publications" ON public.event_publications FOR DELETE TO authenticated USING (true);

CREATE TRIGGER update_event_publications_updated_at
  BEFORE UPDATE ON public.event_publications
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();
