CREATE TYPE public.payment_status AS ENUM ('awaiting_payment','proof_submitted','approved','rejected');

CREATE TABLE public.payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  purpose text NOT NULL DEFAULT 'ai_scan',
  amount numeric NOT NULL DEFAULT 5000,
  currency text NOT NULL DEFAULT 'NGN',
  reference text NOT NULL UNIQUE DEFAULT concat('EMMA-', to_char(now(),'YYMMDD'), '-', upper(substr(gen_random_uuid()::text,1,6))),
  payer_name text,
  proof_url text,
  status public.payment_status NOT NULL DEFAULT 'awaiting_payment',
  admin_note text,
  reviewed_by uuid,
  reviewed_at timestamptz,
  consumed_at timestamptz,
  scan_id uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE ON public.payments TO authenticated;
GRANT ALL ON public.payments TO service_role;
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "payments own read" ON public.payments FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "payments own insert" ON public.payments FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid());
CREATE POLICY "payments own update" ON public.payments FOR UPDATE TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'))
  WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

CREATE TRIGGER payments_touch BEFORE UPDATE ON public.payments
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE INDEX payments_user_status_idx ON public.payments (user_id, status);

-- Helper: notify all admins
CREATE OR REPLACE FUNCTION public.notify_admins(_title text, _body text)
RETURNS void LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$
  INSERT INTO public.notifications (user_id, title, body)
  SELECT ur.user_id, _title, _body FROM public.user_roles ur WHERE ur.role = 'admin';
$$;
REVOKE EXECUTE ON FUNCTION public.notify_admins(text,text) FROM anon, authenticated;

CREATE OR REPLACE FUNCTION public.notify_booking_change()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    PERFORM public.notify_admins('New booking request', NEW.service_name || ' from ' || NEW.full_name);
  ELSIF NEW.status IS DISTINCT FROM OLD.status AND NEW.user_id IS NOT NULL THEN
    INSERT INTO public.notifications (user_id, title, body)
    VALUES (NEW.user_id, 'Booking update', NEW.service_name || ' is now: ' || replace(NEW.status::text,'_',' '));
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER bookings_notify AFTER INSERT OR UPDATE ON public.bookings
  FOR EACH ROW EXECUTE FUNCTION public.notify_booking_change();

CREATE OR REPLACE FUNCTION public.notify_report_published()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NEW.published AND (TG_OP = 'INSERT' OR NOT OLD.published) THEN
    INSERT INTO public.notifications (user_id, title, body)
    VALUES (NEW.user_id, 'Inspection report available', 'Report ' || NEW.reference || ' is ready to view.');
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER reports_notify AFTER INSERT OR UPDATE ON public.inspection_reports
  FOR EACH ROW EXECUTE FUNCTION public.notify_report_published();

CREATE OR REPLACE FUNCTION public.notify_review_approved()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    PERFORM public.notify_admins('New review submitted', NEW.author_name || ' left a ' || NEW.rating || '-star review');
  ELSIF NEW.approved AND NOT OLD.approved THEN
    INSERT INTO public.notifications (user_id, title, body)
    VALUES (NEW.user_id, 'Review published', 'Your review is now visible to other customers.');
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER reviews_notify AFTER INSERT OR UPDATE ON public.reviews
  FOR EACH ROW EXECUTE FUNCTION public.notify_review_approved();

CREATE OR REPLACE FUNCTION public.notify_payment_change()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NEW.status IS DISTINCT FROM OLD.status THEN
    IF NEW.status = 'proof_submitted' THEN
      PERFORM public.notify_admins('Payment proof submitted', 'Reference ' || NEW.reference || ' is awaiting approval.');
    ELSIF NEW.status = 'approved' THEN
      INSERT INTO public.notifications (user_id, title, body)
      VALUES (NEW.user_id, 'Payment approved', 'Reference ' || NEW.reference || ' approved. You can now run your AI mold check.');
    ELSIF NEW.status = 'rejected' THEN
      INSERT INTO public.notifications (user_id, title, body)
      VALUES (NEW.user_id, 'Payment could not be verified', coalesce(NEW.admin_note, 'Please check your transfer details and try again.'));
    END IF;
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER payments_notify AFTER UPDATE ON public.payments
  FOR EACH ROW EXECUTE FUNCTION public.notify_payment_change();