Shipping Rules

┌─────────────────────────────────────────────────────┐
│                  Shipment Created                    │
│          shipping_rule_id: "rule_abc123"             │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
              ┌────────────────┐
              │  Statement 1   │
              │  weight < 5kg  │──── YES ──▶ Allocate: AusPost eParcel
              │  postcode = 2* │
              └───────┬────────┘
                      │ NO
                      ▼
              ┌────────────────┐
              │  Statement 2   │
              │  weight < 25kg │──── YES ──▶ Allocate: Startrack Premium
              │  postcode = 2* │
              └───────┬────────┘
                      │ NO
                      ▼
              ┌────────────────┐
              │    DEFAULT     │
              │                │──────────▶ Allocate: TGE Road
              └────────────────┘

After allocation:
  ┌──────────────────────────────────────────────┐
  │  Apply 3-Tier Pricing Cascade                │
  │  base_cost → +master_markup → +sub_markup    │
  │  Store all cost tiers on shipment record     │
  └──────────────────────────────────────────────┘

-- Shipping Rules
CREATE TABLE shipping_rules (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  name            VARCHAR(255) NOT NULL,
  type            VARCHAR(20) NOT NULL,  -- 'condition' | 'service_group' | 'auto_package'
  is_active       BOOLEAN DEFAULT true,
  priority        INTEGER DEFAULT 0,     -- Higher = evaluated first
  
  -- Default action (condition rules)
  default_carrier_id   UUID REFERENCES carrier_connections(id),
  default_service_code VARCHAR(100),
  
  -- Scope
  scope           VARCHAR(20) DEFAULT 'private',  -- 'global' | 'private'
  assigned_to     UUID[],                          -- Sub-Admin org IDs (global rules)
  
  created_by      UUID NOT NULL REFERENCES users(id),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(organization_id, name)
);

-- Rule Statements (conditions + actions)
CREATE TABLE shipping_rule_statements (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id         UUID NOT NULL REFERENCES shipping_rules(id) ON DELETE CASCADE,
  position        INTEGER NOT NULL,       -- Evaluation order
  
  -- Conditions (JSON array of condition objects)
  conditions      JSONB NOT NULL DEFAULT '[]',
  -- Example: [{"field":"total_weight","operator":"less_than","value":5,"unit":"kg"}]
  
  -- Action (condition rules: allocate carrier)
  carrier_id      UUID REFERENCES carrier_connections(id),
  service_code    VARCHAR(100),
  
  -- Action (service group rules: exclude services)
  excluded_services JSONB DEFAULT '[]',
  
  -- Action (auto-package rules: assign package)
  package_id      UUID REFERENCES predefined_packages(id),
  
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Service Group Priority List
CREATE TABLE shipping_rule_services (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id         UUID NOT NULL REFERENCES shipping_rules(id) ON DELETE CASCADE,
  carrier_id      UUID NOT NULL REFERENCES carrier_connections(id),
  service_code    VARCHAR(100) NOT NULL,
  priority        INTEGER NOT NULL,       -- 1 = highest priority
  
  UNIQUE(rule_id, carrier_id, service_code)
);

-- Rule Match Log (for analytics)
CREATE TABLE shipping_rule_matches (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id         UUID NOT NULL REFERENCES shipping_rules(id),
  shipment_id     UUID NOT NULL REFERENCES shipments(id),
  statement_pos   INTEGER,                -- Which statement matched (NULL = default)
  matched_at      TIMESTAMPTZ DEFAULT NOW()
);