"""service_country model."""

from sqlalchemy import (
    Column,
    Integer,
    MetaData,
    RowMapping,
    Table,
    UniqueConstraint,
    bindparam,
    text,
)
from sqlalchemy.exc import NoResultFound

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

service_country_carveout_table = Table(
    "release_dms_restriction",
    MetaData(),
    Column("restriction_id", Integer, primary_key=True),
    Column("dms_customer_id", Integer),
    Column("upc", Integer),
    Column("release_id", Integer),
    UniqueConstraint(
        "upc",
        "dms_customer_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "dms_customer_id",
        name="new_unique_key",
    ),
)


class ServiceCountryCarveout(Model):
    service_id: int
    country_id: int
    country_code: str


class ServiceCountryPayload(Model):
    service_id: int
    country_codes: list[str]


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

    Retrieves service country carveouts from active contract.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cm.customer_master_master_id,
                       c.id AS country_id,
                       c.country_code
                FROM vendor_contract vc
                JOIN JSON_TABLE(
                    CONCAT('[', vc.dms_carve_out, ']'),
                    '$[*]' COLUMNS (customer_id INT PATH '$')
                ) AS ids
                JOIN customer_master cm ON cm.customer_id = ids.customer_id
                JOIN country c ON c.id = cm.territory
                WHERE vc.id = :vendor_contract_id
                """
            ),
            {"vendor_contract_id": vendor_contract_id},
        )

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

        return {_hydrate_service_country_carveout(row) for row in rows}


def get_subaccount_carveouts(subaccount_id: int) -> set[ServiceCountryCarveout]:
    """Get subaccount service country (substore) carveouts."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cm.customer_master_master_id,
                       c.id AS country_id,
                       c.country_code
                FROM customer_master cm
                INNER JOIN country c ON c.id = cm.territory
                INNER JOIN subaccount_dms_restriction sdr
                    ON sdr.dms_id = cm.customer_id
                WHERE sdr.subaccount_id = :subaccount_id
                """
            ),
            {"subaccount_id": subaccount_id},
        )

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

        return {_hydrate_service_country_carveout(row) for row in rows}


def get_product_carveouts(product_id: int) -> set[ServiceCountryCarveout]:
    """Get product service country (substore) carveouts."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT cm.customer_master_master_id,
                       c.id AS country_id,
                       c.country_code
                FROM customer_master cm
                INNER JOIN country c ON c.id = cm.territory
                INNER JOIN release_dms_restriction rdr
                    ON rdr.dms_customer_id = cm.customer_id
                WHERE rdr.release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )

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

        return {_hydrate_service_country_carveout(row) for row in rows}


def _hydrate_service_country_carveout(data: RowMapping) -> ServiceCountryCarveout:
    return ServiceCountryCarveout(
        service_id=data.customer_master_master_id,
        country_id=data.country_id,
        country_code=data.country_code,
    )


def delete_product_carveouts(product_id: int) -> None:
    """Delete all carveouts by given product_id.

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


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

    Args:
        product_id (int) : id of product or release
        upc (int): upc of product or release
        carveouts (list[ServiceCountryPayload]) : list of service carveouts
        delete_existing_carveouts (bool): if True, existing carveouts will be deleted
    """
    with db_connector.db_session(transaction=True) as session:
        if carveouts:
            sub_store_ids = set()
            for carveout in carveouts:
                sub_store_ids.update(
                    _get_sub_store_ids(carveout.service_id, carveout.country_codes)
                )

            if not sub_store_ids:
                return None

        if delete_existing_carveouts:
            session.execute(
                text(
                    """
                    DELETE
                    FROM release_dms_restriction
                    WHERE release_id = :product_id
                    """
                ),
                {"product_id": product_id},
            )

        if carveouts:
            insert_data = [
                {
                    "dms_customer_id": sub_store_id,
                    "upc": upc,
                    "release_id": product_id,
                }
                for sub_store_id in sub_store_ids
            ]
            session.execute(service_country_carveout_table.insert(), insert_data)

        return None


def _get_sub_store_ids(service_id: int, country_codes: list[str]) -> set[int]:
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                    SELECT cm.customer_id
                    FROM customer_master cm
                    INNER JOIN country c ON c.id = cm.territory
                    WHERE cm.customer_master_master_id = :service_id
                        AND c.country_code in :country_codes
                """
            ).bindparams(bindparam("country_codes", expanding=True)),
            {
                "service_id": service_id,
                "country_codes": country_codes,
            },
        )
        try:
            return {row.customer_id for row in result.mappings().all()}
        except NoResultFound:
            return set()