
CREATE TABLE public.app_update_banners (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  version text NOT NULL UNIQUE,
  title text NOT NULL DEFAULT '',
  title_bn text NOT NULL DEFAULT '',
  message text NOT NULL DEFAULT '',
  message_bn text NOT NULL DEFAULT '',
  bg_color text NOT NULL DEFAULT '#0ea5e9',
  text_color text NOT NULL DEFAULT '#ffffff',
  image_url text,
  link_url text,
  link_label text,
  link_label_bn text,
  auto_hide_seconds integer,
  force_update boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.app_update_banners ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can view active update banners"
ON public.app_update_banners FOR SELECT
USING (is_active = true);

CREATE POLICY "Admins can view all update banners"
ON public.app_update_banners FOR SELECT
TO authenticated
USING (public.has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can insert update banners"
ON public.app_update_banners FOR INSERT
TO authenticated
WITH CHECK (public.has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can update update banners"
ON public.app_update_banners FOR UPDATE
TO authenticated
USING (public.has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete update banners"
ON public.app_update_banners FOR DELETE
TO authenticated
USING (public.has_role(auth.uid(), 'admin'::app_role));

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

CREATE INDEX idx_app_update_banners_active_created
ON public.app_update_banners (is_active, created_at DESC);
