"""SQL queries for fingerprint data."""


GET_BATCH = """
SELECT service, date, isrc, territory
FROM {table_name}
WHERE tuid = -1
LIMIT %(limit)s
OFFSET %(offset)s;
"""

UPDATE_REPORT = """
UPDATE {table_name}
SET tuid = %(tuid)s, internal_conflict = %(internal_conflict)s
WHERE isrc = %(isrc)s
AND territory = %(territory)s
AND date = %(date)s
AND service = %(service)s;
"""

CREATE_TMP_UPDATE_TABLE = """
CREATE TEMPORARY TABLE IF NOT EXISTS video_tiktok_mrr_candidates_3_tmp_update (
    service VARCHAR(100),
    date DATE,
    isrc VARCHAR(50),
    territory VARCHAR(10),
    tuid INT,
    internal_conflict TINYINT,
    rules_summary TEXT,
    INDEX idx_lookup (isrc, territory, date, service)
)
"""

TRUNCATE_TMP_UPDATE_TABLE = (
    'TRUNCATE TABLE video_tiktok_mrr_candidates_3_tmp_update'
)

INSERT_TMP_UPDATE = """
INSERT INTO video_tiktok_mrr_candidates_3_tmp_update
    (service, date, isrc, territory, tuid, internal_conflict, rules_summary)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""

BULK_UPDATE_REPORT = """
UPDATE {table_name} t
JOIN video_tiktok_mrr_candidates_3_tmp_update u
    ON t.isrc = u.isrc AND t.territory = u.territory
    AND t.date = u.date AND t.service = u.service
SET t.tuid = u.tuid, t.internal_conflict = u.internal_conflict,
    t.rules_summary = u.rules_summary
"""


CREATE_TMP_TABLE = """
CREATE OR REPLACE TRANSIENT TABLE mrr_video_candidate_tmp
(
  service STRING, date DATE, isrc STRING, territory STRING,
  tuid INTEGER, internal_conflict INTEGER, rules_summary STRING
)
"""

PUT_CSV = 'PUT file:///{file_name} @%mrr_video_candidate_tmp'

COPY_CSV = """
COPY INTO mrr_video_candidate_tmp
FILE_FORMAT = (
  type = csv field_delimiter = ','
  skip_header=0 error_on_column_count_mismatch=false)
"""

UPDATE_TMP_TABLE = """
UPDATE mrr_video_candidate_tmp SET
  tuid = IFF(ttt.internal_conflict = 0, ttt.tuid, NULL),
  internal_conflict = ttt.internal_conflict,
  rules_summary = IFF(LENGTH(ttt.rules_summary) > 65000,
    '{"size": "rules_summary too large, check Snowflake instead"}',
    ttt.rules_summary)
