SELECT 
    d.PROPERTY_AMOUNT AS AMOUNT,
    d.PROPERTY_HS_CLOSED_DEAL_CLOSE_DATE AS CLOSE_DATE,
    d.PROPERTY_HS_CREATEDATE AS CREATED_DATE,
    CASE WHEN d.PROPERTY_HS_NUM_OF_ASSOCIATED_LINE_ITEMS > 0 THEN TRUE ELSE FALSE END AS HAS_OPPORTUNITY_LINE_ITEM,
    d.PROPERTY_HS_IS_CLOSED AS IS_CLOSED,
    d.IS_DELETED,
    d.PROPERTY_HS_IS_CLOSED_WON AS IS_WON,
    d.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    d.PROPERTY_DEALNAME AS NAME,
    d.PROPERTY_HS_DEAL_STAGE_PROBABILITY AS PROBABILITY,
    s.LABEL AS STAGE_NAME,
    d._FIVETRAN_DELETED,
    s._FIVETRAN_SYNCED
FROM 
    stg_DEAL d
JOIN 
    stg_DEAL_PIPELINE_STAGE s ON d.DEAL_PIPELINE_STAGE_ID = s.STAGE_ID
WHERE 
    d._FIVETRAN_DELETED = FALSE
    AND s._FIVETRAN_DELETED = FALSE

SELECT 
    stg_COMPANY.PROPERTY_HS_OBJECT_SOURCE AS ACCOUNT_SOURCE,
    stg_COMPANY.PROPERTY_ANNUALREVENUE AS ANNUAL_REVENUE,
    stg_COMPANY.PROPERTY_CITY AS BILLING_CITY,
    stg_COMPANY.PROPERTY_COUNTRY AS BILLING_COUNTRY,
    stg_COMPANY.PROPERTY_ZIP AS BILLING_POSTAL_CODE,
    stg_COMPANY.PROPERTY_STATE AS BILLING_STATE,
    stg_COMPANY.PROPERTY_CREATEDATE AS CREATED_DATE,
    stg_COMPANY.PROPERTY_DESCRIPTION AS DESCRIPTION,
    stg_COMPANY.PROPERTY_INDUSTRY AS INDUSTRY,
    stg_COMPANY.IS_DELETED,
    stg_COMPANY.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    stg_COMPANY.PROPERTY_NAME AS NAME,
    stg_COMPANY.PROPERTY_NUMBEROFEMPLOYEES AS NUMBER_OF_EMPLOYEES,
    stg_COMPANY.PROPERTY_PHONE AS PHONE,
    stg_COMPANY.PROPERTY_CITY AS SHIPPING_CITY,
    stg_COMPANY.PROPERTY_COUNTRY AS SHIPPING_COUNTRY,
    stg_COMPANY.PROPERTY_ZIP AS SHIPPING_POSTAL_CODE,
    stg_COMPANY.PROPERTY_STATE AS SHIPPING_STATE,
    stg_COMPANY.PROPERTY_INDUSTRY AS TYPE,
    stg_COMPANY.PROPERTY_WEBSITE AS WEBSITE,
    stg_COMPANY._FIVETRAN_DELETED,
    stg_COMPANY._FIVETRAN_SYNCED
FROM 
    stg_COMPANY
LEFT JOIN 
    stg_OWNER ON stg_COMPANY.PROPERTY_HUBSPOT_OWNER_ID = stg_OWNER.OWNER_ID

SELECT 
    PROPERTY_CREATEDATE AS CREATED_DATE,
    ID,
    IS_DELETED,
    PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    _FIVETRAN_DELETED,
    _FIVETRAN_SYNCED
FROM 
    stg_COMPANY

SELECT 
    CREATED_AT AS CREATED_DATE,
    IS_CLOSED,
    UPDATED_AT AS LAST_MODIFIED_DATE,
    LABEL AS MASTER_LABEL,
    DISPLAY_ORDER AS SORT_ORDER,
    _FIVETRAN_DELETED
FROM 
    stg_TICKET_PIPELINE_STAGE
WHERE 
    1=1  -- This is a placeholder. You might want to add conditions here, e.g., to filter out deleted records

