PRS for Music

SELECT
  row_number() OVER ()                                              AS line_number,
  trim("Work Title")                                                AS song_title,
  upper(trim("Work Title"))                                         AS song_title_key,
  upper(NULLIF(trim("Usage Narrative"), ''))                        AS source_name_key,
  upper(NULLIF(trim("Usage Summary"), ''))                          AS income_class_key,
  upper(NULLIF(trim("Rights Type"), ''))                            AS right_type_key,
  upper(COALESCE(NULLIF(trim("Territory Code"), ''),
                 NULLIF(trim("Territory Name"), '')))               AS territory_raw,
  NULLIF(concat_ws(' / ', NULLIF(trim("IP1"), ''), NULLIF(trim("IP2"), ''),
                          NULLIF(trim("IP3"), ''), NULLIF(trim("IP4"), '')), '')
                                                                    AS writer_name,
  upper(replace(replace(replace(NULLIF(trim("ISWC"), ''), '-', ''), '.', ''), ' ', ''))
                                                                    AS iswc,
  TRY_CAST(NULLIF(trim("Number of Perfs"), '') AS DECIMAL(14,2))    AS units,
  -- Adjustment lines (e.g. fixed point reconciliation) carry no Amount; their
  -- money is the move from the previously paid value to the revised one.
  COALESCE(
    TRY_CAST(replace(NULLIF(trim("Amount (performance revenue)"), ''), ',', '') AS DECIMAL(18,6)),
    CASE WHEN NULLIF(trim("Adjustment Reason"), '') IS NOT NULL THEN
      TRY_CAST(NULLIF(trim("Works Share Transfer To"), '') AS DECIMAL(18,6))
        - COALESCE(TRY_CAST(NULLIF(trim("Works Share Transfer From"), '') AS DECIMAL(18,6)), 0)
    END)                                                            AS amount_raw,
  TRY_CAST(COALESCE(NULLIF(trim("Share"), ''), NULLIF(trim("New Share"), '')) AS DECIMAL(9,6))
                                                                    AS share_percentage,
  COALESCE(NULLIF(trim("Adjustment Reason"), ''),
           NULLIF(trim("Old Share"), ''),
           NULLIF(trim("New Share"), ''),
           NULLIF(trim("Works Share Transfer From"), ''),
           NULLIF(trim("Works Share Transfer To"), '')) IS NOT NULL AS adjustment,
  CAST(try_strptime(NULLIF(trim("Perf Start Date"), ''), '%d/%m/%Y') AS DATE)
                                                                    AS earning_date,
  'GBP'                                                             AS currency,
  to_json(row)                                                      AS source_row
FROM read_csv('{{file}}', header = true, sample_size = -1, all_varchar = true) AS row;