--Creates Schema
CREATE SCHEMA IF NOT EXISTS ods;

--Create Account Table
DROP TABLE IF EXISTS ods.account CASCADE;
CREATE TABLE IF NOT EXISTS ods.account
(
    Name                    varchar(255),
    AccountNumber           varchar(40),
    OwnerId                 varchar(18),
    Site                    varchar(80),
    AccountSource           varchar(255),
    AnnualRevenue           decimal(18, 2),
    CreatedById             varchar(18),
    Jigsaw                  varchar(18),
    Description             Text,
    NumberOfEmployees       decimal(8, 0),
    Fax                     varchar(255),
    Industry                varchar(255),
    LastModifiedById        varchar(18),
    Ownership               varchar(255),
    ParentId                varchar(18),
    Phone                   varchar(50),
    Rating                  varchar(255),
    Sic                     varchar(10),
    SicDesc                 varchar(80),
    TickerSymbol            varchar(20),
    Type                    varchar(255),
    Website                 varchar(255),
    Id                      varchar(18),
    Deleted                 boolean,
    MasterRecordId          varchar(20),
    BillingStreet           varchar(255),
    BillingCity             varchar(255),
    BillingStateProvince    varchar(255),
    BillingZipPostalCode    varchar(255),
    BillingCountry          varchar(255),
    BillingLatitude         decimal(11, 8),
    BillingLongitude        decimal(11, 8),
    BillingGeocodeAccuracy  varchar(255),
    BillingAddress          varchar(255),
    ShippingStreet          varchar(255),
    ShippingCity            varchar(255),
    ShippingStateProvince   varchar(255),
    ShippingZipPostalCode   varchar(255),
    ShippingCountry         varchar(255),
    ShippingLatitude        decimal(11, 8),
    ShippingLongitude       decimal(11, 8),
    ShippingGeocodeAccuracy varchar(255),
    ShippingAddress         varchar(255),
    PhotoURL                varchar(255),
    CreatedDate             timestamp,
    LastModifiedDate        timestamp,
    SystemModstamp          varchar(255),
    LastActivity            varchar(255),
    LastViewedDate          timestamp,
    LastReferencedDate      timestamp,
    DatacomKey              varchar(255),
    JigsawCompanyId         varchar(255),
    SICDescription          Text
) diststyle auto;

--Create Contact Table
DROP TABLE IF EXISTS ods.contact CASCADE;
CREATE TABLE IF NOT EXISTS ods.contact
(
    ContactID                  varchar(18),
    Deleted                    boolean,
    MasterRecordID             varchar(255),
    AccountID                  varchar(255),
    LastName                   varchar(255),
    FirstName                  varchar(255),
    Salutation                 varchar(50),
    FullName                   varchar(255),
    OtherStreet                varchar(255),
    OtherCity                  varchar(255),
    OtherStateProvince         varchar(255),
    OtherZipPostalCode         varchar(20),
    OtherCountry               varchar(255),
    OtherLatitude              decimal(11, 8),
    OtherLongitude             decimal(11, 8),
    OtherGeocodeAccuracy       varchar(100),
    OtherAddress               varchar(255),
    MailingStreet              varchar(255),
    MailingCity                varchar(255),
    MailingStateProvince       varchar(255),
    MailingZipPostalCode       varchar(255),
    MailingCountry             varchar(255),
    MailingLatitude            decimal(11, 8),
    MailingLongitude           decimal(11, 8),
    MailingGeocodeAccuracy     varchar(100),
    MailingAddress             varchar(255),
    BusinessPhone              varchar(50),
    BusinessFax                varchar(50),
    MobilePhone                varchar(50),
    HomePhone                  varchar(50),
    OtherPhone                 varchar(50),
    AsstPhone                  varchar(50),
    ReportsToID                varchar(255),
    Email                      varchar(255),
    Title                      varchar(255),
    Department                 varchar(255),
    AssistantsName             varchar(255),
    LeadSource                 varchar(255),
    Birthdate                  date,
    ContactDescription         varchar(max),
    OwnerID                    varchar(255),
    CreatedDate                timestamp,
    CreatedByID                varchar(255),
    LastModifiedDate           timestamp,
    LastModifiedByID           varchar(255),
    SystemModstamp             timestamp,
    LastActivity               date,
    LastStayinTouchRequestDate timestamp,
    LastStayinTouchSaveDate    timestamp,
    LastViewedDate             timestamp,
    LastReferencedDate         timestamp,
    EmailBouncedReason         varchar(300),
    EmailBouncedDate           timestamp,
    IsEmailBounced             boolean,
    PhotoURL                   varchar(255),
    DatacomKey                 varchar(50),
    JigsawContactID            varchar(50),
    IndividualID               varchar(50)
) diststyle auto;

