ASCAP Domestic

-- ASCAP Domestic distribution export -> normalized schema
SELECT
  row_number() OVER ()                                          AS line_number,
  trim("Work Title")                                            AS song_title,
  upper(trim("Work Title"))                                     AS song_title_key,
  upper(trim("Music User"))                                     AS source_name_key,
  upper(trim("Performance Source/Broadcast Medium"))            AS income_class_key,
  upper(trim("Type Of Right"))                                  AS right_type_key,
  upper(trim("Role Type"))                                      AS role_type_key,
  upper(trim("Territory"))                                      AS territory_raw,
  trim("Composer Name")                                         AS writer_name,
  TRY_CAST("Number of Plays" AS DECIMAL(14,2))                  AS units,
  TRY_CAST("Dollars" AS DECIMAL(18,4))
    + COALESCE(TRY_CAST("Premium Dollars" AS DECIMAL(18,4)), 0) AS amount_raw,
  COALESCE(upper(trim("Adjustment Indicator")), '') IN ('Y', 'YES') AS adjustment,
  COALESCE(TRY_CAST(trim("Performance Start Date") AS DATE),
           CAST(try_strptime(trim("Performance Start Date"), '%m/%d/%Y') AS DATE)) AS earning_date,
  'USD'                                                         AS currency,
  to_json(row)                                                  AS source_row
FROM read_csv('{{file}}', header = true, sample_size = -1, all_varchar = true) AS row;