SELECT 
    stg_CONTACT.PROPERTY_HS_OBJECT_SOURCE AS CONTACT_SOURCE,
    stg_CONTACT.PROPERTY_CREATEDATE AS CREATED_DATE,
    stg_COMPANY.PROPERTY_DESCRIPTION AS DESCRIPTION,
    stg_CONTACT.PROPERTY_EMAIL AS EMAIL,
    stg_CONTACT.PROPERTY_FIRSTNAME AS FIRST_NAME,
    stg_CONTACT.IS_DELETED,
    stg_CONTACT.PROPERTY_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    stg_CONTACT.PROPERTY_LASTNAME AS LAST_NAME,
    stg_CONTACT.PROPERTY_HS_LATEST_SOURCE AS LEAD_SOURCE,
    stg_COMPANY.PROPERTY_CITY AS MAILING_CITY,
    stg_COMPANY.PROPERTY_COUNTRY AS MAILING_COUNTRY,
    stg_COMPANY.PROPERTY_ZIP AS MAILING_POSTAL_CODE,
    stg_COMPANY.PROPERTY_STATE AS MAILING_STATE,
    stg_COMPANY.PROPERTY_ADDRESS AS MAILING_STREET,
    stg_CONTACT.PROPERTY_MOBILEPHONE AS MOBILE_PHONE,
    CONCAT(stg_CONTACT.PROPERTY_FIRSTNAME, ' ', stg_CONTACT.PROPERTY_LASTNAME) AS NAME,
    stg_COMPANY.PROPERTY_CITY AS OTHER_CITY,
    stg_COMPANY.PROPERTY_COUNTRY AS OTHER_COUNTRY,
    stg_COMPANY.PROPERTY_ZIP AS OTHER_POSTAL_CODE,
    stg_COMPANY.PROPERTY_STATE AS OTHER_STATE,
    stg_COMPANY.PROPERTY_ADDRESS_2 AS OTHER_STREET,
    stg_CONTACT.PROPERTY_HS_CALCULATED_MOBILE_NUMBER AS PHONE,
    stg_CONTACT._FIVETRAN_SYNCED AS SYSTEM_MODSTAMP,
    stg_CONTACT.PROPERTY_JOBTITLE AS TITLE,
    stg_CONTACT._FIVETRAN_DELETED,
    stg_CONTACT._FIVETRAN_SYNCED
FROM 
    stg_CONTACT
LEFT JOIN 
    stg_OWNER ON stg_CONTACT.PROPERTY_HUBSPOT_OWNER_ID = stg_OWNER.OWNER_ID
LEFT JOIN 
    stg_COMPANY ON stg_CONTACT.ID = stg_COMPANY.ID
WHERE 
    stg_CONTACT._FIVETRAN_DELETED = FALSE

SELECT 
    stg.PROPERTY_CITY AS CITY,
    stg.PROPERTY_COUNTRY AS COUNTRY,
    stg.PROPERTY_CREATEDATE AS CREATED_DATE,
    stg.IS_DELETED,
    stg.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    stg.PROPERTY_NAME AS NAME,
    stg.ID AS PARENT_ID,
    stg.PROPERTY_ZIP AS POSTAL_CODE,
    stg.PROPERTY_STATE AS STATE,
    stg.PROPERTY_ADDRESS AS STREET,
    stg._FIVETRAN_SYNCED
FROM 
    stg_COMPANY stg

SELECT 
    stg_CONTACT.PROPERTY_CREATEDATE AS CREATED_DATE,
    stg_CONTACT.PROPERTY_EMAIL AS EMAIL_ADDRESS,
    stg_CONTACT.PROPERTY_HS_EMAIL_DOMAIN AS EMAIL_DOMAIN,
    stg_CONTACT.IS_DELETED,
    stg_CONTACT.PROPERTY_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    stg_CONTACT._FIVETRAN_DELETED,
    stg_CONTACT._FIVETRAN_SYNCED
FROM 
    stg_CONTACT
LEFT JOIN 
    stg_OWNER ON stg_CONTACT.PROPERTY_HUBSPOT_OWNER_ID = stg_OWNER.OWNER_ID

SELECT 
    c.PROPERTY_CREATEDATE AS CREATED_DATE,
    c.IS_DELETED,
    c.PROPERTY_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    CONCAT(o.FIRST_NAME, ' ', o.LAST_NAME) AS NAME,
    e.PROPERTY_HS_TASK_STATUS AS STATUS,
    c._FIVETRAN_DELETED,
    c._FIVETRAN_SYNCED
