-- Notify admins when a new service provider applies
CREATE OR REPLACE FUNCTION public.notify_company_application()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  PERFORM public.notify_admins(
    'New service provider application',
    NEW.name || ' applied to join the Emma marketplace. Review and approve in the Companies tab.'
  );
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS companies_notify_application ON public.companies;
CREATE TRIGGER companies_notify_application AFTER INSERT ON public.companies
FOR EACH ROW EXECUTE FUNCTION public.notify_company_application();

-- Notify the owner when their application status changes
CREATE OR REPLACE FUNCTION public.notify_company_status()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF NEW.status IS DISTINCT FROM OLD.status THEN
    INSERT INTO public.notifications (user_id, title, body)
    VALUES (
      NEW.owner_id,
      'Service provider status: ' || NEW.status,
      CASE NEW.status
        WHEN 'approved' THEN 'Your company was approved. You can now sign in to the Service Provider Portal.'
        WHEN 'rejected' THEN 'Your application was rejected. Contact Emma Exclusive Teams for details.'
        WHEN 'suspended' THEN 'Your company has been suspended. Portal access is paused.'
        ELSE 'Your application is pending admin approval.'
      END
    );
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS companies_notify_status ON public.companies;
CREATE TRIGGER companies_notify_status AFTER UPDATE ON public.companies
FOR EACH ROW EXECUTE FUNCTION public.notify_company_status();

-- Notify admins about new AI mold scans
CREATE OR REPLACE FUNCTION public.notify_scan_created()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  PERFORM public.notify_admins('New AI mold scan submitted', 'A customer submitted a new AI mold assessment for review.');
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS mold_scans_notify ON public.mold_scans;
CREATE TRIGGER mold_scans_notify AFTER INSERT ON public.mold_scans
FOR EACH ROW EXECUTE FUNCTION public.notify_scan_created();

-- Notify admins about new quotation requests
CREATE OR REPLACE FUNCTION public.notify_quotation_created()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  PERFORM public.notify_admins('New quotation request', 'A new quotation was created and needs review.');
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS quotations_notify ON public.quotations;
CREATE TRIGGER quotations_notify AFTER INSERT ON public.quotations
FOR EACH ROW EXECUTE FUNCTION public.notify_quotation_created();

REVOKE ALL ON FUNCTION public.notify_company_application() FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.notify_company_status() FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.notify_scan_created() FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.notify_quotation_created() FROM PUBLIC, anon, authenticated;