"""Util functions and helpers related to connecting to ows_collaborator RDS."""

from typing import Optional

from sqlalchemy import URL, bindparam, create_engine, text
from sqlalchemy.engine import Engine

from ..config import COLLABORATOR_DB_CONFIG
from ..constants import SplitType
from ..schemas import SplitRow
from ..utils import LambdaException

engine: Engine = create_engine(
    URL.create(
        "mysql+pymysql",
        username=COLLABORATOR_DB_CONFIG.get("user"),
        password=COLLABORATOR_DB_CONFIG.get("password"),
        host=COLLABORATOR_DB_CONFIG.get("host"),
        database=COLLABORATOR_DB_CONFIG.get("database"),
    )
)


def close_connection():
    engine.dispose()


def get_report_run(report_run_uuid: str):
    with engine.connect() as conn:
        result = conn.execute(
            text("""
                SELECT period_ids, trigger_type
                FROM report_run
                WHERE uuid = :report_run_uuid
            """),
            {"report_run_uuid": report_run_uuid},
        )
        row = result.fetchone()

    if not row:
        raise LambdaException("Unable to get report run")

    period_ids = row[0].split(",")
    trigger_type = row[1]
    return period_ids, trigger_type


def get_track_splits_for_report_run(
    report_run_uuid: str, invalid_collaborators: list
) -> list[SplitRow]:
    base_query = """
        SELECT
            split.id,
            split.split_type_id,
            split.identifier,
            split.split_rate,
            split.collaborator_id,
            split.rate_type
        FROM report_run
        INNER JOIN report
            ON report.report_run_id = report_run.id
            AND report_run.uuid = :report_run_uuid
        INNER JOIN split
            ON split.collaborator_id = report.collaborator_id
        WHERE split.split_type_id = :split_type_id
    """
    params: dict = {
        "report_run_uuid": report_run_uuid,
        "split_type_id": SplitType.TRACK.value,
    }

    if invalid_collaborators:
        stmt = text(
            base_query + " AND split.collaborator_id NOT IN :invalid_collaborators"
        ).bindparams(bindparam("invalid_collaborators", expanding=True))
        params["invalid_collaborators"] = invalid_collaborators
    else:
        stmt = text(base_query)

    with engine.connect() as conn:
        return [SplitRow(*row) for row in conn.execute(stmt, params).fetchall()]


def get_subaccount_splits_for_report_run(
    report_run_uuid: str, invalid_collaborators: list
) -> list[SplitRow]:
    base_query = """
        SELECT
            split.id,
            split.split_type_id,
            split.identifier,
            split.split_rate,
            split.collaborator_id,
            split.rate_type
        FROM report_run
        INNER JOIN report
            ON report.report_run_id = report_run.id
            AND report_run.uuid = :report_run_uuid
        INNER JOIN split
            ON split.collaborator_id = report.collaborator_id
        WHERE split.split_type_id = :split_type_id
    """
    params: dict = {
        "report_run_uuid": report_run_uuid,
        "split_type_id": SplitType.SUBACCOUNT.value,
    }

    if invalid_collaborators:
        stmt = text(
            base_query + " AND split.collaborator_id NOT IN :invalid_collaborators"
        ).bindparams(bindparam("invalid_collaborators", expanding=True))
        params["invalid_collaborators"] = invalid_collaborators
    else:
        stmt = text(base_query)

    with engine.connect() as conn:
        return [SplitRow(*row) for row in conn.execute(stmt, params).fetchall()]


def get_collaborators_for_report_run(report_run_uuid: str, invalid_collaborators: list):
    base_query = """
        SELECT
            collaborator.id,
            collaborator.performance_rights
        FROM report_run
        INNER JOIN report
            ON report.report_run_id = report_run.id
            AND report_run.uuid = :report_run_uuid
        INNER JOIN collaborator
            ON collaborator.id = report.collaborator_id
    """
    params: dict = {"report_run_uuid": report_run_uuid}

    if invalid_collaborators:
        stmt = text(base_query + " WHERE collaborator.id NOT IN :invalid_collaborators").bindparams(
            bindparam("invalid_collaborators", expanding=True)
        )
        params["invalid_collaborators"] = invalid_collaborators
    else:
        stmt = text(base_query)

    with engine.connect() as conn:
        return conn.execute(stmt, params).fetchall()


def update_snowflake_query_id(report_run_uuid: str, snowflake_query_id: int):
    with engine.begin() as conn:
        conn.execute(
            text("""
                UPDATE report_run
                SET snowflake_query_id = :snowflake_query_id
                WHERE uuid = :report_run_uuid
            """),
            {"report_run_uuid": report_run_uuid, "snowflake_query_id": snowflake_query_id},
        )


def get_collaborators_with_any_gross_split(report_run_uuid: str) -> list:
    with engine.connect() as conn:
        result = conn.execute(
            text("""
                SELECT DISTINCT report.collaborator_id
                FROM report_run
                INNER JOIN report
                    ON report.report_run_id = report_run.id
                    AND report_run.uuid = :report_run_uuid
                INNER JOIN split
                    ON split.collaborator_id = report.collaborator_id
                WHERE split.rate_type = 'GROSS'
            """),
            {"report_run_uuid": report_run_uuid},
        )
        return [row[0] for row in result.fetchall()]


def get_collaborators_with_any_split_exceeding_100pc(report_run_uuid: str) -> list:
    with engine.connect() as conn:
        result = conn.execute(
            text("""
                SELECT DISTINCT report.collaborator_id
                FROM report_run
                INNER JOIN report
                    ON report.report_run_id = report_run.id
                    AND report_run.uuid = :report_run_uuid
                INNER JOIN split
                    ON split.collaborator_id = report.collaborator_id
                WHERE split.rate_type = 'NET'
                    AND split.split_rate > 1
            """),
            {"report_run_uuid": report_run_uuid},
        )
        return [row[0] for row in result.fetchall()]


def get_collaborators_with_combined_track_splits_exceeding_100pc(report_run_uuid: str) -> list:
    with engine.connect() as conn:
        result = conn.execute(
            text("""
                SELECT DISTINCT s1.collaborator_id
                FROM report_run
                    INNER JOIN report
                        ON report.report_run_id = report_run.id
                        AND report_run.uuid = :report_run_uuid
                    INNER JOIN split s1
                        ON s1.collaborator_id = report.collaborator_id
                        AND s1.rate_type = 'NET'
                    INNER JOIN split s2
                        ON s2.identifier = s1.identifier
                        AND s2.rate_type = 'NET'
                GROUP BY s1.identifier
                HAVING SUM(s2.split_rate) / COUNT(DISTINCT s1.id) - 1 > 0.0000001
            """),
            {"report_run_uuid": report_run_uuid},
        )
        return [row[0] for row in result.fetchall()]


def update_reports_status(
    report_run_uuid: str, status: str, with_collaborator_ids: Optional[list] = None
):
    base_query = """
        UPDATE report
        SET status = :status
        WHERE report_run_id = (
            SELECT id FROM report_run WHERE uuid = :report_run_uuid
        )
    """
    params: dict = {"report_run_uuid": report_run_uuid, "status": status}

    if with_collaborator_ids:
        stmt = text(base_query + " AND collaborator_id IN :with_collaborator_ids").bindparams(
            bindparam("with_collaborator_ids", expanding=True)
        )
        params["with_collaborator_ids"] = with_collaborator_ids
    else:
        stmt = text(base_query)

    with engine.begin() as conn:
        conn.execute(stmt, params)