-- Profile additions
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS referral_code text UNIQUE,
  ADD COLUMN IF NOT EXISTS referred_by uuid,
  ADD COLUMN IF NOT EXISTS first_paid_at timestamptz,
  ADD COLUMN IF NOT EXISTS loyalty_points integer NOT NULL DEFAULT 0;

UPDATE public.profiles SET referral_code = 'FLX' || upper(substr(md5(id::text),1,6)) WHERE referral_code IS NULL;

-- Request additions
ALTER TABLE public.service_requests
  ADD COLUMN IF NOT EXISTS discount numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS promo_code text,
  ADD COLUMN IF NOT EXISTS points_redeemed integer NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS points_earned integer NOT NULL DEFAULT 0;

-- Promo codes
CREATE TABLE IF NOT EXISTS public.promo_codes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text UNIQUE NOT NULL,
  kind text NOT NULL CHECK (kind IN ('percent','fixed')),
  value numeric NOT NULL CHECK (value > 0),
  min_amount numeric NOT NULL DEFAULT 0,
  usage_limit integer,
  per_user_limit integer NOT NULL DEFAULT 1,
  starts_at timestamptz,
  expires_at timestamptz,
  active boolean NOT NULL DEFAULT true,
  description text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.promo_codes TO anon, authenticated;
GRANT ALL ON public.promo_codes TO service_role;
ALTER TABLE public.promo_codes ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Public read active codes" ON public.promo_codes;
CREATE POLICY "Public read active codes" ON public.promo_codes FOR SELECT USING (active = true);
DROP POLICY IF EXISTS "Admins manage promo codes" ON public.promo_codes;
CREATE POLICY "Admins manage promo codes" ON public.promo_codes FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- Redemptions
CREATE TABLE IF NOT EXISTS public.promo_redemptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code_id uuid NOT NULL REFERENCES public.promo_codes(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  request_id uuid REFERENCES public.service_requests(id) ON DELETE SET NULL,
  discount numeric NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.promo_redemptions TO authenticated;
GRANT ALL ON public.promo_redemptions TO service_role;
ALTER TABLE public.promo_redemptions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users view own redemptions" ON public.promo_redemptions;
CREATE POLICY "Users view own redemptions" ON public.promo_redemptions FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Admins view all redemptions" ON public.promo_redemptions;
CREATE POLICY "Admins view all redemptions" ON public.promo_redemptions FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));

-- Offers / seasonal banners
CREATE TABLE IF NOT EXISTS public.offers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  description text NOT NULL,
  badge text,
  promo_code text,
  cta_label text,
  cta_url text,
  starts_at timestamptz,
  ends_at timestamptz,
  active boolean NOT NULL DEFAULT true,
  sort_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.offers TO anon, authenticated;
GRANT ALL ON public.offers TO service_role;
ALTER TABLE public.offers ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Public read active offers" ON public.offers;
CREATE POLICY "Public read active offers" ON public.offers FOR SELECT USING (active = true AND (starts_at IS NULL OR starts_at <= now()) AND (ends_at IS NULL OR ends_at >= now()));
DROP POLICY IF EXISTS "Admins manage offers" ON public.offers;
CREATE POLICY "Admins manage offers" ON public.offers FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- Referrals log
CREATE TABLE IF NOT EXISTS public.referrals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  referrer_id uuid NOT NULL,
  referee_id uuid NOT NULL UNIQUE,
  reward_amount numeric NOT NULL DEFAULT 0,
  rewarded_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.referrals TO authenticated;
GRANT ALL ON public.referrals TO service_role;
ALTER TABLE public.referrals ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users view own referrals" ON public.referrals;
CREATE POLICY "Users view own referrals" ON public.referrals FOR SELECT TO authenticated USING (auth.uid() = referrer_id OR auth.uid() = referee_id);
DROP POLICY IF EXISTS "Admins view referrals" ON public.referrals;
CREATE POLICY "Admins view referrals" ON public.referrals FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));

