
-- Add auto flags & cron-related columns to sms_settings
ALTER TABLE public.sms_settings
  ADD COLUMN IF NOT EXISTS auto_payment_enabled boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS auto_delivery_enabled boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS auto_due_enabled boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS due_min_amount numeric NOT NULL DEFAULT 1;

-- Payment SMS trigger: fires when a payment/due_collection transaction is created
CREATE OR REPLACE FUNCTION public.queue_payment_sms_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_customer public.customers%ROWTYPE;
  v_message text;
BEGIN
  IF NEW.type NOT IN ('payment','due_collection','collection','received') THEN
    RETURN NEW;
  END IF;

  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_payment_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_message := COALESCE(v_settings.payment_template, 'Payment received: {amount}');
  v_message := replace(v_message, '{name}', COALESCE(v_customer.name,'Customer'));
  v_message := replace(v_message, '{amount}', COALESCE(NEW.amount::text,'0'));
  v_message := replace(v_message, '{invoice_no}', COALESCE(NEW.reference_id,''));
  v_message := replace(v_message, '{due}', COALESCE(v_customer.total_due::text,'0'));

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

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_queue_payment_sms ON public.customer_transactions;
CREATE TRIGGER trg_queue_payment_sms
AFTER INSERT ON public.customer_transactions
FOR EACH ROW EXECUTE FUNCTION public.queue_payment_sms_fn();

-- Delivery SMS trigger: fires when sales_orders.status changes to delivered/partial_delivered
CREATE OR REPLACE FUNCTION public.queue_delivery_sms_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_customer public.customers%ROWTYPE;
  v_message text;
BEGIN
  IF NEW.status NOT IN ('delivered','partial_delivered') THEN
    RETURN NEW;
  END IF;
  IF OLD.status IS NOT DISTINCT FROM NEW.status THEN
    RETURN NEW;
  END IF;

  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_delivery_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_message := COALESCE(v_settings.delivery_template, 'Your order {invoice_no} has been delivered.');
  v_message := replace(v_message, '{name}', COALESCE(v_customer.name,'Customer'));
  v_message := replace(v_message, '{invoice_no}', COALESCE(NEW.order_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'));
  v_message := replace(v_message, '{status}', COALESCE(NEW.status,''));

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

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_queue_delivery_sms ON public.sales_orders;
CREATE TRIGGER trg_queue_delivery_sms
AFTER UPDATE ON public.sales_orders
FOR EACH ROW EXECUTE FUNCTION public.queue_delivery_sms_fn();

-- Make sure invoice trigger is attached (was created earlier but may be missing)
DROP TRIGGER IF EXISTS trg_queue_invoice_sms ON public.sales_orders;
CREATE TRIGGER trg_queue_invoice_sms
AFTER INSERT ON public.sales_orders
FOR EACH ROW EXECUTE FUNCTION public.queue_invoice_sms_fn();