--Create Case Table
DROP TABLE IF EXISTS ods.case CASCADE;
CREATE TABLE IF NOT EXISTS ods.case
(
    CaseID             varchar(18),
    Deleted            boolean,
    MasterRecordID     varchar(18),
    CaseNumber         varchar(255),
    ContactID          varchar(18),
    AccountID          varchar(18),
    ParentCaseID       varchar(18),
    Name               varchar(255),
    EmailAddress       varchar(255),
    Phone              varchar(50),
    Company            varchar(255),
    CaseType           varchar(255),
    Status             varchar(255),
    CaseReason         varchar(255),
    CaseOrigin         varchar(255),
    Subject            varchar(255),
    Priority           varchar(255),
    Description        varchar(max),
    Closed             boolean,
    ClosedDate         timestamp,
    Escalated          boolean,
    OwnerID            varchar(18),
    CreatedDate        timestamp,
    CreatedByID        varchar(18),
    LastModifiedDate   timestamp,
    LastModifiedByID   varchar(18),
    SystemModstamp     timestamp,
    ContactPhone       varchar(50),
    ContactMobile      varchar(50),
    ContactEmail       varchar(255),
    ContactFax         varchar(50),
    InternalComments   text,
    LastViewedDate     timestamp,
    LastReferencedDate timestamp,
    CustomCreateDate   timestamp
) diststyle auto;

--Create Lead Table
DROP TABLE IF EXISTS ods.lead CASCADE;
CREATE TABLE IF NOT EXISTS ods.lead
(
    LeadID                  varchar(100),
    Deleted                 boolean,
    MasterRecordIdReference varchar(100),
    LastName                varchar(255),
    FirstName               varchar(255),
    Salutation              varchar(18),
    FullName                varchar(255),
    Title                   varchar(255),
    Company                 varchar(255),
    Street                  varchar(255),
    City                    varchar(255),
    StateProvince           varchar(255),
    ZipPostalCode           varchar(255),
    Country                 varchar(255),
    Latitude                decimal(11, 8),
    Longitude               decimal(11, 8),
    GeocodeAccuracy         varchar(255),
    Address                 varchar(255),
    Phone                   varchar(255),
    Email                   varchar(255),
    Website                 varchar(255),
    PhotoURL                varchar(255),
    Description             varchar(255),
    LeadSource              varchar(255),
    Status                  varchar(255),
    Industry                varchar(255),
    Rating                  varchar(255),
    AnnualRevenue           decimal,
    Employees               int,
    OwnerID                 varchar(100),
    Converted               boolean,
    ConvertedDate           date,
    ConvertedAccountID      varchar(100),
    ConvertedContactID      varchar(100),
    ConvertedOpportunityID  varchar(100),
    UnreadByOwner           boolean,
    CreatedDate             timestamp,
    CreatedByID             varchar(100),
    LastModifiedDate        timestamp,
    LastModifiedByID        varchar(100),
    SystemModstamp          timestamp,
    LastActivity            date,
    LastViewedDate          timestamp,
    LastReferencedDate      timestamp,
    DatacomKey              varchar(255),
    JigsawContactID         varchar(255),
    EmailBouncedReason      varchar(255),
    EmailBouncedDate        timestamp,
    IndividualID            varchar(100)
) diststyle auto;

--Create Opportunity Table
DROP TABLE IF EXISTS ods.opportunity CASCADE;
CREATE TABLE IF NOT EXISTS ods.opportunity
(
    OpportunityID         varchar(18),
    Deleted               boolean,
    AccountID             varchar(255),
    Name                  varchar(255),
    Description           varchar(max),
    Stage                 varchar(255),
    Amount                int,
    ProbabilityPercentage decimal(12, 2),
    CloseDate             date,
    OpportunityType       varchar(255),
    NextStep              varchar(255),
    LeadSource            varchar(255),
    Closed                boolean,
    Won                   boolean,
    ForecastCategory      varchar(255),
    CampaignID            varchar(18),
    HasLineItem           boolean,
    PriceBookID           varchar(18),
    OwnerID               varchar(18),
    CreatedDate           date,
    CreatedByID           varchar(18),
    LastModifiedDate      date,
    LastModifiedByID      varchar(18),
    SystemModstamp        timestamp,
    LastActivity          date,
    FiscalQuarter         int,
    FiscalYear            int,
    FiscalPeriod          varchar(255),
    ContactID             varchar(18),
    LastViewedDate        timestamp,
    LastReferencedDate    timestamp,
    HasOpenActivity       boolean,
    HasOverdueTask        boolean,
    OpportunityHistoryID  varchar(18)
) diststyle auto;