-- Updated triggers
DROP TRIGGER IF EXISTS promo_codes_updated ON public.promo_codes;
CREATE TRIGGER promo_codes_updated BEFORE UPDATE ON public.promo_codes FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
DROP TRIGGER IF EXISTS offers_updated ON public.offers;
CREATE TRIGGER offers_updated BEFORE UPDATE ON public.offers FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Handle new user (referral code + optional referrer + welcome points)
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE ref_code text; ref_user uuid; ref_input text;
BEGIN
  ref_code := 'FLX' || upper(substr(md5(NEW.id::text),1,6));
  ref_input := NEW.raw_user_meta_data->>'referral_code';
  IF ref_input IS NOT NULL AND length(ref_input) > 0 THEN
    SELECT id INTO ref_user FROM public.profiles WHERE lower(referral_code) = lower(ref_input) LIMIT 1;
    IF ref_user = NEW.id THEN ref_user := NULL; END IF;
  END IF;
  INSERT INTO public.profiles (id, full_name, username, email, phone, avatar_url, referral_code, referred_by, loyalty_points)
  VALUES (
    NEW.id,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'username',
    NEW.email,
    NEW.raw_user_meta_data->>'phone',
    NEW.raw_user_meta_data->>'avatar_url',
    ref_code,
    ref_user,
    CASE WHEN ref_user IS NOT NULL THEN 100 ELSE 0 END
  )
  ON CONFLICT (id) DO NOTHING;
  IF ref_user IS NOT NULL THEN
    INSERT INTO public.referrals (referrer_id, referee_id) VALUES (ref_user, NEW.id) ON CONFLICT DO NOTHING;
  END IF;
  RETURN NEW;
END; $$;

-- Apply promo to a request
CREATE OR REPLACE FUNCTION public.apply_promo_to_request(_request_id uuid, _code text)
RETURNS numeric LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE r RECORD; p RECORD; disc numeric; uses int; user_uses int;
BEGIN
  SELECT * INTO r FROM public.service_requests WHERE id = _request_id FOR UPDATE;
  IF NOT FOUND OR r.user_id <> auth.uid() THEN RAISE EXCEPTION 'Request not found'; 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 p FROM public.promo_codes WHERE lower(code) = lower(_code) AND active = true LIMIT 1;
  IF NOT FOUND THEN RAISE EXCEPTION 'Invalid or inactive code'; END IF;
  IF p.starts_at IS NOT NULL AND p.starts_at > now() THEN RAISE EXCEPTION 'Code not yet active'; END IF;
  IF p.expires_at IS NOT NULL AND p.expires_at < now() THEN RAISE EXCEPTION 'Code expired'; END IF;
  IF r.price < p.min_amount THEN RAISE EXCEPTION 'Order below minimum for this code'; END IF;
  IF p.usage_limit IS NOT NULL THEN
    SELECT count(*) INTO uses FROM public.promo_redemptions WHERE code_id = p.id;
    IF uses >= p.usage_limit THEN RAISE EXCEPTION 'Code fully redeemed'; END IF;
  END IF;
  SELECT count(*) INTO user_uses FROM public.promo_redemptions WHERE code_id = p.id AND user_id = auth.uid();
  IF user_uses >= p.per_user_limit THEN RAISE EXCEPTION 'You have already used this code'; END IF;
  IF p.kind = 'percent' THEN disc := round((r.price * p.value / 100)::numeric, 2);
  ELSE disc := least(p.value, r.price); END IF;
  UPDATE public.service_requests SET discount = disc, promo_code = p.code, points_redeemed = 0 WHERE id = _request_id;
  RETURN disc;
END; $$;
GRANT EXECUTE ON FUNCTION public.apply_promo_to_request(uuid, text) TO authenticated;

