
CREATE OR REPLACE FUNCTION public.queue_invoice_sms_fn()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_customer public.customers%ROWTYPE;
  v_message text;
  v_paid numeric;
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;

  IF NEW.customer_id IS NULL THEN
    RETURN NEW;
  END IF;

  SELECT * INTO v_customer FROM public.customers WHERE id = NEW.customer_id;
  IF v_customer.id IS NULL OR COALESCE(v_customer.mobile, '') = '' THEN
    RETURN NEW;
  END IF;

  v_paid := GREATEST(COALESCE(NEW.total_amount,0) - COALESCE(NEW.due_amount,0), 0);

  v_message := v_settings.invoice_template;
  v_message := replace(v_message, '{name}', COALESCE(v_customer.name, 'Customer'));
  v_message := replace(v_message, '{invoice}', COALESCE(NEW.order_no, ''));
  v_message := replace(v_message, '{invoice_no}', COALESCE(NEW.order_no, ''));
  v_message := replace(v_message, '{bill}', COALESCE(NEW.total_amount::text, '0'));
  v_message := replace(v_message, '{amount}', COALESCE(NEW.total_amount::text, '0'));
  v_message := replace(v_message, '{paid}', v_paid::text);
  v_message := replace(v_message, '{received}', v_paid::text);
  v_message := replace(v_message, '{due}', COALESCE(NEW.due_amount::text, '0'));
  v_message := replace(v_message, '{current_due}', COALESCE(NEW.due_amount::text, '0'));
  v_message := replace(v_message, '{previous_due}', '0');

  INSERT INTO public.sms_logs (customer_id, phone, message, type, reference_id, status, provider, created_by)
  VALUES (v_customer.id, v_customer.mobile, v_message, 'invoice', NEW.order_no, 'pending', v_settings.provider, NEW.created_by);

  RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.queue_invoice_sms_for_sales_fn()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_phone text;
  v_name text;
  v_message text;
  v_paid numeric;
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_paid := GREATEST(COALESCE(NEW.total_amount,0) - COALESCE(NEW.due_amount,0), 0);

  v_message := v_settings.invoice_template;
  v_message := replace(v_message, '{name}', v_name);
  v_message := replace(v_message, '{invoice}', COALESCE(NEW.invoice_no, ''));
  v_message := replace(v_message, '{invoice_no}', COALESCE(NEW.invoice_no, ''));
  v_message := replace(v_message, '{bill}', COALESCE(NEW.total_amount::text, '0'));
  v_message := replace(v_message, '{amount}', COALESCE(NEW.total_amount::text, '0'));
  v_message := replace(v_message, '{paid}', v_paid::text);
  v_message := replace(v_message, '{received}', v_paid::text);
  v_message := replace(v_message, '{due}', COALESCE(NEW.due_amount::text, '0'));
  v_message := replace(v_message, '{current_due}', COALESCE(NEW.due_amount::text, '0'));
  v_message := replace(v_message, '{previous_due}', '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;
$function$;

UPDATE public.sms_settings
SET invoice_template = 'প্রিয় {name}, Invoice:{invoice} মোট:৳{bill} পরিশোধ:৳{paid} বাকি:৳{due} — নিয়ামাহ্';
