-- Realtime for notifications
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'notifications'
  ) THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE public.notifications;
  END IF;
END $$;
ALTER TABLE public.notifications REPLICA IDENTITY FULL;

-- Unique constraint for device upsert (idempotent)
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_indexes WHERE schemaname='public' AND indexname='devices_user_agent_ip_unique'
  ) THEN
    CREATE UNIQUE INDEX devices_user_agent_ip_unique
      ON public.devices (user_id, COALESCE(user_agent,''), COALESCE(ip,''));
  END IF;
END $$;

-- Seed / upsert app settings
INSERT INTO public.app_settings (key, value, is_public) VALUES
  ('app.name',                to_jsonb('eBay BOS'::text),                                       true),
  ('app.support_email',       to_jsonb('support@ebaybos.app'::text),                            true),
  ('signup.enabled',          to_jsonb(true),                                                   true),
  ('features.notifications',  to_jsonb(true),                                                   true),
  ('features.workspaces',     to_jsonb(true),                                                   true),
  ('ebay.client_id',          to_jsonb(''::text),                                               false),
  ('ebay.client_secret',      to_jsonb(''::text),                                               false),
  ('ebay.redirect_uri',       to_jsonb(''::text),                                               false),
  ('notifications.defaults',  '{"email": true, "push": false, "in_app": true}'::jsonb,          false)
ON CONFLICT (key) DO NOTHING;

-- Helper: notify all admins
CREATE OR REPLACE FUNCTION public.notify_admins(_type text, _title text, _body text, _data jsonb DEFAULT '{}'::jsonb)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.notifications (user_id, type, title, body, data)
  SELECT ur.user_id, _type, _title, _body, _data FROM public.user_roles ur WHERE ur.role = 'admin';
END; $$;

-- Notify admins on new signup
CREATE OR REPLACE FUNCTION public.notify_new_signup()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  PERFORM public.notify_admins(
    'signup',
    'New user signed up',
    COALESCE(NEW.email, 'A new user just created an account.'),
    jsonb_build_object('user_id', NEW.id, 'email', NEW.email)
  );
  RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS on_auth_user_signup_notify_admins ON auth.users;
CREATE TRIGGER on_auth_user_signup_notify_admins
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.notify_new_signup();