source_db: art_relations
dim_table: dim_project
export_sql: >-
  SELECT
    project_id,
    project_code,
    vendor_id as labelid,
    subaccount_id,
    stripSpecialChars(project_name)
  FROM project p
  WHERE
    -- legacy catalog_ids
    (project_id < 3500559 OR project_id in (3504895,3543414,3557120,3551814,3508049,3546188,3544926,3548987,3516790,3506874,3508097,3518551,3550794,3526256,3542029,3509814,3547169,3550303)) OR
    -- Project has at least one release getting Accounting / Analytics
    EXISTS (
      SELECT *
        FROM releases r
        JOIN artist_info ai on ai.artist_id = r.artist_id
        WHERE
          r.project_id = p.project_id AND
          (
            r.release_status = 'in_content' AND
            IFNULL(ai.vendor_id, 0) <> 0
          )
    )
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      projectcode = st.projectcode,
      labelid = st.labelid,
      subaccountid = st.subaccountid,
      projectname = st.projectname,
      last_updated = '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.projectid = st.projectid AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.projectcode = st.projectcode, false) OR
          ({db}.{schema}.{dim_table}.projectcode is null and st.projectcode is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.labelid = st.labelid, false) OR
          ({db}.{schema}.{dim_table}.labelid is null and st.labelid is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.subaccountid = st.subaccountid, false) OR
          ({db}.{schema}.{dim_table}.subaccountid is null and st.subaccountid is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.projectname = st.projectname, false) OR
          ({db}.{schema}.{dim_table}.projectname is null and st.projectname is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
      (
        projectid,
        projectcode,
        labelid,
        subaccountid,
        projectname,
        last_updated,
        date_created
      )
    SELECT
      st.projectid,
      st.projectcode,
      st.labelid,
      st.subaccountid,
      st.projectname,
      '{current_timestamp}' AS last_updated,
      '{current_timestamp}' AS date_created
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} dp ON st.projectid = dp.projectid
    WHERE
      dp.projectid IS NULL