FROM 
    stg_ENGAGEMENT_TASK e
LEFT JOIN 
    stg_OWNER o ON e.PROPERTY_HUBSPOT_OWNER_ID = o.OWNER_ID
LEFT JOIN 
    stg_CONTACT c ON e.PROPERTY_HS_TASK_FOR_OBJECT_ID = c.ID
WHERE 
    c._FIVETRAN_DELETED IS NOT TRUE

SELECT 
    c.PROPERTY_CREATEDATE AS CREATED_DATE,
    c.ID,
    c.IS_DELETED,
    c.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    c.PROPERTY_NAME AS NAME,
    c.PROPERTY_HS_OBJECT_ID AS PARTY_ID,
    c._FIVETRAN_DELETED,
    c._FIVETRAN_SYNCED
FROM 
    stg_COMPANY c
WHERE 
    c._FIVETRAN_DELETED = FALSE

SELECT 
    c.PROPERTY_CREATEDATE AS CREATED_DATE,
    c.PROPERTY_FIRSTNAME AS FIRST_NAME,
    c.IS_DELETED,
    c.PROPERTY_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    c.PROPERTY_LASTNAME AS LAST_NAME,
    c.PROPERTY_FIRSTNAME || ' ' || c.PROPERTY_LASTNAME AS NAME,
    c._FIVETRAN_DELETED,
    c._FIVETRAN_SYNCED
FROM 
    stg_CONTACT c
LEFT JOIN 
    stg_OWNER o ON c.PROPERTY_HUBSPOT_OWNER_ID = o.OWNER_ID
WHERE 
    c._FIVETRAN_DELETED IS NOT TRUE

SELECT 
    stg_CONTACT.PROPERTY_CREATEDATE AS CREATED_DATE,
    stg_CONTACT.PROPERTY_EMAIL AS EMAIL_ADDRESS,
    stg_CONTACT.IS_DELETED,
    stg_CONTACT.PROPERTY_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    CONCAT(stg_CONTACT.PROPERTY_FIRSTNAME, ' ', stg_CONTACT.PROPERTY_LASTNAME) AS NAME,
    stg_CONTACT._FIVETRAN_DELETED,
    stg_CONTACT._FIVETRAN_SYNCED
FROM 
    stg_CONTACT

SELECT 
    CREATED_AT AS CREATED_DATE,
    PROBABILITY AS DEFAULT_PROBABILITY,
    CASE WHEN CLOSED_WON = true THEN true ELSE false END AS IS_CLOSED,
    CLOSED_WON AS IS_WON,
    UPDATED_AT AS LAST_MODIFIED_DATE,
    LABEL AS MASTER_LABEL,
    DISPLAY_ORDER AS SORT_ORDER,
    _FIVETRAN_DELETED,
    _FIVETRAN_SYNCED
FROM 
    stg_DEAL_PIPELINE_STAGE

SELECT 
    et.PROPERTY_HS_CREATEDATE AS CREATED_DATE,
    et._FIVETRAN_DELETED AS IS_DELETED,
    et.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    et.PROPERTY_HS_OBJECT_ID AS TARGET_OBJECT_ID
FROM 
    stg_ENGAGEMENT_TASK et
WHERE 
    1=1  -- This is a placeholder condition that always evaluates to true.
          -- It's sometimes used to make it easier to add actual conditions later if needed.

SELECT 
    PROPERTY_CREATEDATE AS CREATED_DATE,
    ID,
    IS_DELETED,
    PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    PROPERTY_NAME AS NAME,
    _FIVETRAN_DELETED,
    _FIVETRAN_SYNCED
FROM 
    stg_COMPANY

SELECT 
    COALESCE(c.ID, ct.PROPERTY_HS_OBJECT_ID) AS ID,
    ct.PROPERTY_HS_OBJECT_ID AS KQ_ID_C,
    ct.PROPERTY_CREATEDATE AS SSOT_CREATED_DATE_C,
    ct.PROPERTY_HS_OBJECT_SOURCE AS SSOT_DATA_SOURCE_ID_C,
    ct.PROPERTY_HS_OBJECT_ID AS SSOT_DATA_SOURCE_OBJECT_ID_C,
    ct.PROPERTY_HS_OBJECT_ID AS SSOT_ID_C,
    ct.PROPERTY_LASTMODIFIEDDATE AS SSOT_LAST_MODIFIED_DATE_C,
    ct.PROPERTY_JOBTITLE AS SSOT_TITLE_C,
    ct._FIVETRAN_DELETED,
    ct._FIVETRAN_SYNCED
