"""SQL queries."""


GET_BATCH = """
SELECT 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;
"""

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

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.conflict = 0, ttt.tuid, NULL),
  internal_conflict = ttt.conflict
FROM (
  SELECT
    MAX(reg.isrc) AS isrc,
    MAX(reg.territory) AS territory,
    MAX(reg.tuid) AS tuid,
    MAX(reg.internal_conflict) AS conflict
  FROM registry AS reg
  GROUP BY (reg.isrc, reg.territory)) ttt
WHERE
  mrr_video_candidate_tmp.isrc = ttt.isrc AND
  mrr_video_candidate_tmp.territory = ttt.territory
"""

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

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