ASCAP International

-- ASCAP International 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("Licensor"))                                       AS source_name_key,
  trim(COALESCE(upper(trim("Revenue Class Code")), '') || ' ' ||
       COALESCE(upper(trim("Revenue Class Description")), '')) AS income_class_key,
  upper(trim("Type Of Right"))                                  AS right_type_key,
  upper(trim("Role Type"))                                      AS role_type_key,
  COALESCE(NULLIF(upper(trim("Territory")), ''),
           upper(trim("Country Name")))                         AS territory_raw,
  COALESCE(NULLIF(trim("Legal Earner Party Name"), ''),
           trim("Party Name"))                                  AS writer_name,
  TRY_CAST("Member Share" AS DECIMAL(9,6))                      AS share_percentage,
  TRY_CAST("$ Amount" AS DECIMAL(18,4))                         AS amount_raw,
  COALESCE(upper(trim("Adjustment Indicator")), '')
    IN ('Y', 'YES', 'T', 'TRUE')                                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;