-- 1. Extend app_settings
ALTER TABLE public.app_settings
  ADD COLUMN IF NOT EXISTS welcome_bonus_text text NOT NULL DEFAULT 'নতুন অ্যাকাউন্টে স্বাগত বোনাস হিসেবে ৳1,000 পাবেন।',
  ADD COLUMN IF NOT EXISTS theme_preset text NOT NULL DEFAULT 'pink',
  ADD COLUMN IF NOT EXISTS theme_mode text NOT NULL DEFAULT 'light',
  ADD COLUMN IF NOT EXISTS whatsapp_url text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS facebook_url text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS telegram_url text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS help_center_intro text NOT NULL DEFAULT 'যেকোনো সমস্যায় আমাদের কমিউনিটি বা সাপোর্ট টিমের সাথে যোগাযোগ করুন।';

-- 2. Recovery email on profiles
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS recovery_email text;

-- 3. PIN reset codes
CREATE TABLE IF NOT EXISTS public.pin_reset_codes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  code_hash text NOT NULL,
  expires_at timestamptz NOT NULL,
  used_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

GRANT ALL ON public.pin_reset_codes TO service_role;
-- No grants for anon/authenticated: all access via SECURITY DEFINER RPCs

ALTER TABLE public.pin_reset_codes ENABLE ROW LEVEL SECURITY;
-- No policies: table locked to service_role and RPCs

CREATE INDEX IF NOT EXISTS idx_pin_reset_user ON public.pin_reset_codes(user_id, expires_at DESC);

-- 4. RPC: change PIN using old PIN
CREATE OR REPLACE FUNCTION public.change_user_pin(
  _user uuid,
  _old_hash text,
  _new_hash text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE v_current text;
BEGIN
  SELECT pin_hash INTO v_current FROM profiles WHERE id = _user;
  IF NOT FOUND THEN RAISE EXCEPTION 'অ্যাকাউন্ট পাওয়া যায়নি'; END IF;
  IF v_current <> _old_hash THEN RAISE EXCEPTION 'আগের পিন সঠিক নয়'; END IF;
  IF _new_hash = _old_hash THEN RAISE EXCEPTION 'নতুন পিন আগের পিনের মতো হতে পারবে না'; END IF;
  UPDATE profiles SET pin_hash = _new_hash WHERE id = _user;
  RETURN jsonb_build_object('ok', true);
END; $$;

-- 5. RPC: create reset code (server calls this, then emails plaintext)
CREATE OR REPLACE FUNCTION public.create_pin_reset(
  _user uuid,
  _code_hash text,
  _ttl_minutes int DEFAULT 10
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE v_id uuid;
BEGIN
  -- Invalidate old unused codes
  UPDATE pin_reset_codes SET used_at = now() WHERE user_id = _user AND used_at IS NULL;
  INSERT INTO pin_reset_codes (user_id, code_hash, expires_at)
  VALUES (_user, _code_hash, now() + make_interval(mins => _ttl_minutes))
  RETURNING id INTO v_id;
  RETURN jsonb_build_object('ok', true, 'id', v_id);
END; $$;

-- 6. RPC: verify code + set new pin
CREATE OR REPLACE FUNCTION public.verify_and_reset_pin(
  _user uuid,
  _code_hash text,
  _new_hash text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE v_rec pin_reset_codes%ROWTYPE;
BEGIN
  SELECT * INTO v_rec FROM pin_reset_codes
    WHERE user_id = _user AND code_hash = _code_hash AND used_at IS NULL AND expires_at > now()
    ORDER BY created_at DESC LIMIT 1 FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'ভুল অথবা মেয়াদোত্তীর্ণ কোড'; END IF;
  UPDATE pin_reset_codes SET used_at = now() WHERE id = v_rec.id;
  UPDATE profiles SET pin_hash = _new_hash WHERE id = _user;
  RETURN jsonb_build_object('ok', true);
END; $$;