WITH companies AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_hubspot.stg_hubspot__company
), engagements AS (
  SELECT
    *
  FROM TEST.PUBLIC_hubspot.hubspot__engagements
), engagement_companies AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_hubspot.stg_hubspot__engagement_company
), engagement_companies_joined AS (
  SELECT
    engagements.engagement_type,
    engagement_companies.company_id
  FROM engagements
  INNER JOIN engagement_companies
    USING (engagement_id)
), engagement_companies_agg AS (
  SELECT
    company_id,
    COUNT(CASE WHEN engagement_type = 'NOTE' THEN company_id END) AS count_engagement_notes,
    COUNT(CASE WHEN engagement_type = 'TASK' THEN company_id END) AS count_engagement_tasks,
    COUNT(CASE WHEN engagement_type = 'CALL' THEN company_id END) AS count_engagement_calls,
    COUNT(CASE WHEN engagement_type = 'MEETING' THEN company_id END) AS count_engagement_meetings,
    COUNT(CASE WHEN engagement_type = 'EMAIL' THEN company_id END) AS count_engagement_emails,
    COUNT(CASE WHEN engagement_type = 'INCOMING_EMAIL' THEN company_id END) AS count_engagement_incoming_emails,
    COUNT(CASE WHEN engagement_type = 'FORWARDED_EMAIL' THEN company_id END) AS count_engagement_forwarded_emails
  FROM engagement_companies_joined
  GROUP BY
    1
), joined AS (
  SELECT
    companies.*,
    COALESCE(engagement_companies_agg.count_engagement_notes, 0) AS count_engagement_notes,
    COALESCE(engagement_companies_agg.count_engagement_tasks, 0) AS count_engagement_tasks,
    COALESCE(engagement_companies_agg.count_engagement_calls, 0) AS count_engagement_calls,
    COALESCE(engagement_companies_agg.count_engagement_meetings, 0) AS count_engagement_meetings,
    COALESCE(engagement_companies_agg.count_engagement_emails, 0) AS count_engagement_emails,
    COALESCE(engagement_companies_agg.count_engagement_incoming_emails, 0) AS count_engagement_incoming_emails,
    COALESCE(engagement_companies_agg.count_engagement_forwarded_emails, 0) AS count_engagement_forwarded_emails
  FROM companies
  LEFT JOIN engagement_companies_agg
    USING (company_id)
)
SELECT
  *
FROM joined

WITH history AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_hubspot.stg_hubspot__company_property_history
), windows AS (
  SELECT
    company_id,
    field_name,
    change_source,
    change_source_id,
    change_timestamp AS valid_from,
    new_value,
    LEAD(change_timestamp) OVER (PARTITION BY company_id, field_name ORDER BY change_timestamp) AS valid_to
  FROM history
), surrogate AS (
  SELECT
    windows.*,
    MD5(
      CAST(COALESCE(CAST(field_name AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(company_id AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(valid_from AS TEXT), '_dbt_utils_surrogate_key_null_') AS TEXT)
    ) AS id
  FROM windows
)
SELECT
  *
FROM surrogate

WITH history AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_hubspot.stg_hubspot__contact_property_history
), windows AS (
  SELECT
    contact_id,
    field_name,
    change_source,
    change_source_id,
    change_timestamp AS valid_from,
    new_value,
    LEAD(change_timestamp) OVER (PARTITION BY contact_id, field_name ORDER BY change_timestamp) AS valid_to
  FROM history
), surrogate AS (
  SELECT
    windows.*,
    MD5(
      CAST(COALESCE(CAST(field_name AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(contact_id AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(valid_from AS TEXT), '_dbt_utils_surrogate_key_null_') AS TEXT)
    ) AS id
  FROM windows
)
SELECT
  *
FROM surrogate

WITH contact_lists AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_hubspot.stg_hubspot__contact_list
), email_metrics AS (
  SELECT
    *
  FROM TEST.PUBLIC_hubspot.int_hubspot__email_metrics__by_contact_list
), joined AS (
  SELECT
    contact_lists.*
  FROM contact_lists
  LEFT JOIN email_metrics
    USING (contact_list_id)
)
SELECT
  *
FROM joined