"""DB utils."""

from datetime import date
from typing import List, Tuple

import pymysql

import config
from constants import common as consts
from constants import sql as sql_consts
from logger import logger
import s3
import utils


class MainDB:
    """Main DB methods container.

    """
    def __init__(self):
        """Init main DB.

        """
        self.sql_connection = pymysql.connect(
            host=config.MySQL.HOST,
            port=config.MySQL.PORT,
            user=config.MySQL.USER,
            passwd=config.MySQL.PASSWORD,
            db=config.MySQL.DATABASE
        )
        self.sql_cursor = self.sql_connection.cursor()

    @utils.handle_errors()
    def exec_select(self, sql_text: str, arguments: tuple or None = None, replacements: dict or None = None) -> List:
        """Execute DB select command.

        Args:
            sql_text (str): SQL command.
            arguments (tuple or None): Command parameters.
            replacements (dict or None): SQL command substitutions.

        Return:
            List: Set of row.

        """
        if replacements:
            sql_text = sql_text.format(**replacements)
        self.sql_cursor.execute(sql_text, arguments)
        # logger.debug(self.sql_cursor._last_executed)
        result = self.sql_cursor.fetchall()
        if not result:
            return []
        if len(result[0]) == 1:
            return [r[0] for r in result]
        return result

    def _exec_command(self, sql_text: str, arguments: tuple or None = None, replacements: dict or None = None) -> int:
        """Execute DB change command.

        Args:
            sql_text (str): SQL command.
            arguments (tuple or None): Command parameters.
            replacements (dict or None): SQL command substitutions.

        Return:
            int: Row affected count.

        """
        if replacements:
            sql_text = sql_text.format(**replacements)
        self.sql_cursor.execute(sql_text, arguments)
        # logger.debug(self.sql_cursor._last_executed)
        row_count = self.sql_cursor.rowcount
        self.sql_connection.commit()
        return row_count

    @utils.handle_errors()
    def exec_command(self, sql_text: str, arguments: tuple or None = None, replacements: dict or None = None) -> int:
        """Execute DB change command.

        Args:
            sql_text (str): SQL command.
            arguments (tuple or None): Command parameters.
            replacements (dict or None): SQL command substitutions.

        Return:
            int: Row affected count.

        """
        return self._exec_command(sql_text, arguments, replacements)

    def generate_in_clause_str(self, items: List) -> str:
        """Generate in clause str to substitute list.

        Args:
            items (List): In clause items.

        Returns:
            str: In clause string.

        """
        return ','.join(['%s'] * len(items))

    def generate_where_and_or(self, items: List[Tuple], template: str) -> Tuple[str, List]:
        """Generate where block like multiple column IN but with AND and OR.

        Multiple column IN = Full table scan, ANDs + ORs = Index scan.

        Args:
            items: Filter args.
            template: Column set template.

        Returns:
              Filter full str template and flatten list of args.

        """
        filter_str = " OR ".join([template] * len(items))
        filter_items = [param for item in items for param in item]
        return filter_str, filter_items

    def exec_vendor_command(
        self,
        vendor: str,
        sql_text: str,
        playlists: List[str] or List[Tuple],
        template: str = sql_consts.SQL_TEMPLATE_APPLE_FILTER_PlaylistId_StoreFront,
        arguments: tuple or None = None,
        replacements: dict or None = None,
        handle_errors: bool = True
    ) -> int:
        """Execute DB change command.

        Args:
            sql_text: SQL command.
            vendor: Spotify or Apple.
            playlists: Playlist ID or ID + storefront list.
            template: Column filter template.
            arguments: Command parameters.
            replacements: SQL command substitutions.
            handle_errors: Handle errors or allow to handle from caller.

        Return:
            int: Row affected count.

        """
        if vendor == consts.VENDOR_SPOTIFY:
            repl_dict = dict(playlists=self.generate_in_clause_str(playlists))
            arg_tuple = tuple(playlists)
        else:
            filter_str, filter_values = self.generate_where_and_or(playlists, template)
            repl_dict = dict(filter=filter_str)
            arg_tuple = tuple(filter_values)

        if arguments:
            arg_tuple = tuple(list(arguments) + list(arg_tuple))
        if replacements:
            repl_dict.update(replacements)

        exec_command = self.exec_command if handle_errors else self._exec_command

        return exec_command(sql_text, replacements=repl_dict, arguments=arg_tuple)

    def get_top_playlists_last_date(self, vendor: str) -> date:
        """Get top playlists table last records date.

        Args:
            vendor (str): Vendor name.

        Returns:
            date: Last date.

        """
        return self.exec_select(
            sql_consts.SQL_SELECT_MAX_DATE, replacements=dict(top_playlists=sql_consts.TABLE_PLAYLIST_TOP[vendor]))[0]

    def get_missing_playlists(self, vendor: str, last_top_date: date) -> List[str] or List[Tuple]:
        """Get playlists without aggregated summary and with history.

        Args:
            vendor (str): Vendor name.
            last_top_date (date): Last playlists top date.

        Returns:
            List[str]: Playlist IDs.

        """
        if vendor == consts.VENDOR_APPLE:
            return self.exec_select(sql_consts.SQL_SELECT_APPLE_PLAYLISTS_NO_HISTORY, arguments=(last_top_date,))
        else:
            # one query with all the actions below is too slow
            # get all current top playlists
            top_playlists = self.exec_select(sql_consts.SQL_SELECT_SPOTIFY_TOP_PLAYLISTS, arguments=(last_top_date,))
            # get aggregated top playlists
            aggregated_top_playlists = self.exec_select(
                sql_consts.SQL_SELECT_SPOTIFY_PLAYLISTS_AGGREGATED,
                replacements=dict(playlists=self.generate_in_clause_str(top_playlists)),
                arguments=tuple(top_playlists)
            )
            # calc missing top playlists in aggregated table
            missing_top_playlists = list(set(top_playlists) - set(aggregated_top_playlists))
            if not missing_top_playlists:
                return []
            # check the these missing top playlists have history records
            missing_top_with_history = self.exec_select(
                sql_consts.SQL_SELECT_SPOTIFY_PLAYLISTS_WITH_OLD_HISTORY,
                replacements=dict(playlists=self.generate_in_clause_str(missing_top_playlists)),
                arguments=tuple(missing_top_playlists)
            )
            return missing_top_with_history

    def get_non_top_playlists_history(self, vendor: str, last_top_date: date, limit: int) -> List[str]:
        """Drop non top playlists history data.

        Args:
            vendor: Vendor name.
            last_top_date: Last playlists top date.
            limit: Batch size.

        Returns:
            Non top playlists in history.

        """
        return self.exec_select(
            sql_consts.SQL_SELECT_EXCESS_PLAYLISTS[vendor],
            arguments=(last_top_date,),
            replacements=dict(limit=limit)
        )

    def delete_history_by_playlists(self, vendor: str, playlists: List[str] or List[Tuple]) -> int:
        """Drop non top playlists history data.

        Args:
            vendor: Vendor name.
            playlists: Playlist ID or playlist ID + storefront list to delete.

        Returns:
            int: Affected rows count.

        """
        return self.exec_vendor_command(vendor, sql_consts.SQL_DELETE_NON_TOP_PLAYLISTS[vendor], playlists)

    def handle_s3_non_finished_chunk(self, *args):
        """Handle S3 upload errors by removing non finished chunk files.

        """
        _, _, s3_path, s3_client = args
        logger.debug(f'Removing files in {s3_path}')
        s3_client.delete_folder_files(s3_client.get_relative_by_full(s3_path))

    @utils.handle_errors(custom_handler=handle_s3_non_finished_chunk)
    def upload_to_s3(self, vendor: str, playlists: List[str], s3_path: str, s3_client: s3.S3Client):
        """Upload data chunk to S3.

        Args:
            vendor (str): Vendor name.
            playlists (List[str]): Playlist ID or ID + storefront list.
            s3_path (str): S3 upload path.
            s3_client (s3.S3Client): S3 client to handle upload errors.

        """
        return self.exec_vendor_command(
            vendor,
            sql_consts.SQL_UPLOAD_S3[vendor],
            playlists,
            replacements=dict(s3_path=f'{s3_path}h'),
            handle_errors=False,
            template=sql_consts.SQL_TEMPLATE_APPLE_UPLOAD_FILTER_PlaylistId_StoreFront,
        )

    def download_from_s3(self, vendor: str, s3_path: str) -> int:
        """Download data chunks from S3 to MySQL.

        Args:
            vendor (str): Vendor name.
            s3_path (str): S3 download path.

        Returns:
            int: Rows count.

        """
        return self.exec_command(sql_consts.SQL_DOWNLOAD_S3[vendor], replacements=dict(s3_path=s3_path))

    def get_playlists(
            self, vendor: str, last_top_date: date, prev_playlist: str or Tuple[str, str], limit: int) -> List[str]:
        """Get next playlist ID chunk for specific vendor.

        Args:
            vendor: Vendor name.
            last_top_date: Last playlists top date.
            prev_playlist: Previous playlist ID.
            limit: Batch size.

        Returns:
            List[str]: Playlist ID chunk.

        """
        if vendor == consts.VENDOR_SPOTIFY:
            arguments = (last_top_date, prev_playlist)
        else:
            arguments = (last_top_date, prev_playlist[0], *prev_playlist)

        return self.exec_select(
            sql_consts.SQL_SELECT_PLAYLIST[vendor], replacements=dict(limit=limit), arguments=arguments)

    def select_history_chunk(self, vendor: str, current_date: date, playlists: List[str] or List[Tuple]) -> int:
        """Get next playlist ID chunk for specific vendor.

        Args:
            vendor: Vendor name.
            current_date: Current processing date.
            playlists: Playlist ID or ID + storefront list.

        Returns:
            int: Row count.

        """
        return self.exec_vendor_command(
            vendor,
            sql_consts.SQL_COPY_HISTORY[vendor],
            playlists,
            arguments=(current_date,),
            replacements=dict(temp_table=sql_consts.TABLE_HISTORY_TEMP[vendor]),
            template=sql_consts.SQL_TEMPLATE_APPLE_UPLOAD_FILTER_PlaylistId_StoreFront,
        )

    def drop_temp_table(self, vendor: str):
        """Drop temp table.

        Args:
            vendor (str): Vendor name.

        """
        self.exec_command(
            sql_consts.SQL_DROP_TEMP_TABLE, replacements=dict(history_temp=sql_consts.TABLE_HISTORY_TEMP[vendor]))