FROM 
    stg_CONTACT ct
LEFT JOIN 
    stg_COMPANY c ON ct.COMPANY_ID = c.ID

SELECT 
    PROPERTY_HS_OBJECT_SOURCE AS SSOT_ACCOUNT_SOURCE_C,
    PROPERTY_ANNUALREVENUE AS SSOT_ANNUAL_REVENUE_AMOUNT_C,
    PROPERTY_CREATEDATE AS SSOT_CREATED_DATE_C,
    PROPERTY_HS_OBJECT_SOURCE_ID AS SSOT_DATA_SOURCE_ID_C,
    PROPERTY_HS_OBJECT_ID AS SSOT_DATA_SOURCE_OBJECT_ID_C,
    PROPERTY_DESCRIPTION AS SSOT_DESCRIPTION_C,
    PROPERTY_NUMBEROFEMPLOYEES AS SSOT_EMPLOYEE_COUNT_C,
    PROPERTY_HS_LASTMODIFIEDDATE AS SSOT_LAST_MODIFIED_DATE_C,
    PROPERTY_NAME AS SSOT_NAME_C,
    PROPERTY_INDUSTRY AS SSOT_PRIMARY_INDUSTRY_C,
    PROPERTY_WEBSITE AS SSOT_WEBSITE_ADDR_C,
    _FIVETRAN_DELETED,
    _FIVETRAN_SYNCED
FROM 
    stg_COMPANY

SELECT 
    stg_COMPANY.ID AS KQ_PARTY_ID_C,
    stg_COMPANY.PORTAL_ID AS SSOT_DATA_SOURCE_ID_C,
    stg_COMPANY.ID AS SSOT_DATA_SOURCE_OBJECT_ID_C,
    stg_COMPANY.PROPERTY_SL_EMAIL_ADDRESS_FIRST AS SSOT_EMAIL_ADDRESS_C,
    stg_COMPANY.PROPERTY_HS_OBJECT_ID AS SSOT_ID_C,
    stg_COMPANY._FIVETRAN_DELETED,
    stg_COMPANY._FIVETRAN_SYNCED
FROM 
    stg_COMPANY

SELECT 
    c.PROPERTY_CREATEDATE AS SSOT_CREATED_DATE_C,
    c.PROPERTY_HS_OBJECT_SOURCE_ID AS SSOT_DATA_SOURCE_ID_C,
    c.PROPERTY_HS_OBJECT_ID AS SSOT_DATA_SOURCE_OBJECT_ID_C,
    c.PROPERTY_FIRSTNAME AS SSOT_FIRST_NAME_C,
    c.PROPERTY_HS_OBJECT_ID AS SSOT_ID_C,
    c.PROPERTY_LASTMODIFIEDDATE AS SSOT_LAST_MODIFIED_DATE_C,
    c.PROPERTY_LASTNAME AS SSOT_LAST_NAME_C,
    CONCAT(c.PROPERTY_FIRSTNAME, ' ', c.PROPERTY_LASTNAME) AS SSOT_PERSON_NAME_C,
    c.PROPERTY_JOBTITLE AS SSOT_TITLE_NAME_C,
    c._FIVETRAN_DELETED,
    c._FIVETRAN_SYNCED
FROM 
    stg_CONTACT c
LEFT JOIN 
    stg_OWNER o ON c.PROPERTY_HUBSPOT_OWNER_ID = o.OWNER_ID
WHERE 
    c._FIVETRAN_DELETED = FALSE

