-- ======================
-- ATS: Activity
-- ======================
CREATE TABLE "unified_ats_activity" (
  "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,
  "candidate_id"           VARCHAR,
  "application_id"         VARCHAR,
  "job_id"                 VARCHAR,
  "interview_id"           VARCHAR,
  "document_ids"           VARIANT,
  "title"                  VARCHAR,
  "description"            VARCHAR,
  "is_private"             BOOLEAN,
  "user_ids"               VARIANT,
  "type"                   VARCHAR,
  "from"                   VARIANT,
  "to"                     VARIANT,
  "cc"                     VARIANT,
  "bcc"                    VARIANT,
  "sub_type"               VARCHAR,
  "company_id"             VARCHAR,
  "metadata"               VARIANT,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Candidate
-- ======================
CREATE TABLE "unified_ats_candidate" (
  "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,
  "emails"                 VARIANT,
  "title"                  VARCHAR,
  "telephones"             VARIANT,
  "company_name"           VARCHAR,
  "image_url"              VARCHAR,
  "tags"                   VARIANT,
  "address"                VARIANT,
  "external_identifier"    VARCHAR,
  "link_urls"              VARIANT,
  "origin"                 VARCHAR,
  "company_id"             VARCHAR,
  "sources"                VARIANT,
  "date_of_birth"          TIMESTAMP_NTZ,
  "user_id"                VARCHAR,
  "user_ids"               VARIANT,
  "web_url"                VARCHAR,
  "experiences"            VARIANT,
  "education"              VARIANT,
  "skills"                 VARIANT,
  "job_ids"                VARIANT,
  "metadata"               VARIANT,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Job
-- ======================
CREATE TABLE "unified_ats_job" (
  "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,
  "recruiter_ids"          VARIANT,
  "hiring_manager_ids"     VARIANT,
  "hiring_managers"        VARIANT,
  "status"                 VARCHAR,
  "closed_at"              TIMESTAMP_NTZ,
  "addresses"              VARIANT,
  "compensation"           VARIANT,
  "employment_type"        VARCHAR,
  "remote"                 BOOLEAN,
  "language_locale"        VARCHAR,
  "public_job_urls"        VARIANT,
  "number_of_openings"     FLOAT,
  "company_id"             VARCHAR,
  "questions"              VARIANT,
  "postings"               VARIANT,
  "groups"                 VARIANT,
  "openings"               VARIANT,
  "minimum_experience_years" FLOAT,
  "minimum_degree"         VARCHAR,
  "skills"                 VARIANT,
  "metadata"               VARIANT,
  "user_id"                VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Interview
-- ======================
CREATE TABLE "unified_ats_interview" (
  "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,
  "candidate_id"           VARCHAR,
  "job_id"                 VARCHAR,
  "application_id"         VARCHAR,
  "user_ids"               VARIANT,
  "status"                 VARCHAR,
  "start_at"               TIMESTAMP_NTZ,
  "end_at"                 TIMESTAMP_NTZ,
  "location"               VARCHAR,
  "external_event_xref"    VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Document
-- ======================
CREATE TABLE "unified_ats_document" (
  "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,
  "document_url"           VARCHAR,
  "document_data"          VARCHAR,
  "filename"               VARCHAR,
  "type"                   VARCHAR,
  "candidate_id"           VARCHAR,
  "application_id"         VARCHAR,
  "job_id"                 VARCHAR,
  "user_id"                VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Applicationstatus
-- ======================
CREATE TABLE "unified_ats_applicationstatu" (
  "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,
  "job_id"                 VARCHAR,
  "status"                 VARCHAR,
  "original_status"        VARCHAR,
  "description"            VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Application
-- ======================
CREATE TABLE "unified_ats_application" (
  "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,
  "candidate_id"           VARCHAR,
  "job_id"                 VARCHAR,
  "created_at"             TIMESTAMP_NTZ,
  "updated_at"             TIMESTAMP_NTZ,
  "applied_at"             TIMESTAMP_NTZ,
  "hired_at"               TIMESTAMP_NTZ,
  "rejected_at"            TIMESTAMP_NTZ,
  "rejected_reason"        VARCHAR,
  "source"                 VARCHAR,
  "status"                 VARCHAR,
  "original_status"        VARCHAR,
  "answers"                VARIANT,
  "offers"                 VARIANT,
  "user_id"                VARCHAR,
  "metadata"               VARIANT,
  "original_substatus"     VARCHAR,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Scorecard
-- ======================
CREATE TABLE "unified_ats_scorecard" (
  "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,
  "application_id"         VARCHAR,
  "interviewer_id"         VARCHAR,
  "interview_id"           VARCHAR,
  "candidate_id"           VARCHAR,
  "job_id"                 VARCHAR,
  "recommendation"         VARCHAR,
  "comment"                VARCHAR,
  "questions"              VARIANT,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
-- ======================
-- ATS: Company
-- ======================
CREATE TABLE "unified_ats_company" (
  "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,
  "address"                VARIANT,
  "website_url"            VARCHAR,
  "phone"                  VARCHAR,
  "parent_id"              VARCHAR,
  "recruiter_ids"          VARIANT,
  "metadata"               VARIANT,
  "raw"                    VARIANT,
  UNIQUE ("unified_connection_id", "unified_id")
);
