-- =========================
-- PROFILES
-- =========================
CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  phone text UNIQUE NOT NULL,
  full_name text NOT NULL DEFAULT 'bKash User',
  pin_hash text NOT NULL,
  balance numeric(14,2) NOT NULL DEFAULT 1000.00,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

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

CREATE POLICY "Users can view own profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

-- =========================
-- TRANSACTIONS
-- =========================
CREATE TYPE public.tx_type AS ENUM (
  'send_money', 'received', 'cash_out', 'mobile_recharge', 'pay_bill', 'add_money', 'payment'
);

CREATE TABLE public.transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type public.tx_type NOT NULL,
  amount numeric(14,2) NOT NULL CHECK (amount > 0),
  fee numeric(14,2) NOT NULL DEFAULT 0,
  counterparty_phone text,
  counterparty_name text,
  reference text,
  balance_after numeric(14,2) NOT NULL,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_transactions_user_created ON public.transactions(user_id, created_at DESC);

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

CREATE POLICY "Users can view own transactions" ON public.transactions
  FOR SELECT USING (auth.uid() = user_id);

-- =========================
-- AUTO PROFILE CREATION
-- =========================
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, phone, full_name, pin_hash, balance)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'phone', NEW.email),
    COALESCE(NEW.raw_user_meta_data->>'full_name', 'bKash User'),
    COALESCE(NEW.raw_user_meta_data->>'pin_hash', ''),
    1000.00
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- =========================
-- updated_at trigger
-- =========================
CREATE OR REPLACE FUNCTION public.touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

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

-- =========================
-- SEND MONEY (atomic)
-- =========================
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.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.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; $$;

REVOKE ALL ON FUNCTION public.send_money(uuid, text, text, numeric, text) FROM public;

-- =========================
-- MAKE PAYMENT (recharge / bill / cash out / payment)
-- =========================
CREATE OR REPLACE FUNCTION public.make_payment(
  p_user uuid,
  p_pin_hash text,
  p_type public.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.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; $$;

REVOKE ALL ON FUNCTION public.make_payment(uuid, text, public.tx_type, numeric, text, text, numeric) FROM public;