"""Athena queries."""

from constants.common import VENDOR_APPLE, VENDOR_SPOTIFY


TABLE_HISTORY = {
    VENDOR_APPLE: 'tblAppleTrackPlaylistHistory',
    VENDOR_SPOTIFY: 'tblSpotifyTrackPlaylistHistory'
}


SQL_ATHENA_CREATE_DATABASE = """CREATE DATABASE IF NOT EXISTS {database};"""

SQL_ATHENA_DROP_DATABASE = """DROP DATABASE IF EXISTS {database};"""

SQL_ATHENA_CREATE_TABLE = {
    VENDOR_APPLE: """
CREATE EXTERNAL TABLE IF NOT EXISTS {database}.tblAppleTrackPlaylistHistory (
  `PlaylistId` string,
  `ISRC` string,
  `StoreFront` string,
  `Date` date
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION '{s3_path}'
TBLPROPERTIES ('has_encrypted_data'='true');
""",
    VENDOR_SPOTIFY: """
CREATE EXTERNAL TABLE IF NOT EXISTS {database}.tblSpotifyTrackPlaylistHistory (
  `PlaylistId` string,
  `ISRC` string,
  `Date` date
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION '{s3_path}'
TBLPROPERTIES ('has_encrypted_data'='true');
"""
}

SQL_ATHENA_UPDATE_TABLE = """ALTER TABLE `{table}` SET LOCATION '{s3_path}';"""

SQL_ATHENA_DROP_TABLE = """DROP TABLE `{database}.{table}`;"""

SQL_ATHENA_AGG = {
    VENDOR_APPLE: """
SELECT DISTINCT
  h2.PlaylistId,
  h2.ISRC,
  h2.StoreFront,
  h2.Date AS EntryDate,
  CASE WHEN h2.IsRight = 1 THEN h2.Date ELSE h2.NextDate END AS ExitDate
FROM (
  SELECT
    h1.PlaylistId,
    h1.ISRC,
    h1.StoreFront,
    h1.Date,
    h1.IsLeft,
    h1.IsRight,
    LEAD(h1.Date, 1, h1.Date) OVER (PARTITION BY h1.PlaylistId, h1.ISRC, h1.StoreFront ORDER BY h1.Date) AS NextDate
  FROM (
    SELECT
      h.PlaylistId,
      h.ISRC,
      h.StoreFront,
      h.Date,
      CASE WHEN h.Date < (LEAD(h.Date, 1, DATE('2900-12-01')) OVER (PARTITION BY h.PlaylistId, h.ISRC, h.StoreFront ORDER BY h.Date) + INTERVAL '-2' DAY) THEN 1 ELSE 0 END AS IsRight,
      CASE WHEN h.Date > (LAG(h.Date,1 , DATE('1900-01-01')) OVER (PARTITION BY h.PlaylistId, h.ISRC, h.StoreFront ORDER BY h.Date) + INTERVAL '2' DAY) THEN 1 ELSE 0 END AS IsLeft
    FROM tblAppleTrackPlaylistHistory AS h
  ) AS h1
  WHERE h1.IsLeft = 1 OR h1.IsRight = 1
) AS h2
WHERE h2.IsLeft = 1
""",
    VENDOR_SPOTIFY: """
SELECT DISTINCT
  h2.PlaylistId,
  h2.ISRC,
  h2.Date AS EntryDate,
  CASE WHEN h2.IsRight = 1 THEN h2.Date ELSE h2.NextDate END AS ExitDate
FROM (
  SELECT
    h1.PlaylistId,
    h1.ISRC,
    h1.Date,
    h1.IsLeft,
    h1.IsRight,
    LEAD(h1.Date, 1, h1.Date) OVER (PARTITION BY h1.PlaylistId, h1.ISRC ORDER BY h1.Date) AS NextDate
  FROM (
    SELECT
      h.PlaylistId,
      h.ISRC,
      h.Date,
      CASE WHEN h.Date < (
        LEAD(h.Date, 1, DATE('2900-12-01'))
        OVER (PARTITION BY h.PlaylistId, h.ISRC ORDER BY h.Date) + INTERVAL '-2' DAY)
      THEN 1 ELSE 0 END AS IsRight,
      CASE WHEN h.Date > (
        LAG(h.Date,1 , DATE('1900-01-01'))
        OVER (PARTITION BY h.PlaylistId, h.ISRC ORDER BY h.Date) + INTERVAL '2' DAY)
      THEN 1 ELSE 0 END AS IsLeft
      FROM tblSpotifyTrackPlaylistHistory AS h
  ) AS h1
  WHERE h1.IsLeft = 1 OR h1.IsRight = 1
) AS h2
WHERE h2.IsLeft = 1
"""
}