"""SQL queries."""

from constants.common import Table as TableType


class Table:
    STREAMS = "tblSpotifyPlaylistStreamsWeekHistory"
    FOLLOWERS = "tblSpotifyPlaylistFollowersWeekHistory"

    MAPPING = {TableType.STREAMS: STREAMS, TableType.FOLLOWERS: FOLLOWERS}


class SQL:
    UPLOAD_S3 = {
        TableType.STREAMS: """
SELECT `PlaylistUri` AS PlaylistId, `Date`, `Market` AS CountryCode, `TotalStreams` AS Streams
FROM `tblSpotifyAnalyticsAccountPlaylistStreamInfo`
WHERE `PlaylistUri` IN :playlist_id_list
  AND `AccountId` = :distributer_id
  AND `Market` = :country_code
  AND `Date` BETWEEN :date_from AND :date_to
INTO OUTFILE S3 '{s3_path}'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
        """,
        TableType.FOLLOWERS: """
SELECT `PlaylistId`, `Date`, `Followers`
FROM `tblSpotifyPlaylistFollowerHistory`
WHERE `PlaylistId` IN :playlist_id_list
  AND `Date` BETWEEN :date_from AND :date_to
INTO OUTFILE S3 '{s3_path}'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
        """,
    }

    _DOWNLOAD_S3 = """
LOAD DATA FROM S3 PREFIX '{{s3_path}}' IGNORE
INTO TABLE {result_table}
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
({columns});
    """

    DOWNLOAD_S3 = {
        TableType.STREAMS: _DOWNLOAD_S3.format(
            result_table=Table.STREAMS,
            columns="""
`Date`,
`PlaylistId`,
`Value`,
`ValueAD`,
`ValueAE`,
`ValueAG`,
`ValueAL`,
`ValueAM`,
`ValueAO`,
`ValueAR`,
`ValueAT`,
`ValueAU`,
`ValueAZ`,
`ValueBA`,
`ValueBB`,
`ValueBD`,
`ValueBE`,
`ValueBF`,
`ValueBG`,
`ValueBH`,
`ValueBI`,
`ValueBJ`,
`ValueBN`,
`ValueBO`,
`ValueBR`,
`ValueBS`,
`ValueBT`,
`ValueBW`,
`ValueBY`,
`ValueBZ`,
`ValueCA`,
`ValueCD`,
`ValueCG`,
`ValueCH`,
`ValueCI`,
`ValueCL`,
`ValueCM`,
`ValueCO`,
`ValueCR`,
`ValueCV`,
`ValueCW`,
`ValueCY`,
`ValueCZ`,
`ValueDE`,
`ValueDJ`,
`ValueDK`,
`ValueDM`,
`ValueDO`,
`ValueDZ`,
`ValueEC`,
`ValueEE`,
`ValueEG`,
`ValueES`,
`ValueET`,
`ValueFI`,
`ValueFJ`,
`ValueFM`,
`ValueFR`,
`ValueGA`,
`ValueGB`,
`ValueGD`,
`ValueGE`,
`ValueGH`,
`ValueGM`,
`ValueGN`,
`ValueGQ`,
`ValueGR`,
`ValueGT`,
`ValueGW`,
`ValueGY`,
`ValueHK`,
`ValueHN`,
`ValueHR`,
`ValueHT`,
`ValueHU`,
`ValueID`,
`ValueIE`,
`ValueIL`,
`ValueIN`,
`ValueIQ`,
`ValueIS`,
`ValueIT`,
`ValueJM`,
`ValueJO`,
`ValueJP`,
`ValueKE`,
`ValueKG`,
`ValueKH`,
`ValueKI`,
`ValueKM`,
`ValueKN`,
`ValueKR`,
`ValueKW`,
`ValueKZ`,
`ValueLA`,
`ValueLB`,
`ValueLC`,
`ValueLI`,
`ValueLK`,
`ValueLR`,
`ValueLS`,
`ValueLT`,
`ValueLU`,
`ValueLV`,
`ValueLY`,
`ValueMA`,
`ValueMC`,
`ValueMD`,
`ValueME`,
`ValueMG`,
`ValueMH`,
`ValueMK`,
`ValueML`,
`ValueMN`,
`ValueMO`,
`ValueMR`,
`ValueMT`,
`ValueMU`,
`ValueMV`,
`ValueMW`,
`ValueMX`,
`ValueMY`,
`ValueMZ`,
`ValueNA`,
`ValueNE`,
`ValueNG`,
`ValueNI`,
`ValueNL`,
`ValueNO`,
`ValueNP`,
`ValueNR`,
`ValueNZ`,
`ValueOM`,
`ValuePA`,
`ValuePE`,
`ValuePG`,
`ValuePH`,
`ValuePK`,
`ValuePL`,
`ValuePS`,
`ValuePT`,
`ValuePW`,
`ValuePY`,
`ValueQA`,
`ValueRO`,
`ValueRS`,
`ValueRU`,
`ValueRW`,
`ValueSA`,
`ValueSB`,
`ValueSC`,
`ValueSE`,
`ValueSG`,
`ValueSI`,
`ValueSK`,
`ValueSL`,
`ValueSM`,
`ValueSN`,
`ValueSR`,
`ValueST`,
`ValueSV`,
`ValueSZ`,
`ValueTD`,
`ValueTG`,
`ValueTH`,
`ValueTJ`,
`ValueTL`,
`ValueTN`,
`ValueTO`,
`ValueTR`,
`ValueTT`,
`ValueTV`,
`ValueTW`,
`ValueTZ`,
`ValueUA`,
`ValueUG`,
`ValueUS`,
`ValueUY`,
`ValueUZ`,
`ValueVC`,
`ValueVE`,
`ValueVN`,
`ValueVU`,
`ValueWS`,
`ValueXK`,
`ValueZA`,
`ValueZM`,
`ValueZW`,
`Change`,
`ChangeAD`,
`ChangeAE`,
`ChangeAG`,
`ChangeAL`,
`ChangeAM`,
`ChangeAO`,
`ChangeAR`,
`ChangeAT`,
`ChangeAU`,
`ChangeAZ`,
`ChangeBA`,
`ChangeBB`,
`ChangeBD`,
`ChangeBE`,
`ChangeBF`,
`ChangeBG`,
`ChangeBH`,
`ChangeBI`,
`ChangeBJ`,
`ChangeBN`,
`ChangeBO`,
`ChangeBR`,
`ChangeBS`,
`ChangeBT`,
`ChangeBW`,
`ChangeBY`,
`ChangeBZ`,
`ChangeCA`,
`ChangeCD`,
`ChangeCG`,
`ChangeCH`,
`ChangeCI`,
`ChangeCL`,
`ChangeCM`,
`ChangeCO`,
`ChangeCR`,
`ChangeCV`,
`ChangeCW`,
`ChangeCY`,
`ChangeCZ`,
`ChangeDE`,
`ChangeDJ`,
`ChangeDK`,
`ChangeDM`,
`ChangeDO`,
`ChangeDZ`,
`ChangeEC`,
`ChangeEE`,
`ChangeEG`,
`ChangeES`,
`ChangeET`,
`ChangeFI`,
`ChangeFJ`,
`ChangeFM`,
`ChangeFR`,
`ChangeGA`,
`ChangeGB`,
`ChangeGD`,
`ChangeGE`,
`ChangeGH`,
`ChangeGM`,
`ChangeGN`,
`ChangeGQ`,
`ChangeGR`,
`ChangeGT`,
`ChangeGW`,
`ChangeGY`,
`ChangeHK`,
`ChangeHN`,
`ChangeHR`,
`ChangeHT`,
`ChangeHU`,
`ChangeID`,
`ChangeIE`,
`ChangeIL`,
`ChangeIN`,
`ChangeIQ`,
`ChangeIS`,
`ChangeIT`,
`ChangeJM`,
`ChangeJO`,
`ChangeJP`,
`ChangeKE`,
`ChangeKG`,
`ChangeKH`,
`ChangeKI`,
`ChangeKM`,
`ChangeKN`,
`ChangeKR`,
`ChangeKW`,
`ChangeKZ`,
`ChangeLA`,
`ChangeLB`,
`ChangeLC`,
`ChangeLI`,
`ChangeLK`,
`ChangeLR`,
`ChangeLS`,
`ChangeLT`,
`ChangeLU`,
`ChangeLV`,
`ChangeLY`,
`ChangeMA`,
`ChangeMC`,
`ChangeMD`,
`ChangeME`,
`ChangeMG`,
`ChangeMH`,
`ChangeMK`,
`ChangeML`,
`ChangeMN`,
`ChangeMO`,
`ChangeMR`,
`ChangeMT`,
`ChangeMU`,
`ChangeMV`,
`ChangeMW`,
`ChangeMX`,
`ChangeMY`,
`ChangeMZ`,
`ChangeNA`,
`ChangeNE`,
`ChangeNG`,
`ChangeNI`,
`ChangeNL`,
`ChangeNO`,
`ChangeNP`,
`ChangeNR`,
`ChangeNZ`,
`ChangeOM`,
`ChangePA`,
`ChangePE`,
`ChangePG`,
`ChangePH`,
`ChangePK`,
`ChangePL`,
`ChangePS`,
`ChangePT`,
`ChangePW`,
`ChangePY`,
`ChangeQA`,
`ChangeRO`,
`ChangeRS`,
`ChangeRU`,
`ChangeRW`,
`ChangeSA`,
`ChangeSB`,
`ChangeSC`,
`ChangeSE`,
`ChangeSG`,
`ChangeSI`,
`ChangeSK`,
`ChangeSL`,
`ChangeSM`,
`ChangeSN`,
`ChangeSR`,
`ChangeST`,
`ChangeSV`,
`ChangeSZ`,
`ChangeTD`,
`ChangeTG`,
`ChangeTH`,
`ChangeTJ`,
`ChangeTL`,
`ChangeTN`,
`ChangeTO`,
`ChangeTR`,
`ChangeTT`,
`ChangeTV`,
`ChangeTW`,
`ChangeTZ`,
`ChangeUA`,
`ChangeUG`,
`ChangeUS`,
`ChangeUY`,
`ChangeUZ`,
`ChangeVC`,
`ChangeVE`,
`ChangeVN`,
`ChangeVU`,
`ChangeWS`,
`ChangeXK`,
`ChangeZA`,
`ChangeZM`,
`ChangeZW`
            """
        ),
        TableType.FOLLOWERS: _DOWNLOAD_S3.format(
            result_table=Table.FOLLOWERS, columns="`Date`, `PlaylistId`, `Value`, `Change`"
        ),
    }