FROM (
  WITH rows_with_osr AS (
    SELECT tmp.service, tmp.date, tmp.isrc, tmp.territory,
    COALESCE(
      MAX(CASE WHEN osr.isrc = tmp.isrc THEN osr.id END),
      MAX(osr.id)
    ) AS osr_id
    FROM facts.prod.orchard_sound_recording osr
    INNER JOIN facts.prod.contains_fingerprint f
    on f.orchard_sound_recording_id = osr.id
    INNER JOIN facts.prod.fingerprinted_as fa on fa.acrid_id = f.acrid_id
    INNER JOIN facts.prod.has_asset ha
      on ha.orchard_asset_id = fa.orchard_asset_id
    INNER JOIN
      orchard_app_reporting_v2.art_relations_prod_art_relations.track t
      on t.id = ha.orchard_track_id
    INNER JOIN mrr_video_candidate_tmp tmp
      on tmp.isrc = t.isrc
    group by tmp.service, tmp.date, tmp.isrc, tmp.territory
  ),
  rows_with_osr_and_osr_ids AS (
    select
      rwo.service,
      rwo.date,
      rwo.isrc,
      rwo.territory,
      rwo.osr_id,
      osr.primary_track_id as osr_primary_track_id,
      project.vendor_id as osr_vendor_id,
      project.subaccount_id as osr_subaccount_id
    FROM rows_with_osr rwo
    INNER JOIN facts.prod.orchard_sound_recording osr
      on osr.id = rwo.osr_id
    INNER JOIN facts.prod.orchard_track t
      ON t.id = osr.primary_track_id
    INNER JOIN facts.prod.orchard_product product
      ON product.id = t.product_id
    INNER JOIN facts.prod.project project
      ON project.id = product.project_id
  ),
  product_deletion_dates AS (
    --Work out the effective deletion date per UPC.
    --For products not currently deleted (deletions = 'N'),
    -- deletion_date is NULL.
    --For products currently deleted (deletions = 'Y'),
    --we find the earliest log entry where deletions was set to 'Y'
    --that timestamp is when the product was first deleted.
    SELECT
      rel.upc,
      CASE
        WHEN rel.deletions = 'N' THEN NULL
        ELSE MIN(rl.log_timestamp)::DATE
      END as deletion_date
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases rel
    LEFT JOIN
      orchard_app_reporting_v2
        .art_relations_prod_art_relations_log.releases_log rl
      ON rl.upc = rel.upc
      AND rl.deletions = 'Y'
    GROUP BY rel.upc, rel.deletions
  ),
  product_completion_dates AS (
    --Work out the effective completion date per UPC.
    --For products we find the earliest log entry
    --where release_status was set to 'in_content'.
    --That timestamp is when the product was first completed.
    SELECT
      rel.upc,
      MIN(rl.log_timestamp)::DATE as completion_date
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases rel
    LEFT JOIN
      orchard_app_reporting_v2
        .art_relations_prod_art_relations_log.releases_log rl
      ON rl.upc = rel.upc
      AND rl.release_status = 'in_content'
    GROUP BY rel.upc
  ),
  fingerprint_tracks AS (
    --For the rows_with_osr find all orchard_tracks (can be many)
    --and the products + vendor_ids + subaccount_ids
    --those orchard_tracks belong to
    --so we can work out if there are conflicts.
    --We exclude products whose deletion_date is before the date:
    --a product deleted after the date was still valid during that
    --period and should be included.
    SELECT
      rwo.service,
      rwo.date,
      rwo.isrc,
      rwo.territory,
      rwo.osr_id,
      rwo.osr_primary_track_id,
      rwo.osr_vendor_id,
      rwo.osr_subaccount_id,
      t.id as track_id,
      t.isrc as track_isrc,
      t.product_id,
      product.upc,
      pdd.deletion_date as product_deletion_date,
      pcd.completion_date as product_completion_date,
      project.vendor_id as track_vendor_id,
      project.subaccount_id as track_subaccount_id
    FROM rows_with_osr_and_osr_ids rwo
    JOIN facts.prod.contains_fingerprint cf
      ON cf.orchard_sound_recording_id = rwo.osr_id
    JOIN facts.prod.fingerprinted_as fa ON fa.acrid_id = cf.acrid_id
    JOIN facts.prod.has_asset ha
      ON ha.orchard_asset_id = fa.orchard_asset_id
    JOIN facts.prod.orchard_track t ON t.id = ha.orchard_track_id
    JOIN facts.prod.orchard_product product ON product.id = t.product_id
    --product should be either not deleted
    --or deleted after the rwo.date
    JOIN product_deletion_dates pdd
      ON pdd.upc = product.upc
      AND (pdd.deletion_date IS NULL OR pdd.deletion_date > rwo.date)
    --product should be completed before the report date
    JOIN product_completion_dates pcd
      ON pcd.upc = product.upc AND pcd.completion_date <= rwo.date
    JOIN facts.prod.project project ON project.id = product.project_id
  ),
  fingerprinting_track_rules AS (
    --Rules matched at the Track level (highest priority)
    SELECT
      ft.service, ft.date, ft.isrc, ft.territory,
      ft.osr_id, ft.osr_primary_track_id, ft.osr_vendor_id,
      ft.osr_subaccount_id,
      ft.track_id, ft.track_isrc, ft.product_id, ft.upc,
      ft.product_deletion_date, ft.product_completion_date,
      ft.track_vendor_id, ft.track_subaccount_id,
      r.policy,
      r.period_start as rule_period_start,
      r.period_end as rule_period_end,
      r.created_at as rule_created_at,
      upper(r.orchard_obj_type) as rule_level,
      1 as rule_priority
    FROM fingerprint_tracks ft
    JOIN facts.prod.has_fingerprint_rule r
      ON r.orchard_obj_id = ft.track_id
      AND r.orchard_obj_type = 'Track'
      --For carveout rules with no explicit period_start,
      --fall back to created_at as the effective
      --period_start so they are not retroactively applied
      --to report periods before they existed.
      --Non-carveout rules with period_start = NULL
      --remain unrestricted (apply to all dates).
      AND (
        COALESCE(r.period_start,
          CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) IS NULL
        OR COALESCE(
          r.period_start,
          CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) <= ft.date)
      AND (r.period_end IS NULL OR r.period_end >= ft.date)
      AND (r.territory = ft.territory OR r.territory = '*')
      AND r.service = ft.service
  ),
  fingerprinting_subaccount_rules AS (
    --Rules matched at the SubAccount level
    SELECT
      ft.service, ft.date, ft.isrc, ft.territory,
      ft.osr_id, ft.osr_primary_track_id, ft.osr_vendor_id,
      ft.osr_subaccount_id,
      ft.track_id, ft.track_isrc, ft.product_id, ft.upc,
      ft.product_deletion_date, ft.product_completion_date,
      ft.track_vendor_id, ft.track_subaccount_id,
      r.policy,
      r.period_start as rule_period_start,
      r.period_end as rule_period_end,
      r.created_at as rule_created_at,
      upper(r.orchard_obj_type) as rule_level,
      2 as rule_priority
    FROM fingerprint_tracks ft
    JOIN facts.prod.has_fingerprint_rule r
      ON r.orchard_obj_id = ft.track_subaccount_id
      AND r.orchard_obj_type = 'SubAccount'
      --For carveout rules with no explicit period_start,
      --fall back to created_at as the effective
      --period_start so they are not retroactively applied
      --to report periods before they existed.
      --Non-carveout rules with period_start = NULL remain
      --unrestricted (apply to all dates).
      AND (
        COALESCE(r.period_start,
          CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) IS NULL
        OR COALESCE(
          r.period_start,
          CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) <= ft.date)
      AND (r.period_end IS NULL OR r.period_end >= ft.date)
      AND (r.territory = ft.territory OR r.territory = '*')
      AND r.service = ft.service
  ),
  fingerprinting_vendor_rules AS (
    --Rules matched at the Vendor level (lowest priority)
    SELECT
      ft.service, ft.date, ft.isrc, ft.territory,
      ft.osr_id, ft.osr_primary_track_id, ft.osr_vendor_id,
      ft.osr_subaccount_id,
      ft.track_id, ft.track_isrc, ft.product_id, ft.upc,
      ft.product_deletion_date, ft.product_completion_date,
      ft.track_vendor_id, ft.track_subaccount_id,
      r.policy,
      r.period_start as rule_period_start,
      r.period_end as rule_period_end,
      r.created_at as rule_created_at,
      upper(r.orchard_obj_type) as rule_level,
      3 as rule_priority
    FROM fingerprint_tracks ft
    JOIN facts.prod.has_fingerprint_rule r
      ON r.orchard_obj_id = ft.track_vendor_id
      AND r.orchard_obj_type = 'Vendor'
      --For carveout rules with no explicit period_start,
      --fall back to created_at as the effective
      --period_start so they are not retroactively applied
      --to report periods before they existed.
      --Non-carveout rules with period_start = NULL remain
      --unrestricted (apply to all dates).
      AND (
        COALESCE(r.period_start,
          CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) IS NULL
        OR COALESCE(
          r.period_start,
        CASE WHEN r.policy = 'carveout'
          THEN r.created_at::DATE END) <= ft.date)
      AND (r.period_end IS NULL OR r.period_end >= ft.date)
      AND (r.territory = ft.territory OR r.territory = '*')
      AND r.service = ft.service
  ),
  fingerprinting_tracks_with_rules AS (
    --Union all three rule levels and keep only
    --the highest-priority rule per track
    SELECT * FROM fingerprinting_track_rules
    UNION ALL
    SELECT * FROM fingerprinting_subaccount_rules
    UNION ALL
    SELECT * FROM fingerprinting_vendor_rules
  ),
  fingerprinting_tracks_with_rules_resolved AS (
    SELECT * FROM fingerprinting_tracks_with_rules
    QUALIFY ROW_NUMBER() OVER (
      PARTITION BY service, date, isrc, territory, track_id
      ORDER BY rule_priority ASC
    ) = 1
  ),
  rules_summaries as (
    SELECT
      service,
      date,
      isrc,
      territory,
      TO_VARCHAR(ARRAY_AGG(DISTINCT OBJECT_CONSTRUCT(
        'track_id', track_id,
        'track_isrc', track_isrc,
        'upc', upc,
        'track_vendor_id', track_vendor_id,
        'track_subaccount_id', track_subaccount_id,
        'product_deletion_date', product_deletion_date,
        'product_completion_date', product_completion_date,
        'osr_primary_track_id', osr_primary_track_id,
        'osr_vendor_id', osr_vendor_id,
        'osr_subaccount_id', osr_subaccount_id,
        'policy', policy,
        'rule_level', rule_level,
        'rule_period_start', rule_period_start,
        'rule_period_end', rule_period_end,
        'rule_created_at', rule_created_at
      ))) as rules_summary
    FROM fingerprinting_tracks_with_rules_resolved
    GROUP BY service, date, isrc, territory
  ),
  winning_tracks_by_account AS (
    SELECT
      service,
      date,
      isrc,
      territory,
      osr_primary_track_id,
      osr_vendor_id,
      osr_subaccount_id,
      track_vendor_id,
      track_subaccount_id,
      policy,
      max(track_id) as max_track_id
    FROM fingerprinting_tracks_with_rules_resolved
    GROUP BY service, date, isrc, territory,
      osr_primary_track_id, osr_vendor_id, osr_subaccount_id,
      track_vendor_id, track_subaccount_id, policy
  ),
  unconflicted_tracks as (
      -- Find the winning_tracks_by_account where
      --we have exactly one vendor+subaccount
      -- These don't have conflicts so we can just take the max
      select
        service,
        date,
        isrc,
        territory,
        osr_primary_track_id,
        osr_vendor_id,
        osr_subaccount_id,
        max(track_vendor_id) as track_vendor_id,
        max(track_subaccount_id) as track_subaccount_id,
        max(max_track_id) as max_track_id
      from winning_tracks_by_account
      group by service, date, isrc, territory,
        osr_primary_track_id, osr_vendor_id, osr_subaccount_id
      having count(*) = 1
      union
      -- Find the winning_tracks_by_account where after removing carveouts
      -- we have exactly one vendor+subaccount
      -- These don't have conflicts so we can just take the max
      select
        service,
        date,
        isrc,
        territory,
        osr_primary_track_id,
        osr_vendor_id,
        osr_subaccount_id,
        max(track_vendor_id) as track_vendor_id,
        max(track_subaccount_id) as track_subaccount_id,
        max(max_track_id) as max_track_id
      from winning_tracks_by_account
      where policy != 'carveout'
      group by service, date, isrc, territory,
        osr_primary_track_id, osr_vendor_id, osr_subaccount_id
      having count(*) = 1
  ),
  resolved_tracks as (
    select wt.service,
      wt.date,
      wt.isrc,
      wt.territory,
      case
          when ut.isrc is not null then 0
          else 1
      end as internal_conflict,
      case
          when wt.osr_vendor_id = ut.track_vendor_id
            and wt.osr_subaccount_id = ut.track_subaccount_id
              then wt.osr_primary_track_id
          else ut.max_track_id
      end as tuid,
      rs.rules_summary
    from winning_tracks_by_account wt
    inner join rules_summaries rs
      on rs.service = wt.service
      and rs.date = wt.date
      and rs.isrc = wt.isrc
      and rs.territory = wt.territory
    left outer join unconflicted_tracks ut
      on wt.service = ut.service
      and wt.date = ut.date
      and wt.isrc = ut.isrc
      and wt.territory = ut.territory
    group by all
  )
  select
    service,
    date,
    isrc,
    territory,
    internal_conflict,
    tuid,
    rules_summary
  from resolved_tracks
) ttt
WHERE
  mrr_video_candidate_tmp.isrc = ttt.isrc AND
  mrr_video_candidate_tmp.territory = ttt.territory AND
  mrr_video_candidate_tmp.date = ttt.date AND
  mrr_video_candidate_tmp.service = ttt.service
"""

UNLOAD_CSV = """
  COPY INTO @%mrr_video_candidate_tmp/unload/ FROM mrr_video_candidate_tmp
  FILE_FORMAT = (
    type = 'CSV' field_delimiter = ',' null_if = 'null'
    field_optionally_enclosed_by = '"')
  OVERWRITE = true
  MAX_FILE_SIZE = {max_file_size}
"""

GET_CSV = 'GET @%mrr_video_candidate_tmp/unload/ file:///{file_name}'