"""service_carveout model."""

from typing import Any

from sqlalchemy import (
    Column,
    Enum,
    Integer,
    MetaData,
    RowMapping,
    Table,
    UniqueConstraint,
    bindparam,
    insert,
    select,
    text,
)

from carveouts.connectors.mysql import db_connector
from carveouts.models import Model

metadata = MetaData()
service_carveout_table = Table(
    "release_dms_master_restriction",
    metadata,
    Column("restriction_id", Integer, primary_key=True),
    Column("customer_master_master_id", Integer),
    Column("distribution_type_id", Integer),
    Column("upc", Integer),
    Column("release_id", Integer),
    Column("updated_by", Integer),
    UniqueConstraint(
        "upc",
        "customer_master_master_id",
        "distribution_type_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "customer_master_master_id",
        "distribution_type_id",
        name="new_unique_key",
    ),
)
account_service_carveout_table = Table(
    "vendor_dms_master_restriction",
    metadata,
    Column("restriction_id", Integer, primary_key=True),
    Column("vendor_contract_id", Integer),
    Column("customer_master_master_id", Integer),
    Column("distribution_type_id", Integer),
    Column("updated_by", Integer),
    UniqueConstraint(
        "vendor_contract_id",
        "customer_master_master_id",
        "distribution_type_id",
        name="unique_vendor_restriction",
    ),
)
service_default_carveout_table = Table(
    "release_default_dms_master_restriction",
    metadata,
    Column("default_restriction_id", Integer, primary_key=True),
    Column("distribution_type_id", Integer),
    Column("upc", Integer),
    Column("release_id", Integer),
    UniqueConstraint(
        "upc",
        "distribution_type_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "distribution_type_id",
        name="new_unique_key",
    ),
)
vendor_contract_distribution_type_table = Table(
    "vendor_contract_distribution_type",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("vendor_contract_id", Integer),
    Column("distribution_type_id", Integer),
    Column("new_store_default", Enum("Y", "N")),
    UniqueConstraint(
        "vendor_contract_id",
        "distribution_type_id",
        name="vend_cont_distro_type_id",
    ),
)
active_contract_view = Table(
    "vw_active_vendor_contract",
    metadata,
    Column("vendor_contract_id", Integer),
    Column("vendor_id", Integer),
)


class ServiceCarveout(Model):
    service_id: int
    service_name: str | None = None
    distribution_types: list[int]

    def __hash__(self) -> int:
        return hash(
            (self.service_id, self.service_name, tuple(self.distribution_types))
        )


def get_vendor_carveouts(vendor_contract_id: int) -> set[ServiceCarveout]:
    """Get vendor service carveouts.

    Retrieves carveouts from the active contract if the vendor has any contracts.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cmm.customer_master_master_id AS service_id,
                    cmm.customer_name AS service_name,
                    GROUP_CONCAT(DISTINCT vdmr.distribution_type_id) AS distribution_type_list
                FROM vendor_dms_master_restriction vdmr
                INNER JOIN customer_master_master cmm
                    ON cmm.customer_master_master_id = vdmr.customer_master_master_id
                WHERE vdmr.vendor_contract_id = :vendor_contract_id
                GROUP BY cmm.customer_master_master_id
                ORDER BY cmm.customer_master_master_id
                """
            ),
            {"vendor_contract_id": vendor_contract_id},
        )

        rows = result.mappings().all()
        if not rows:
            return set()

        return {_hydrate_service_carveouts(row) for row in rows}


def get_subaccount_carveouts(subaccount_id: int) -> set[ServiceCarveout]:
    """Get subaccount service carveouts.

    Retrieves subaccount service carveouts.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cmm.customer_master_master_id AS service_id,
                       cmm.customer_name AS service_name
                FROM subaccount_dms_master_restriction sdmr
                INNER JOIN customer_master_master cmm
                    ON cmm.customer_master_master_id = sdmr.customer_master_master_id
                WHERE sdmr.subaccount_id = :subaccount_id
                GROUP BY cmm.customer_master_master_id
                ORDER BY cmm.customer_master_master_id
                """
            ),
            {"subaccount_id": subaccount_id},
        )

        rows = result.mappings().all()
        if not rows:
            return set()

        return {_hydrate_service_carveouts(row) for row in rows}


def get_product_carveouts(product_id: int) -> set[ServiceCarveout]:
    """Get product service carveouts.

    Retrieves product service carveouts.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cmm.customer_master_master_id AS service_id,
                       cmm.customer_name AS service_name,
                       GROUP_CONCAT(DISTINCT rdmr.distribution_type_id) AS distribution_type_list
                FROM release_dms_master_restriction rdmr
                INNER JOIN customer_master_master cmm
                    ON cmm.customer_master_master_id = rdmr.customer_master_master_id
                WHERE rdmr.release_id = :product_id
                GROUP BY cmm.customer_master_master_id
                ORDER BY cmm.customer_master_master_id
                """
            ),
            {"product_id": product_id},
        )

        rows = result.mappings().all()
        if not rows:
            return set()

        return {_hydrate_service_carveouts(row) for row in rows}


def _hydrate_service_carveouts(data: RowMapping) -> ServiceCarveout:
    return ServiceCarveout(
        service_id=data.service_id,
        service_name=data.service_name,
        distribution_types=(
            list(map(int, data["distribution_type_list"].split(",")))
            if data.get("distribution_type_list")
            else []
        ),
    )


