
-- 1. sms_settings (singleton row keyed by id)
CREATE TABLE public.sms_settings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  provider text NOT NULL DEFAULT 'ssl_wireless',
  api_token text NOT NULL DEFAULT '',
  api_secret text NOT NULL DEFAULT '',
  sender_id text NOT NULL DEFAULT '',
  base_url text NOT NULL DEFAULT 'https://smsplus.sslwireless.com/api/v3/send-sms',
  sms_enabled boolean NOT NULL DEFAULT false,
  auto_invoice_enabled boolean NOT NULL DEFAULT false,
  invoice_template text NOT NULL DEFAULT 'Dear {name}, your invoice {invoice_no} of Tk {amount} has been created. Thank you for shopping with us.',
  payment_template text NOT NULL DEFAULT 'Dear {name}, we have received Tk {amount} against invoice {invoice_no}. Due: Tk {due}. Thank you.',
  due_template text NOT NULL DEFAULT 'Dear {name}, your pending due is Tk {due}. Please settle at your earliest convenience.',
  delivery_template text NOT NULL DEFAULT 'Dear {name}, your order {invoice_no} status is now: {status}.',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.sms_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view sms settings"
  ON public.sms_settings FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Admins can insert sms settings"
  ON public.sms_settings FOR INSERT TO authenticated
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can update sms settings"
  ON public.sms_settings FOR UPDATE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete sms settings"
  ON public.sms_settings FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE TRIGGER update_sms_settings_updated_at
  BEFORE UPDATE ON public.sms_settings
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- seed singleton row
INSERT INTO public.sms_settings (provider) VALUES ('ssl_wireless');

-- 2. sms_logs
CREATE TABLE public.sms_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid,
  phone text NOT NULL,
  message text NOT NULL,
  type text NOT NULL DEFAULT 'manual', -- manual, invoice, payment, due, delivery
  reference_id text DEFAULT '',
  status text NOT NULL DEFAULT 'pending', -- pending, sent, failed
  provider text NOT NULL DEFAULT 'ssl_wireless',
  provider_response jsonb DEFAULT '{}'::jsonb,
  error_message text DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  sent_at timestamptz
);

CREATE INDEX idx_sms_logs_customer_created ON public.sms_logs (customer_id, created_at DESC);
CREATE INDEX idx_sms_logs_status ON public.sms_logs (status, created_at DESC);

ALTER TABLE public.sms_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view sms logs"
  ON public.sms_logs FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Staff can insert sms logs"
  ON public.sms_logs FOR INSERT TO authenticated
  WITH CHECK (is_staff(auth.uid()));

CREATE POLICY "Staff can update sms logs"
  ON public.sms_logs FOR UPDATE TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Admins can delete sms logs"
  ON public.sms_logs FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

-- 3. Auto-SMS trigger on sales_orders insert
CREATE OR REPLACE FUNCTION public.queue_invoice_sms_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_settings public.sms_settings%ROWTYPE;
  v_customer public.customers%ROWTYPE;
  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;

  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 := v_settings.invoice_template;
  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'));

  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;
$$;

CREATE TRIGGER trg_queue_invoice_sms
  AFTER INSERT ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.queue_invoice_sms_fn();
