
CREATE OR REPLACE FUNCTION public.queue_invoice_sms_for_sales_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_phone text;
  v_name text;
  v_message text;
BEGIN
  SELECT * INTO v_settings FROM public.sms_settings LIMIT 1;
  IF v_settings.id IS NULL OR v_settings.sms_enabled = false OR v_settings.auto_invoice_enabled = false THEN
    RETURN NEW;
  END IF;

  v_phone := COALESCE(NEW.customer_mobile, '');
  v_name := COALESCE(NEW.customer_name, 'Customer');
  IF NEW.customer_id IS NOT NULL THEN
    SELECT COALESCE(mobile, v_phone), COALESCE(name, v_name)
      INTO v_phone, v_name
      FROM public.customers WHERE id = NEW.customer_id;
  END IF;

  IF v_phone IS NULL OR length(trim(v_phone)) = 0 THEN
    RETURN NEW;
  END IF;

  v_message := v_settings.invoice_template;
  v_message := replace(v_message, '{name}', v_name);
  v_message := replace(v_message, '{invoice_no}', COALESCE(NEW.invoice_no, ''));
  v_message := replace(v_message, '{amount}', COALESCE(NEW.total_amount::text, '0'));
  v_message := replace(v_message, '{due}', COALESCE(NEW.due_amount::text, '0'));

  INSERT INTO public.sms_logs (customer_id, phone, message, type, reference_id, status, provider, created_by)
  VALUES (NEW.customer_id, v_phone, v_message, 'invoice', COALESCE(NEW.invoice_no,''), 'pending', v_settings.provider, NEW.created_by);

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_queue_invoice_sms_sales ON public.sales;
CREATE TRIGGER trg_queue_invoice_sms_sales
AFTER INSERT ON public.sales
FOR EACH ROW
EXECUTE FUNCTION public.queue_invoice_sms_for_sales_fn();