SELECT 
    et.PROPERTY_HS_CREATEDATE AS CREATED_DATE,
    et.PROPERTY_HS_TASK_IS_COMPLETED AS IS_CLOSED,
    et._FIVETRAN_DELETED AS IS_DELETED,
    CASE WHEN et.PROPERTY_HS_TASK_PRIORITY = 'HIGH' THEN TRUE ELSE FALSE END AS IS_HIGH_PRIORITY,
    et.PROPERTY_HS_TASK_SEND_DEFAULT_REMINDER AS IS_REMINDER_SET,
    et.PROPERTY_HS_LASTMODIFIEDDATE AS LAST_MODIFIED_DATE,
    et.PROPERTY_HS_TASK_PRIORITY AS PRIORITY,
    et.PROPERTY_HS_TASK_STATUS AS STATUS,
    et.PROPERTY_HS_TASK_SUBJECT AS SUBJECT,
    et.PROPERTY_HS_TASK_TYPE AS TASK_SUBTYPE,
    et._FIVETRAN_DELETED,
    et._FIVETRAN_SYNCED
FROM 
    stg_ENGAGEMENT_TASK et
LEFT JOIN 
    stg_ENGAGEMENT e ON et.ENGAGEMENT_ID = e.ID
LEFT JOIN 
    stg_OWNER o ON et.PROPERTY_HUBSPOT_OWNER_ID = o.OWNER_ID

SELECT 
    et.CREATED_TIME AS CREATED_DATE,
    et.ID,
    et._FIVETRAN_DELETED AS IS_DELETED,
    et.LAST_UPDATED AS LAST_MODIFIED_DATE,
    e.TYPE,
    et._FIVETRAN_DELETED,
    et._FIVETRAN_SYNCED
FROM 
    stg_ENGAGEMENT_TASK et
LEFT JOIN 
    stg_ENGAGEMENT e ON et.ENGAGEMENT_ID = e.ID

SELECT 
    stg_CONTACT.PROPERTY_CREATEDATE AS SSOT_CREATED_DATE_C,
    stg_CONTACT.PROPERTY_FIRSTNAME AS SSOT_FIRST_NAME_C,
    stg_CONTACT.PROPERTY_LASTMODIFIEDDATE AS SSOT_LAST_MODIFIED_DATE_C,
    stg_CONTACT.PROPERTY_LASTNAME AS SSOT_LAST_NAME_C,
    CONCAT(stg_CONTACT.PROPERTY_FIRSTNAME, ' ', stg_CONTACT.PROPERTY_LASTNAME) AS SSOT_PERSON_NAME_C,
    stg_CONTACT.PROPERTY_JOBTITLE AS SSOT_TITLE_NAME_C,
    stg_CONTACT._FIVETRAN_DELETED,
    stg_CONTACT._FIVETRAN_SYNCED
FROM 
    stg_CONTACT
LEFT JOIN 
    stg_OWNER ON stg_CONTACT.PROPERTY_HUBSPOT_OWNER_ID = stg_OWNER.OWNER_ID

SELECT 
    o.CREATED_AT AS CREATED_DATE,
    o.EMAIL,
    o.FIRST_NAME,
    o.IS_ACTIVE,
    o.UPDATED_AT AS LAST_MODIFIED_DATE,
    o.LAST_NAME,
    COALESCE(u._FIVETRAN_DELETED, FALSE) AS _FIVETRAN_DELETED,
    o._FIVETRAN_SYNCED
FROM 
    stg_USERS u
JOIN 
    stg_OWNER o ON u.ID = o.OWNER_ID

SELECT 
    o.CREATED_AT AS CREATED_DATE,
    o.EMAIL,
    o.UPDATED_AT AS LAST_MODIFIED_DATE,
    u._FIVETRAN_DELETED,
    o._FIVETRAN_SYNCED
FROM 
    stg_USERS u
JOIN 
    stg_OWNER o ON u.ID = o.OWNER_ID

SELECT 
    o.CREATED_AT AS CREATED_DATE,
    o.EMAIL AS EXTERNAL_EMAIL,
    o.FIRST_NAME AS EXTERNAL_FIRST_NAME,
    o.LAST_NAME AS EXTERNAL_LAST_NAME,
    CASE WHEN o.IS_ACTIVE = TRUE THEN FALSE ELSE TRUE END AS IS_DELETED,
    o.UPDATED_AT AS LAST_MODIFIED_DATE,
    CONCAT(o.FIRST_NAME, ' ', o.LAST_NAME) AS NAME,
    o._FIVETRAN_DELETED,
    o._FIVETRAN_SYNCED
FROM 
    stg_OWNER o
JOIN 
    stg_USERS u ON o.EMAIL = u.EMAIL
WHERE 
    o._FIVETRAN_DELETED = FALSE