"""
MySQL Database connection and actions.
"""

import asyncio

import pymysql
import pymysql.cursors

from src.backend import logger

from ...constants import FlagTableColumns
from ...environment_vars import MYSQL_CREDENTIALS
from .components.audit_groups import AuditGroups
from .components.audit_rows import AuditRows
from .components.audits import Audits
from .components.export import Export
from .components.flags import Flags
from .components.meta import Meta
from .components.users import Permissions, Users

logger = logger.new_logger(__name__)


class Database:
    """Base class for database connections."""

    def __init__(self, **kwargs):
        """Initialize the database connection.

        KwArgs:
            **kwargs: Additional credential parameters, if provided
                they will override the default ones.
        """
        self.credentials = MYSQL_CREDENTIALS | (kwargs or {})

    def _sync_get_connection(self):
        """Synchronous method to connect to the database."""
        logger.debug("Establishing connection to MySQL database...")
        connection = pymysql.connect(
            **self.credentials,
            cursorclass=pymysql.cursors.DictCursor,
        )
        return connection

    async def get_connection(self):
        """Async method to connect to the database."""
        loop = asyncio.get_event_loop()
        logger.debug("Getting connection to MySQL database...")
        connection = await loop.run_in_executor(None, self._sync_get_connection)
        logger.debug("Connection to MySQL database established.")
        return connection

    async def _execute_query(
        self,
        query: str,
        params: list | tuple | None = None,
        echo: bool = False,
        fetch: bool = True,
        fetchall: bool = True,
        lastrowid: bool = False,
        connection=None,
    ):
        """Base query execution method. DO NOT USE DIRECTLY, prefer using
        the shorthand methods below for the desired fetch type
        (nofetch, fetchone, fetchall).

        Args:
            query: SQL query.
            params: Query parameters.
            echo: Whether to log the query.
            fetch: Whether to fetch any results (true) or not (false).
                If false, the method will return None (i.e. it is a no-fetch query).
                If true, the method will return the fetched data, either a single
                row or a list of rows, depending on the fetchall parameter.
            fetchall: Whether to fetch all results (true) or just the first one (false).
                This parameter is only taken into account if fetch is true.
            lastrowid: Whether to return the last row ID after an insert query. This
                parameter is only taken into account if fetch is false.
            connection: Database connection. If not provided, a new connection will be
                created and closed after the query is executed. Otherwise, the provided
                connection will be used and not closed after the query is executed;
                it is the caller's responsibility to close the connection.
        """
        data = None
        conn = connection or await self.get_connection()

        with conn.cursor() as cur:
            if echo:
                logger.debug(f"Executing query: {query}")
            try:
                row_count = cur.execute(query, params)
            except Exception as ex:
                conn.rollback()
                logger.error("Query failed, rollback: {}", ex)
                raise ex
            if echo:
                _ = "s" if row_count != 1 else ""
                logger.debug(f"Query involved {row_count} row{_}.")
            if fetch:
                data = (cur.fetchall if fetchall else cur.fetchone)()
            conn.commit()
        if connection is None:
            conn.close()
        if not fetch and lastrowid:
            return cur.lastrowid
        return data

    async def execute_query_fetchone(
        self,
        query: str,
        params: list | tuple | None = None,
        connection=None,
    ):
        """Execute a query and fetch a single row.
        query: SQL query.
        params: Query parameters.
        connection: Database connection. If not provided, a new connection will be
            created and closed after the query is executed. Otherwise, the provided
            connection will be used and not closed after the query is executed;
            it is the caller's responsibility to close the connection.
        """
        result = await self._execute_query(
            query,
            params,
            fetch=True,
            fetchall=False,
            connection=connection,
        )
        return result

    async def execute_query_fetchall(
        self, query: str, params: list | tuple | None = None, connection=None
    ):
        """Execute a query and fetch all rows.
        query: SQL query.
        params: Query parameters.
        connection: Database connection. If not provided, a new connection will be
            created and closed after the query is executed. Otherwise, the provided
            connection will be used and not closed after the query is executed;
            it is the caller's responsibility to close the connection.
        """
        result = await self._execute_query(
            query,
            params,
            fetch=True,
            fetchall=True,
            connection=connection,
        )
        return result

    async def execute_query_nofetch(
        self,
        query: str,
        params: list | tuple | None = None,
        lastrowid: bool = False,
        connection=None,
    ):
        """Execute a query that does not fetch any results.

        Args:
            query: SQL query.
            params: Query parameters.
            lastrowid: Whether to return the last row ID after an insert query.
            connection: Database connection. If not provided, a new connection will be
                created and closed after the query is executed. Otherwise, the provided
                connection will be used and not closed after the query is executed;
                it is the caller's responsibility to close the connection.

        """
        result = await self._execute_query(
            query,
            params,
            fetch=False,
            lastrowid=lastrowid,
            connection=connection,
        )
        return result


class Client:
    """Database client."""

    Audits: Audits
    AuditGroups: AuditGroups
    AuditRows: AuditRows
    Meta: Meta
    Export: Export
    Users: Users
    Permissions: Permissions
    Flags: Flags

    _audit_flag_columns: str = ",".join(
        f"AF.{column}"
        for column in (
            FlagTableColumns.ID,
            FlagTableColumns.ROW_IDX,
            FlagTableColumns.AUDIT,
            FlagTableColumns.FLAG,
            FlagTableColumns.DATE_CREATED_UTC,
            FlagTableColumns.DATE_RESOLVED_UTC,
            FlagTableColumns.RESOLUTION,
            FlagTableColumns.RESOLUTION_SUBTYPE,
            FlagTableColumns.CREATED_BY,
            FlagTableColumns.RESOLVED_BY,
            FlagTableColumns.DETAILS,
            FlagTableColumns.UPC,
            FlagTableColumns.ISRC,
            FlagTableColumns.ASSET_ID,
            FlagTableColumns.VIDEO_ID,
        )
    )

    def __init__(self, db=Database):
        """Initialize the client.

        Args:
            db: Database class to use for the connection.
        """
        self.db = db()

        # Initialize components of the client. Each component encapsulates
        # the queries and actions related to a specific part of the database.
        self.Audits = Audits(self)
        self.AuditGroups = AuditGroups(self)
        self.AuditRows = AuditRows(self)
        self.Meta = Meta(self)
        self.Export = Export(self)
        self.Users = Users(self)
        self.Permissions = Permissions(self)
        self.Flags = Flags(self)