-- ======================
-- SHIPPING: Shipment
-- ======================
CREATE TABLE unified_shipping_shipment (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  order_id             text,
  organization_id      text,
  from_address         jsonb,
  to_address           jsonb,
  packages             jsonb,
  carrier_id           text,
  service_code         text,
  status               text,
  rate_id              text,
  label_id             text,
  tracking_id          text,
  shipped_at           timestamptz,
  rate_amount          numeric,
  rate_currency        text,
  rate_service_name    text,
  rate_estimated_days  numeric,
  rate_estimated_delivery_at timestamptz,
  is_rate_guaranteed   boolean,
  return_address       jsonb,
  return_authorization_number text,
  warehouse_location_id text,
  warehouse_location_name text,
  customs              jsonb,
  is_international     boolean,
  insurance            jsonb,
  special_instructions text[],
  is_signature_required boolean,
  is_adult_signature_required boolean,
  reference_number     text,
  is_return            boolean,
  original_shipment_id text,
  return_reason        text,
  return_type          text,
  carrier_name         text,
  tracking_url         text,
  lineitems            jsonb,
  raw                  jsonb
);
-- ======================
-- SHIPPING: Label
-- ======================
CREATE TABLE unified_shipping_label (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  shipment_id          text,
  tracking_number      text,
  label_url            text,
  label_format         text,
  status               text,
  is_voided            boolean,
  label_cost           numeric,
  label_cost_currency  text,
  rate_id              text,
  service_code         text,
  raw                  jsonb
);
-- ======================
-- SHIPPING: Tracking
-- ======================
CREATE TABLE unified_shipping_tracking (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  shipment_id          text,
  tracking_number      text,
  status               text,
  events               jsonb,
  estimated_delivery   timestamptz,
  actual_delivery_at   timestamptz,
  status_description   text,
  carrier_id           text,
  carrier_status_code  text,
  carrier_status_description text,
  raw                  jsonb
);
-- ======================
-- SHIPPING: Carrier
-- ======================
CREATE TABLE unified_shipping_carrier (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  name                 text,
  code                 text,
  is_active            boolean,
  logo_url             text,
  website_url          text,
  raw                  jsonb
);
