from typing import Collection

from .... import logger
from ....constants import Tables
from ....typings import AuditGroupID, AuditID, Rows
from ....utils.sql import placeholders

logger = logger.new_logger(__name__)


class Meta:
    """Class for interacting with the metadata table in the database."""

    def __init__(self, client):
        self.client = client

    async def set(self, audit_id: AuditID, metadata: dict) -> None:
        """Set audit metadata for multiple keys in a single SQL query.
        If a key already exists, the value is updated.

        Args:
            audit_id: Audit ID.
            metadata: Dictionary of metadata keys and values.
        """
        if not metadata:
            logger.warning("No metadata provided for audit ID {}", audit_id)
            return

        logger.debug("Setting metadata for audit ID {}...", audit_id)

        # Start building the query
        query = f"INSERT INTO {Tables.AUDITS_META} (audit, `key`, value) VALUES "
        values = []
        for key, value in metadata.items():
            # Prepare the value; convert None to SQL NULL
            value_str = None if value is None else str(value)
            values.extend([audit_id, key, value_str])

        # Create placeholders for each key-value pair
        placeholders = ", ".join("(%s, %s, %s)" for _ in metadata)
        query += placeholders

        query += " AS new_values (audit, `key`, value) "
        query += "ON DUPLICATE KEY UPDATE value = new_values.value"

        await self.client.db.execute_query_nofetch(query, values)

    async def get(self, audit_id: AuditID, keys: Collection[str] | None = None) -> dict:
        """Get metadata of an audit.

        Args:
            audit_id: Audit ID.
            keys: Metadata keys to get. If None, all metadata is returned.
        """
        logger.debug("Getting metadata for audit ID {}...", audit_id)
        _and_keys = f"AND `key` IN ({placeholders(keys)})" if keys else ""
        query = f"""
            SELECT `key`, value
            FROM {Tables.AUDITS_META}
            WHERE audit = %s
            {_and_keys}
            """
        db = self.client.db
        rows = await db.execute_query_fetchall(query, (audit_id, *(keys or [])))
        return {row["key"]: row["value"] for row in rows}

    async def get_audit_group(self, audit_group_id: AuditGroupID) -> Rows:
        """Get all the metadata of the audits belonging to an audit group.

        Args:
            audit_group_id: Audit group ID.
        """
        logger.debug("Getting metadata for audit group ID {}...", audit_group_id)
        query = f"""
            WITH AUDITS AS (
                        SELECT
                            id,
                            type,
                            `group`
                    FROM audits
                    WHERE `group` = %s
                    )

            SELECT
              AUDITS.id,
              AUDITS.type,
              META.`key`,
              META.value
            FROM {Tables.AUDITS} AUDITS
            LEFT JOIN {Tables.AUDITS_META} META
             ON AUDITS.id = META.audit
            """
        return await self.client.db.execute_query_fetchall(query, (audit_group_id,))