-- 1) app_role enum + user_roles table
DO $$ BEGIN
  CREATE TYPE public.app_role AS ENUM ('admin','user');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, role)
);

GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view own roles" ON public.user_roles;
CREATE POLICY "Users can view own roles" ON public.user_roles
  FOR SELECT TO authenticated USING (auth.uid() = user_id);

-- 2) has_role security-definer helper
CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role)
$$;

-- 3) is_blocked column on profiles
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS is_blocked boolean NOT NULL DEFAULT false;

-- 4) admin policies on profiles / transactions (read-all for admins)
DROP POLICY IF EXISTS "Admins can view all profiles" ON public.profiles;
CREATE POLICY "Admins can view all profiles" ON public.profiles
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

DROP POLICY IF EXISTS "Admins can view all transactions" ON public.transactions;
CREATE POLICY "Admins can view all transactions" ON public.transactions
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

-- 5) Block check inside money flows
CREATE OR REPLACE FUNCTION public.send_money(p_sender uuid, p_pin_hash text, p_receiver_phone text, p_amount numeric, p_note text DEFAULT NULL)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_sender profiles%ROWTYPE;
  v_receiver profiles%ROWTYPE;
  v_new_sender_bal numeric;
  v_new_receiver_bal numeric;
BEGIN
  IF p_amount <= 0 THEN RAISE EXCEPTION 'পরিমাণ অবশ্যই ০ এর বেশি হতে হবে'; END IF;
  SELECT * INTO v_sender FROM profiles WHERE id = p_sender FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'প্রেরকের অ্যাকাউন্ট পাওয়া যায়নি'; END IF;
  IF v_sender.is_blocked THEN RAISE EXCEPTION 'আপনার অ্যাকাউন্ট ব্লক করা হয়েছে'; END IF;
  IF v_sender.pin_hash <> p_pin_hash THEN RAISE EXCEPTION 'ভুল পিন'; END IF;
  SELECT * INTO v_receiver FROM profiles WHERE phone = p_receiver_phone FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'প্রাপকের নম্বর বিকাশে নিবন্ধিত নয়'; END IF;
  IF v_receiver.is_blocked THEN RAISE EXCEPTION 'প্রাপকের অ্যাকাউন্ট ব্লক'; END IF;
  IF v_receiver.id = v_sender.id THEN RAISE EXCEPTION 'নিজের নম্বরে টাকা পাঠানো যাবে না'; END IF;
  IF v_sender.balance < p_amount THEN RAISE EXCEPTION 'অপর্যাপ্ত ব্যালেন্স'; END IF;
  v_new_sender_bal := v_sender.balance - p_amount;
  v_new_receiver_bal := v_receiver.balance + p_amount;
  UPDATE profiles SET balance = v_new_sender_bal WHERE id = v_sender.id;
  UPDATE profiles SET balance = v_new_receiver_bal WHERE id = v_receiver.id;
  INSERT INTO transactions (user_id, type, amount, counterparty_phone, counterparty_name, balance_after, note)
  VALUES (v_sender.id, 'send_money', p_amount, v_receiver.phone, v_receiver.full_name, v_new_sender_bal, p_note);
  INSERT INTO transactions (user_id, type, amount, counterparty_phone, counterparty_name, balance_after, note)
  VALUES (v_receiver.id, 'received', p_amount, v_sender.phone, v_sender.full_name, v_new_receiver_bal, p_note);
  RETURN jsonb_build_object('ok', true, 'new_balance', v_new_sender_bal);
END; $$;

CREATE OR REPLACE FUNCTION public.make_payment(p_user uuid, p_pin_hash text, p_type tx_type, p_amount numeric, p_reference text, p_counterparty_name text, p_fee numeric DEFAULT 0)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_user profiles%ROWTYPE; v_total numeric; v_new_bal numeric;
BEGIN
  IF p_amount <= 0 THEN RAISE EXCEPTION 'পরিমাণ অবশ্যই ০ এর বেশি হতে হবে'; END IF;
  IF p_type NOT IN ('cash_out','mobile_recharge','pay_bill','payment') THEN RAISE EXCEPTION 'অবৈধ লেনদেনের ধরন'; END IF;
  SELECT * INTO v_user FROM profiles WHERE id = p_user FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'অ্যাকাউন্ট পাওয়া যায়নি'; END IF;
  IF v_user.is_blocked THEN RAISE EXCEPTION 'আপনার অ্যাকাউন্ট ব্লক করা হয়েছে'; END IF;
  IF v_user.pin_hash <> p_pin_hash THEN RAISE EXCEPTION 'ভুল পিন'; END IF;
  v_total := p_amount + COALESCE(p_fee,0);
  IF v_user.balance < v_total THEN RAISE EXCEPTION 'অপর্যাপ্ত ব্যালেন্স'; END IF;
  v_new_bal := v_user.balance - v_total;
  UPDATE profiles SET balance = v_new_bal WHERE id = v_user.id;
  INSERT INTO transactions (user_id, type, amount, fee, counterparty_phone, counterparty_name, reference, balance_after)
  VALUES (v_user.id, p_type, p_amount, COALESCE(p_fee,0), p_reference, p_counterparty_name, p_reference, v_new_bal);
  RETURN jsonb_build_object('ok', true, 'new_balance', v_new_bal);
END; $$;

-- 6) Admin actions
CREATE OR REPLACE FUNCTION public.admin_adjust_balance(p_admin uuid, p_target uuid, p_amount numeric, p_note text DEFAULT NULL)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_target profiles%ROWTYPE; v_new_bal numeric; v_type tx_type;
BEGIN
  IF NOT public.has_role(p_admin, 'admin') THEN RAISE EXCEPTION 'অননুমোদিত'; END IF;
  IF p_amount = 0 THEN RAISE EXCEPTION 'পরিমাণ ০ হতে পারবে না'; END IF;
  SELECT * INTO v_target FROM profiles WHERE id = p_target FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'ইউজার পাওয়া যায়নি'; END IF;
  v_new_bal := v_target.balance + p_amount;
  IF v_new_bal < 0 THEN RAISE EXCEPTION 'ফলাফল ঋণাত্মক হতে পারবে না'; END IF;
  UPDATE profiles SET balance = v_new_bal WHERE id = p_target;
  v_type := CASE WHEN p_amount > 0 THEN 'add_money'::tx_type ELSE 'payment'::tx_type END;
  INSERT INTO transactions (user_id, type, amount, counterparty_name, balance_after, note)
  VALUES (p_target, v_type, abs(p_amount), 'অ্যাডমিন অ্যাডজাস্টমেন্ট', v_new_bal, COALESCE(p_note,'admin adjust'));
  RETURN jsonb_build_object('ok', true, 'new_balance', v_new_bal);
END; $$;

CREATE OR REPLACE FUNCTION public.admin_set_blocked(p_admin uuid, p_target uuid, p_blocked boolean)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NOT public.has_role(p_admin, 'admin') THEN RAISE EXCEPTION 'অননুমোদিত'; END IF;
  UPDATE profiles SET is_blocked = p_blocked WHERE id = p_target;
  IF NOT FOUND THEN RAISE EXCEPTION 'ইউজার পাওয়া যায়নি'; END IF;
  RETURN jsonb_build_object('ok', true, 'is_blocked', p_blocked);
END; $$;