-- Wallets
CREATE TABLE public.wallets (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  balance NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.wallets TO authenticated;
GRANT ALL ON public.wallets TO service_role;
ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own wallet" ON public.wallets FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Admins view all wallets" ON public.wallets FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER trg_wallets_updated BEFORE UPDATE ON public.wallets FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Auto-create wallet on new profile
CREATE OR REPLACE FUNCTION public.ensure_wallet() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN INSERT INTO public.wallets(user_id) VALUES (NEW.id) ON CONFLICT DO NOTHING; RETURN NEW; END; $$;
CREATE TRIGGER trg_profiles_wallet AFTER INSERT ON public.profiles FOR EACH ROW EXECUTE FUNCTION public.ensure_wallet();
INSERT INTO public.wallets(user_id) SELECT id FROM public.profiles ON CONFLICT DO NOTHING;

-- Payment channels
CREATE TABLE public.payment_channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  kind TEXT NOT NULL DEFAULT 'till', -- till | paybill | bank | other
  till_number TEXT,
  paybill_number TEXT,
  account_number TEXT,
  account_name TEXT,
  instructions TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.payment_channels TO anon, authenticated;
GRANT INSERT, UPDATE, DELETE ON public.payment_channels TO authenticated;
GRANT ALL ON public.payment_channels TO service_role;
ALTER TABLE public.payment_channels ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone views active channels" ON public.payment_channels FOR SELECT TO anon, authenticated USING (is_active OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins manage channels" ON public.payment_channels FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER trg_payment_channels_updated BEFORE UPDATE ON public.payment_channels FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

INSERT INTO public.payment_channels (name, kind, till_number, account_name, instructions, sort_order)
VALUES ('M-PESA Buy Goods', 'till', '3521362', 'RISTATECHENTERPRISES',
'Go to M-PESA → Lipa na M-PESA → Buy Goods → Till 3521362 → Enter amount → Confirm paid to RISTATECHENTERPRISES.', 1);

-- Deposit status
CREATE TYPE public.deposit_status AS ENUM ('pending','approved','rejected');

CREATE TABLE public.deposits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  channel_id UUID REFERENCES public.payment_channels(id) ON DELETE SET NULL,
  amount NUMERIC(12,2) NOT NULL CHECK (amount > 0),
  mpesa_reference TEXT NOT NULL,
  note TEXT,
  status public.deposit_status NOT NULL DEFAULT 'pending',
  admin_notes TEXT,
  reviewed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  reviewed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.deposits TO authenticated;
GRANT ALL ON public.deposits TO service_role;
ALTER TABLE public.deposits ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own deposits" ON public.deposits FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Users create own deposits" ON public.deposits FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id AND status = 'pending');
CREATE POLICY "Admins view all deposits" ON public.deposits FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins update deposits" ON public.deposits FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER trg_deposits_updated BEFORE UPDATE ON public.deposits FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Service request enrichments
ALTER TABLE public.service_requests
  ADD COLUMN price NUMERIC(12,2),
  ADD COLUMN paid BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN paid_at TIMESTAMPTZ,
  ADD COLUMN delivery_notes TEXT,
  ADD COLUMN deliverables JSONB NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN delivered_at TIMESTAMPTZ;

-- Approve deposit RPC: adds amount to wallet atomically
CREATE OR REPLACE FUNCTION public.approve_deposit(_deposit_id UUID, _note TEXT DEFAULT NULL)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE d RECORD;
BEGIN
  IF NOT public.has_role(auth.uid(),'admin') THEN RAISE EXCEPTION 'Not authorized'; END IF;
  SELECT * INTO d FROM public.deposits WHERE id = _deposit_id FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Deposit not found'; END IF;
  IF d.status <> 'pending' THEN RAISE EXCEPTION 'Deposit already reviewed'; END IF;
  INSERT INTO public.wallets(user_id, balance) VALUES (d.user_id, d.amount)
    ON CONFLICT (user_id) DO UPDATE SET balance = public.wallets.balance + EXCLUDED.balance, updated_at = now();
  UPDATE public.deposits SET status='approved', admin_notes = _note, reviewed_by = auth.uid(), reviewed_at = now() WHERE id = _deposit_id;
END; $$;
REVOKE ALL ON FUNCTION public.approve_deposit(UUID, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.approve_deposit(UUID, TEXT) TO authenticated;

CREATE OR REPLACE FUNCTION public.reject_deposit(_deposit_id UUID, _note TEXT DEFAULT NULL)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NOT public.has_role(auth.uid(),'admin') THEN RAISE EXCEPTION 'Not authorized'; END IF;
  UPDATE public.deposits SET status='rejected', admin_notes = _note, reviewed_by = auth.uid(), reviewed_at = now()
    WHERE id = _deposit_id AND status='pending';
  IF NOT FOUND THEN RAISE EXCEPTION 'Deposit not found or already reviewed'; END IF;
END; $$;
REVOKE ALL ON FUNCTION public.reject_deposit(UUID, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.reject_deposit(UUID, TEXT) TO authenticated;

-- Pay for service from balance
CREATE OR REPLACE FUNCTION public.pay_for_service(_request_id UUID)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE r RECORD; w RECORD;
BEGIN
  SELECT * INTO r FROM public.service_requests WHERE id = _request_id FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Request not found'; END IF;
  IF r.user_id <> auth.uid() THEN RAISE EXCEPTION 'Not your request'; END IF;
  IF r.paid THEN RAISE EXCEPTION 'Already paid'; END IF;
  IF r.price IS NULL OR r.price <= 0 THEN RAISE EXCEPTION 'Price not set yet'; END IF;
  SELECT * INTO w FROM public.wallets WHERE user_id = auth.uid() FOR UPDATE;
  IF NOT FOUND OR w.balance < r.price THEN RAISE EXCEPTION 'Insufficient balance'; END IF;
  UPDATE public.wallets SET balance = balance - r.price, updated_at = now() WHERE user_id = auth.uid();
  UPDATE public.service_requests SET paid = true, paid_at = now(),
    status = CASE WHEN status = 'pending' THEN 'processing'::request_status ELSE status END
    WHERE id = _request_id;
END; $$;
REVOKE ALL ON FUNCTION public.pay_for_service(UUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.pay_for_service(UUID) TO authenticated;