-- 1) profiles-এ kyc_status কলাম
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='profiles' AND column_name='kyc_status') THEN
    ALTER TABLE public.profiles ADD COLUMN kyc_status text NOT NULL DEFAULT 'unverified';
    ALTER TABLE public.profiles ADD CONSTRAINT profiles_kyc_status_chk CHECK (kyc_status IN ('unverified','pending','needs_review','verified','rejected'));
  END IF;
END $$;

-- 2) kyc_verifications table
CREATE TABLE IF NOT EXISTS public.kyc_verifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  nid_number text,
  name_on_nid text,
  dob text,
  address text,
  nid_front_path text NOT NULL,
  nid_back_path text NOT NULL,
  selfie_path text NOT NULL,
  nid_front_hash text NOT NULL,
  nid_back_hash text,
  selfie_hash text,
  status text NOT NULL DEFAULT 'pending',
  ai_confidence numeric,
  ai_notes text,
  ai_extracted jsonb,
  review_note text,
  reviewed_by uuid REFERENCES auth.users(id),
  reviewed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT kyc_status_chk CHECK (status IN ('pending','needs_review','verified','rejected'))
);

CREATE UNIQUE INDEX IF NOT EXISTS kyc_verifications_nid_number_verified_uniq
  ON public.kyc_verifications (nid_number)
  WHERE status = 'verified' AND nid_number IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS kyc_verifications_front_hash_uniq
  ON public.kyc_verifications (nid_front_hash)
  WHERE status IN ('verified','pending','needs_review');

CREATE INDEX IF NOT EXISTS kyc_verifications_user_idx ON public.kyc_verifications (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS kyc_verifications_status_idx ON public.kyc_verifications (status, created_at DESC);

GRANT SELECT, INSERT, UPDATE ON public.kyc_verifications TO authenticated;
GRANT ALL ON public.kyc_verifications TO service_role;

ALTER TABLE public.kyc_verifications ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users view own kyc" ON public.kyc_verifications
  FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Users insert own kyc" ON public.kyc_verifications
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Admin update kyc" ON public.kyc_verifications
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin'))
  WITH CHECK (public.has_role(auth.uid(), 'admin'));

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

-- 3) send_money ও make_payment এ KYC চেক
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::text)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
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.kyc_status <> 'verified' THEN RAISE EXCEPTION 'অনুগ্রহ করে NID ভেরিফিকেশন সম্পন্ন করুন'; 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; $function$;

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 TO 'public'
AS $function$
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.kyc_status <> 'verified' THEN RAISE EXCEPTION 'অনুগ্রহ করে NID ভেরিফিকেশন সম্পন্ন করুন'; 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; $function$;

-- 4) admin KYC review function
CREATE OR REPLACE FUNCTION public.admin_review_kyc(p_admin uuid, p_kyc_id uuid, p_approve boolean, p_note text DEFAULT NULL)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE v_kyc kyc_verifications%ROWTYPE; v_new_status text;
BEGIN
  IF NOT public.has_role(p_admin, 'admin') THEN RAISE EXCEPTION 'অননুমোদিত'; END IF;
  SELECT * INTO v_kyc FROM kyc_verifications WHERE id = p_kyc_id FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'রেকর্ড পাওয়া যায়নি'; END IF;
  v_new_status := CASE WHEN p_approve THEN 'verified' ELSE 'rejected' END;
  UPDATE kyc_verifications
    SET status = v_new_status, review_note = p_note, reviewed_by = p_admin, reviewed_at = now()
  WHERE id = p_kyc_id;
  UPDATE profiles SET kyc_status = v_new_status WHERE id = v_kyc.user_id;
  INSERT INTO notifications (target_user_id, title, body, is_broadcast, sent_by)
  VALUES (
    v_kyc.user_id,
    CASE WHEN p_approve THEN 'NID ভেরিফাইড ✅' ELSE 'NID রিজেক্ট ❌' END,
    CASE WHEN p_approve THEN 'আপনার NID ভেরিফিকেশন অনুমোদিত হয়েছে। এখন লেনদেন করতে পারবেন।' ELSE COALESCE('আপনার NID রিজেক্ট হয়েছে: ' || p_note, 'আপনার NID রিজেক্ট হয়েছে। আবার আপলোড করুন।') END,
    false, p_admin
  );
  RETURN jsonb_build_object('ok', true, 'status', v_new_status);
END; $function$;