def delete_product_carveouts(product_id: int) -> None:
    """Delete product level service carveouts.

    Args:
        product_id (int) : id of product or release
    """
    with db_connector.db_session() as session:
        session.execute(
            text(
                """
                DELETE
                FROM release_dms_master_restriction
                WHERE release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )


def dataload_vendor_contract_carveout(
    vendor_contract_store_list: list[dict[str, int]],
) -> list[dict[str, Any]]:
    """Get multiple vendor level carveouts by store_id and vendor_contract_id.

    Args:
        vendor_contract_store_list (list):
           A list with the vendor contract and store ids.

    Returns:
        list: the data of the model.

    """
    query = """
        SELECT vr.vendor_contract_id,
               vr.customer_master_master_id AS dms_id,
               GROUP_CONCAT(vr.distribution_type_id) AS distribution_type_list
        FROM vendor_dms_master_restriction vr
        WHERE 0
    """
    query_args = {}

    for i, vendor_cont_store in enumerate(vendor_contract_store_list):
        query_args[f"vendor_contract_id_{i}"] = vendor_cont_store["vendor_contract_id"]
        query_args[f"delivery_store_id_{i}"] = vendor_cont_store["delivery_store_id"]
        query += f"""
            OR (vr.vendor_contract_id = :vendor_contract_id_{i}
                AND vr.customer_master_master_id = :delivery_store_id_{i}
            )
        """

    query += """
        GROUP BY vr.vendor_contract_id, vr.customer_master_master_id
    """

    with db_connector.db_session() as session:
        result = session.execute(text(query), query_args)

    rows = result.mappings().all()
    if not rows:
        return []

    return [
        {
            "vendor_contract_id": row.vendor_contract_id,
            "dms_id": row.dms_id,
            "distribution_type_list": row.distribution_type_list,
        }
        for row in rows
    ]


def add_product_carveouts(
    product_id: int,
    upc: int,
    carveouts: list[ServiceCarveout],
    updated_by: int | None = None,
    delete_existing_carveouts: bool = True,
) -> None:
    """Add Release level service carveouts.

    Args:
        product_id (int) : id of product or release
        upc (int): upc of product or release
        carveouts (list[ServiceCarveout]) : list of service carveouts
        updated_by (int|None) : id of OA user
        delete_existing_carveouts (bool): if True, existing carveouts will be deleted
            before adding new ones
    """
    with db_connector.db_session(transaction=True) as session:
        if delete_existing_carveouts:
            session.execute(
                text(
                    """
                    DELETE
                    FROM release_dms_master_restriction
                    WHERE release_id = :product_id
                    """
                ),
                {"product_id": product_id},
            )

        if carveouts:
            insert_data = [
                {
                    "customer_master_master_id": carveout.service_id,
                    "distribution_type_id": distribution_type_id,
                    "upc": upc,
                    "release_id": product_id,
                    "updated_by": updated_by,
                }
                for carveout in carveouts
                for distribution_type_id in carveout.distribution_types
            ]

            session.execute(service_carveout_table.insert(), insert_data)


def add_account_carveouts(
    vendor_contract_id: int,
    carveouts: list[ServiceCarveout],
    updated_by: int | None = None,
    delete_existing_carveouts: bool = True,
) -> None:
    """Add account contract level service carveouts.

    Args:
        vendor_contract_id (int) : id of account contract
        carveouts (list[ServiceCarveout]) : list of service carveouts
        updated_by (int|None) : id of OA user
        delete_existing_carveouts (bool): if True, existing carveouts will be deleted
            before adding new ones
    """
    with db_connector.db_session(transaction=True) as session:
        if delete_existing_carveouts:
            session.execute(
                text(
                    """
                    DELETE
                    FROM vendor_dms_master_restriction
                    WHERE vendor_contract_id = :vendor_contract_id
                    """
                ),
                {"vendor_contract_id": vendor_contract_id},
            )

        if carveouts:
            insert_data = [
                {
                    "vendor_contract_id": vendor_contract_id,
                    "customer_master_master_id": carveout.service_id,
                    "distribution_type_id": distribution_type_id,
                    "updated_by": updated_by,
                }
                for carveout in carveouts
                for distribution_type_id in carveout.distribution_types
            ]

            session.execute(account_service_carveout_table.insert(), insert_data)


def create_product_new_service_carveouts(
    service_id: int, updated_by: int | None = None
) -> None:
    """Create new store product carveouts for a service."""
    select_stmt = select(
        bindparam("service_id", service_id).label("customer_master_master_id"),
        service_default_carveout_table.c.distribution_type_id,
        service_default_carveout_table.c.upc,
        service_default_carveout_table.c.release_id,
        bindparam("updated_by", updated_by).label("updated_by"),
    )

    with db_connector.db_session() as session:
        session.execute(
            insert(service_carveout_table).from_select(
                [
                    "customer_master_master_id",
                    "distribution_type_id",
                    "upc",
                    "release_id",
                    "updated_by",
                ],
                select_stmt,
            )
        )


def create_account_new_service_carveouts(
    service_id: int, updated_by: int | None = None
) -> None:
    """Create new store account carveouts for a service."""
    select_stmt = select(
        bindparam("service_id", service_id).label("customer_master_master_id"),
        vendor_contract_distribution_type_table.c.distribution_type_id,
        vendor_contract_distribution_type_table.c.vendor_contract_id,
        bindparam("updated_by", updated_by).label("updated_by"),
    ).join(
        active_contract_view,
        vendor_contract_distribution_type_table.c.vendor_contract_id
        == active_contract_view.c.vendor_contract_id,
    )

    with db_connector.db_session() as session:
        session.execute(
            insert(account_service_carveout_table)
            .from_select(
                [
                    "customer_master_master_id",
                    "distribution_type_id",
                    "vendor_contract_id",
                    "updated_by",
                ],
                select_stmt,
            )
            .prefix_with("IGNORE")
        )