-- ENUMS
CREATE TYPE public.order_status AS ENUM ('submitted','in_review','processing','awaiting_info','completed','cancelled');
CREATE TYPE public.order_payment_status AS ENUM ('unpaid','pending','paid','refunded');
CREATE TYPE public.payment_method AS ENUM ('wallet','mpesa','paystack','cash');

-- CATALOG SERVICES
CREATE TABLE public.catalog_services (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  icon text NOT NULL DEFAULT 'FileText',
  category text NOT NULL DEFAULT 'General',
  price_label text NOT NULL DEFAULT 'On request',
  base_price numeric NOT NULL DEFAULT 0,
  turnaround text NOT NULL DEFAULT 'Varies',
  description text NOT NULL DEFAULT '',
  requirements text[] NOT NULL DEFAULT '{}',
  image_url text,
  is_featured boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,
  sort_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.catalog_services TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.catalog_services TO authenticated;
GRANT ALL ON public.catalog_services TO service_role;
ALTER TABLE public.catalog_services ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view active services" ON public.catalog_services FOR SELECT TO anon, authenticated USING (is_active OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins manage services" ON public.catalog_services FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- ORDER REFERENCE SEQUENCE
CREATE SEQUENCE public.order_ref_seq START 1;

-- ORDERS
CREATE TABLE public.orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_no text NOT NULL UNIQUE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  service_id uuid REFERENCES public.catalog_services(id) ON DELETE SET NULL,
  service_slug text NOT NULL,
  service_name text NOT NULL,
  contact_name text NOT NULL,
  contact_phone text NOT NULL,
  contact_email text,
  whatsapp text,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  notes text,
  admin_notes text,
  status public.order_status NOT NULL DEFAULT 'submitted',
  payment_status public.order_payment_status NOT NULL DEFAULT 'unpaid',
  price numeric,
  discount numeric NOT NULL DEFAULT 0,
  assigned_to uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  delivery_notes text,
  paid_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders TO authenticated;
GRANT ALL ON public.orders TO service_role;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own orders" ON public.orders FOR SELECT TO authenticated USING (auth.uid() = user_id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Users create own orders" ON public.orders FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own unpaid orders" ON public.orders FOR UPDATE TO authenticated USING (auth.uid() = user_id AND payment_status = 'unpaid') WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Admins manage orders" ON public.orders FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE INDEX orders_user_idx ON public.orders(user_id, created_at DESC);
CREATE INDEX orders_status_idx ON public.orders(status);

CREATE OR REPLACE FUNCTION public.set_order_no()
RETURNS trigger LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
  IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
    NEW.order_no := 'FX-' || to_char(now(),'YYYY') || '-' || lpad(nextval('public.order_ref_seq')::text, 6, '0');
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER orders_set_order_no BEFORE INSERT ON public.orders FOR EACH ROW EXECUTE FUNCTION public.set_order_no();
CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON public.orders FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER catalog_services_set_updated_at BEFORE UPDATE ON public.catalog_services FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ORDER DOCUMENTS
CREATE TABLE public.order_documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid NOT NULL REFERENCES public.orders(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  kind text NOT NULL DEFAULT 'upload',
  name text NOT NULL,
  path text NOT NULL,
  mime_type text,
  size_bytes bigint,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, DELETE ON public.order_documents TO authenticated;
GRANT ALL ON public.order_documents TO service_role;
ALTER TABLE public.order_documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own order documents" ON public.order_documents FOR SELECT TO authenticated USING (auth.uid() = user_id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Users add documents to own orders" ON public.order_documents FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id AND kind = 'upload');
CREATE POLICY "Admins manage order documents" ON public.order_documents FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- PAYMENTS
CREATE TABLE public.payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  order_id uuid REFERENCES public.orders(id) ON DELETE SET NULL,
  amount numeric NOT NULL,
  method public.payment_method NOT NULL DEFAULT 'wallet',
  reference text,
  description text,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.payments TO authenticated;
GRANT ALL ON public.payments TO service_role;
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own payments" ON public.payments FOR SELECT TO authenticated USING (auth.uid() = user_id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins manage payments" ON public.payments FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- NOTIFICATIONS
CREATE TABLE public.notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  order_id uuid REFERENCES public.orders(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text NOT NULL DEFAULT '',
  link text,
  is_read boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, UPDATE ON public.notifications TO authenticated;
GRANT ALL ON public.notifications TO service_role;
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own notifications" ON public.notifications FOR SELECT TO authenticated USING (auth.uid() = user_id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Users mark own notifications read" ON public.notifications FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
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 INDEX notifications_user_idx ON public.notifications(user_id, created_at DESC);

CREATE OR REPLACE FUNCTION public.notify_order_change()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.notifications(user_id, order_id, title, body, link)
    VALUES (NEW.user_id, NEW.id, 'Order ' || NEW.order_no || ' received',
            'We have received your request for ' || NEW.service_name || '.', '/orders');
  ELSE
    IF NEW.status IS DISTINCT FROM OLD.status THEN
      INSERT INTO public.notifications(user_id, order_id, title, body, link)
      VALUES (NEW.user_id, NEW.id, 'Order ' || NEW.order_no || ' is now ' || replace(NEW.status::text,'_',' '),
              NEW.service_name, '/orders');
    END IF;
    IF NEW.payment_status IS DISTINCT FROM OLD.payment_status THEN
      INSERT INTO public.notifications(user_id, order_id, title, body, link)
      VALUES (NEW.user_id, NEW.id, 'Payment ' || NEW.payment_status::text || ' for ' || NEW.order_no,
              NEW.service_name, '/payments');
    END IF;
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER orders_notify_insert AFTER INSERT ON public.orders FOR EACH ROW EXECUTE FUNCTION public.notify_order_change();
CREATE TRIGGER orders_notify_update AFTER UPDATE ON public.orders FOR EACH ROW EXECUTE FUNCTION public.notify_order_change();

-- PAY FOR AN ORDER FROM WALLET
CREATE OR REPLACE FUNCTION public.pay_order_from_wallet(_order_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE o RECORD; w RECORD; total numeric;
BEGIN
  SELECT * INTO o FROM public.orders WHERE id = _order_id FOR UPDATE;
  IF NOT FOUND OR o.user_id <> auth.uid() THEN RAISE EXCEPTION 'Order not found'; END IF;
  IF o.payment_status = 'paid' THEN RAISE EXCEPTION 'Order already paid'; END IF;
  IF o.price IS NULL OR o.price <= 0 THEN RAISE EXCEPTION 'Price not set yet'; END IF;
  total := greatest(o.price - o.discount, 0);
  SELECT * INTO w FROM public.wallets WHERE user_id = auth.uid() FOR UPDATE;
  IF NOT FOUND OR w.balance < total THEN RAISE EXCEPTION 'Insufficient balance'; END IF;
  UPDATE public.wallets SET balance = balance - total, updated_at = now() WHERE user_id = auth.uid();
  INSERT INTO public.payments(user_id, order_id, amount, method, reference, description)
  VALUES (auth.uid(), o.id, total, 'wallet', o.order_no, 'Payment for ' || o.service_name);
  UPDATE public.orders
    SET payment_status = 'paid', paid_at = now(),
        status = CASE WHEN status = 'submitted' THEN 'processing'::public.order_status ELSE status END
    WHERE id = _order_id;
END; $$;
REVOKE ALL ON FUNCTION public.pay_order_from_wallet(uuid) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.pay_order_from_wallet(uuid) TO authenticated;