erp_naurua/db/migrations/0004_phase1_direct_sales.sql

BEGIN;

-- Enable direct/manual sales orders (e.g. market booth daily capture).
-- Direct orders are identifiable by order_source=direct and external_ref prefix DIR-.

ALTER TABLE sales_order
  ADD COLUMN IF NOT EXISTS order_source TEXT NOT NULL DEFAULT 'wix';

ALTER TABLE sales_order
  ALTER COLUMN party_id DROP NOT NULL;

UPDATE sales_order
SET order_source = 'wix'
WHERE order_source IS NULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'chk_sales_order_source'
  ) THEN
    ALTER TABLE sales_order
      ADD CONSTRAINT chk_sales_order_source
      CHECK (order_source IN ('wix', 'direct'));
  END IF;
END;
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'chk_sales_order_direct_ref_prefix'
  ) THEN
    ALTER TABLE sales_order
      ADD CONSTRAINT chk_sales_order_direct_ref_prefix
      CHECK (order_source <> 'direct' OR external_ref LIKE 'DIR-%');
  END IF;
END;
$$;

CREATE INDEX IF NOT EXISTS idx_sales_order_source ON sales_order(order_source);

CREATE SEQUENCE IF NOT EXISTS seq_sales_order_direct_ref;

CREATE OR REPLACE FUNCTION fn_next_direct_sales_order_ref(
  p_order_date DATE DEFAULT CURRENT_DATE
) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_seq BIGINT;
BEGIN
  v_seq := nextval('seq_sales_order_direct_ref');
  RETURN format(
    'DIR-%s-%s',
    to_char(p_order_date, 'YYYYMMDD'),
    lpad(v_seq::TEXT, 5, '0')
  );
END;
$$;

CREATE OR REPLACE FUNCTION fn_sales_order_assign_direct_ref()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.order_source = 'direct' AND (NEW.external_ref IS NULL OR btrim(NEW.external_ref) = '') THEN
    NEW.external_ref := fn_next_direct_sales_order_ref(COALESCE(NEW.order_date::DATE, CURRENT_DATE));
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sales_order_assign_direct_ref ON sales_order;

CREATE TRIGGER trg_sales_order_assign_direct_ref
BEFORE INSERT OR UPDATE OF order_source, external_ref, order_date ON sales_order
FOR EACH ROW
EXECUTE FUNCTION fn_sales_order_assign_direct_ref();

CREATE OR REPLACE FUNCTION fn_enqueue_sales_order_events()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_key TEXT;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_event_key := format('order.imported:%s', NEW.external_ref);
    PERFORM fn_enqueue_event(
      'order.imported',
      v_event_key,
      'sales_order',
      NEW.id::TEXT,
      jsonb_build_object(
        'orderId', NEW.id,
        'externalRef', NEW.external_ref,
        'orderSource', NEW.order_source,
        'orderStatus', NEW.order_status,
        'paymentStatus', NEW.payment_status,
        'occurredAt', NOW()
      )
    );
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.order_status IS DISTINCT FROM NEW.order_status AND NEW.order_status = 'cancelled' THEN
      v_event_key := format('order.cancelled.full:%s:%s', NEW.external_ref, COALESCE(NEW.cancelled_at, NOW()));
      PERFORM fn_enqueue_event(
        'order.cancelled.full',
        v_event_key,
        'sales_order',
        NEW.id::TEXT,
        jsonb_build_object(
          'orderId', NEW.id,
          'externalRef', NEW.external_ref,
          'orderSource', NEW.order_source,
          'orderStatus', NEW.order_status,
          'cancelledAt', COALESCE(NEW.cancelled_at, NOW()),
          'cancelledReason', NEW.cancelled_reason
        )
      );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION fn_enqueue_partial_cancel_events()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_order_id BIGINT;
  v_external_ref TEXT;
  v_order_source TEXT;
  v_event_key TEXT;
BEGIN
  IF NEW.line_status = 'partially_cancelled' AND OLD.line_status IS DISTINCT FROM NEW.line_status THEN
    SELECT so.id, so.external_ref, so.order_source
    INTO v_order_id, v_external_ref, v_order_source
    FROM sales_order so
    WHERE so.id = NEW.sales_order_id;

    v_event_key := format('order.cancelled.partial:%s:%s:%s', v_external_ref, NEW.id, NEW.qty_cancelled);

    PERFORM fn_enqueue_event(
      'order.cancelled.partial',
      v_event_key,
      'sales_order_line',
      NEW.id::TEXT,
      jsonb_build_object(
        'orderId', v_order_id,
        'externalRef', v_external_ref,
        'orderSource', v_order_source,
        'lineId', NEW.id,
        'lineNo', NEW.line_no,
        'qty', NEW.qty,
        'qtyCancelled', NEW.qty_cancelled,
        'lineStatus', NEW.line_status,
        'occurredAt', NOW()
      )
    );
  END IF;

  RETURN NEW;
END;
$$;

-- Payment methods used by direct/manual sales mask.
INSERT INTO payment_method (code, label)
VALUES
  ('cash', 'Barzahlung'),
  ('paypal', 'PayPal')
ON CONFLICT (code) DO NOTHING;

UPDATE payment_method
SET label = 'Ueberweisung', updated_at = NOW()
WHERE code = 'bank_transfer'
  AND label <> 'Ueberweisung';

COMMIT;