CREATE TABLE public.notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  target_user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text NOT NULL,
  is_broadcast boolean NOT NULL DEFAULT false,
  sent_by uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.notification_reads (
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  notification_id uuid NOT NULL REFERENCES public.notifications(id) ON DELETE CASCADE,
  read_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, notification_id)
);

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

ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notification_reads ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own or broadcast" ON public.notifications
  FOR SELECT TO authenticated
  USING (is_broadcast = true OR target_user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Admins manage notifications" ON public.notifications
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin'))
  WITH CHECK (public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Users manage own reads" ON public.notification_reads
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE INDEX idx_notifications_target ON public.notifications(target_user_id, created_at DESC);
CREATE INDEX idx_notifications_broadcast ON public.notifications(is_broadcast, created_at DESC);

ALTER PUBLICATION supabase_realtime ADD TABLE public.notifications;
ALTER TABLE public.notifications REPLICA IDENTITY FULL;

CREATE OR REPLACE FUNCTION public.admin_send_notification(
  p_admin uuid,
  p_target_phone text,
  p_title text,
  p_body text
) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
AS $$
DECLARE v_target uuid; v_broadcast boolean; v_id uuid;
BEGIN
  IF NOT public.has_role(p_admin, 'admin') THEN RAISE EXCEPTION 'অননুমোদিত'; END IF;
  IF coalesce(trim(p_title),'') = '' OR coalesce(trim(p_body),'') = '' THEN
    RAISE EXCEPTION 'টাইটেল ও মেসেজ আবশ্যক';
  END IF;
  IF coalesce(trim(p_target_phone),'') = '' THEN
    v_broadcast := true;
    v_target := NULL;
  ELSE
    v_broadcast := false;
    SELECT id INTO v_target FROM profiles WHERE phone = p_target_phone;
    IF v_target IS NULL THEN RAISE EXCEPTION 'এই নম্বরের ইউজার পাওয়া যায়নি'; END IF;
  END IF;
  INSERT INTO notifications (target_user_id, title, body, is_broadcast, sent_by)
  VALUES (v_target, p_title, p_body, v_broadcast, p_admin)
  RETURNING id INTO v_id;
  RETURN jsonb_build_object('ok', true, 'id', v_id, 'broadcast', v_broadcast);
END; $$;