-- Redeem loyalty points against a request (1 pt = KES 1)
CREATE OR REPLACE FUNCTION public.redeem_points_on_request(_request_id uuid, _points integer)
RETURNS numeric LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE r RECORD; bal int; disc numeric; base_discount numeric;
BEGIN
  IF _points <= 0 THEN RAISE EXCEPTION 'Points must be positive'; END IF;
  SELECT * INTO r FROM public.service_requests WHERE id = _request_id FOR UPDATE;
  IF NOT FOUND OR r.user_id <> auth.uid() THEN RAISE EXCEPTION 'Request not found'; END IF;
  IF r.paid THEN RAISE EXCEPTION 'Already paid'; END IF;
  IF r.price IS NULL THEN RAISE EXCEPTION 'Price not set yet'; END IF;
  SELECT loyalty_points INTO bal FROM public.profiles WHERE id = auth.uid();
  IF bal < _points THEN RAISE EXCEPTION 'Insufficient points'; END IF;
  base_discount := r.discount - r.points_redeemed;
  disc := least(_points::numeric, greatest(r.price - base_discount, 0));
  IF disc <= 0 THEN RAISE EXCEPTION 'Nothing left to redeem'; END IF;
  UPDATE public.service_requests SET points_redeemed = disc::int, discount = base_discount + disc WHERE id = _request_id;
  RETURN disc;
END; $$;
GRANT EXECUTE ON FUNCTION public.redeem_points_on_request(uuid, integer) TO authenticated;

-- Updated pay_for_service: applies first-time discount, deducts final price, awards points, rewards referrer
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; final_price numeric; pts_award int; ref uuid; ref_bonus numeric := 100; welcome_disc numeric; prior_paid timestamptz;
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 first_paid_at INTO prior_paid FROM public.profiles WHERE id = auth.uid();
  IF prior_paid IS NULL AND r.discount = 0 THEN
    welcome_disc := round((r.price * 0.10)::numeric, 2);
    UPDATE public.service_requests SET discount = welcome_disc, promo_code = COALESCE(r.promo_code, 'WELCOME10') WHERE id = _request_id;
    r.discount := welcome_disc;
    IF r.promo_code IS NULL THEN r.promo_code := 'WELCOME10'; END IF;
  END IF;

  final_price := greatest(r.price - r.discount, 0);

  SELECT * INTO w FROM public.wallets WHERE user_id = auth.uid() FOR UPDATE;
  IF NOT FOUND OR w.balance < final_price THEN RAISE EXCEPTION 'Insufficient balance'; END IF;
  UPDATE public.wallets SET balance = balance - final_price, updated_at = now() WHERE user_id = auth.uid();

  IF r.promo_code IS NOT NULL AND r.promo_code <> 'WELCOME10' THEN
    INSERT INTO public.promo_redemptions (code_id, user_id, request_id, discount)
    SELECT id, auth.uid(), _request_id, r.discount - r.points_redeemed FROM public.promo_codes WHERE lower(code) = lower(r.promo_code) LIMIT 1;
  END IF;

  IF r.points_redeemed > 0 THEN
    UPDATE public.profiles SET loyalty_points = loyalty_points - r.points_redeemed WHERE id = auth.uid();
  END IF;

  pts_award := floor(final_price * 0.05)::int;
  IF pts_award > 0 THEN
    UPDATE public.profiles SET loyalty_points = loyalty_points + pts_award WHERE id = auth.uid();
    UPDATE public.service_requests SET points_earned = pts_award WHERE id = _request_id;
  END IF;

  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;

  IF prior_paid IS NULL THEN
    UPDATE public.profiles SET first_paid_at = now() WHERE id = auth.uid();
    SELECT referred_by INTO ref FROM public.profiles WHERE id = auth.uid();
    IF ref IS NOT NULL THEN
      INSERT INTO public.wallets (user_id, balance) VALUES (ref, ref_bonus)
        ON CONFLICT (user_id) DO UPDATE SET balance = public.wallets.balance + ref_bonus, updated_at = now();
      UPDATE public.referrals SET reward_amount = ref_bonus, rewarded_at = now() WHERE referee_id = auth.uid();
    END IF;
  END IF;
END; $$;
GRANT EXECUTE ON FUNCTION public.pay_for_service(uuid) TO authenticated;