"""Athena queries."""

from constants.common import Table

TABLE_HISTORY = "tblSpotifyHotHitsPlaylistHistory"


class AthenaSQL:
    CREATE_DATABASE = """CREATE DATABASE IF NOT EXISTS {database};"""

    CREATE_TABLE = """
CREATE EXTERNAL TABLE IF NOT EXISTS {database}.{table} ({columns})
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION '{s3_path}'
TBLPROPERTIES ('has_encrypted_data'='true');
    """

    CREATE_TABLE_COLUMNS = {
        Table.STATS: """
`PlaylistId` string,
`ISRC` string,
`PlaylistIndex` int,
`Date` date
        """,
    }

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

    AGGREGATE = {
        Table.STATS: """
SELECT
  s.PlaylistId,
  s.ISRC,
  MIN(CASE WHEN s.EntryRank = 1 THEN s.Date END) AS EntryDate,
  MIN(CASE WHEN s.LatestRank = 1 THEN s.Date END) AS LatestDate,
  MIN(CASE WHEN s.LatestRank = 1 THEN s.PlaylistIndex END) AS LatestPosition,
  MIN(CASE WHEN s.PeakRank = 1 THEN s.Date END) AS PeakDate,
  MIN(CASE WHEN s.PeakRank = 1 THEN s.PlaylistIndex END) AS PeakPosition
FROM (
  SELECT
    hm.PlaylistId,
    hm.ISRC,
    hm.PlaylistIndex,
    hm.Date,
    hm.EntryRank,
    hm.LatestRank,
    hm.PeakRank
  FROM (
    SELECT
      h.PlaylistId,
      h.ISRC,
      h.PlaylistIndex,
      h.Date,
      ROW_NUMBER() OVER (PARTITION BY h.PlaylistId, h.ISRC ORDER BY h.Date) AS EntryRank,
      ROW_NUMBER() OVER (PARTITION BY h.PlaylistId, h.ISRC ORDER BY h.Date DESC) AS LatestRank,
      ROW_NUMBER() OVER (PARTITION BY h.PlaylistId, h.ISRC ORDER BY h.PlaylistIndex, h.Date) AS PeakRank
    FROM {table} AS h
  ) AS hm
  WHERE hm.EntryRank = 1 OR hm.LatestRank = 1 OR hm.PeakRank = 1
) AS s
GROUP BY s.PlaylistId, s.ISRC
        """,
    }