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