-- ======================
-- MARTECH: List
-- ======================
CREATE TABLE "unified_martech_list" (
  "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,
  "description"            VARCHAR,
  "start_at"               TIMESTAMP_NTZ,
  "end_at"                 TIMESTAMP_NTZ,
  "user_id"                VARCHAR,
  "is_active"              BOOLEAN,
  "address"                VARIANT,
  "subject"                VARCHAR,
  "sender_name"            VARCHAR,
  "sender_email"           VARCHAR,
  "language"               VARCHAR,
  "sender_company"         VARCHAR,
  "sender_phone"           VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- MARTECH: Member
-- ======================
CREATE TABLE "unified_martech_member" (
  "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,
  "first_name"             VARCHAR,
  "last_name"              VARCHAR,
  "company"                VARCHAR,
  "emails"                 VARIANT,
  "list_ids"               VARIANT,
  "tags"                   VARIANT,
  "status"                 VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- MARTECH: Campaign
-- ======================
CREATE TABLE "unified_martech_campaign" (
  "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,
  "type"                   VARCHAR,
  "status"                 VARCHAR,
  "list_ids"               VARIANT,
  "subject_line"           VARCHAR,
  "preview_text"           VARCHAR,
  "from_name"              VARCHAR,
  "from_email"             VARCHAR,
  "reply_to_email"         VARCHAR,
  "send_at"                TIMESTAMP_NTZ,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- MARTECH: Report
-- ======================
CREATE TABLE "unified_martech_report" (
  "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,
  "start_at"               TIMESTAMP_NTZ,
  "end_at"                 TIMESTAMP_NTZ,
  "campaign_id"            VARCHAR,
  "list_id"                VARCHAR,
  "emails_sent"            FLOAT,
  "abuse_reports"          FLOAT,
  "unsubscribed"           FLOAT,
  "sent_at"                TIMESTAMP_NTZ,
  "hard_bounces"           FLOAT,
  "soft_bounces"           FLOAT,
  "syntax_errors"          FLOAT,
  "forwards_count"         FLOAT,
  "forwards_opens"         FLOAT,
  "opens_total"            FLOAT,
  "unique_opens"           FLOAT,
  "open_rate"              FLOAT,
  "last_open_at"           TIMESTAMP_NTZ,
  "clicks_total"           FLOAT,
  "unique_clicks"          FLOAT,
  "unique_subscriber_clicks" FLOAT,
  "click_rate"             FLOAT,
  "last_click_at"          TIMESTAMP_NTZ,
  "links"                  VARIANT,
  "human_opens_total"      FLOAT,
  "human_unique_opens"     FLOAT,
  "human_open_rate"        FLOAT,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
