"""country_carveout model."""

from sqlalchemy import (
    Column,
    Integer,
    MetaData,
    RowMapping,
    Table,
    UniqueConstraint,
    text,
)

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

country_carveout_table = Table(
    "release_territory_restriction",
    MetaData(),
    Column("restriction_id", Integer, primary_key=True),
    Column("upc", Integer),
    Column("release_id", Integer),
    Column("country_id", Integer),
    UniqueConstraint(
        "upc",
        "country_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "country_id",
        name="new_unique_key",
    ),
)


class CountryCarveout(Model):
    """country_carveout model."""

    country_id: int
    country_code: str


def get_vendor_carveouts(vendor_contract_id: int) -> set[CountryCarveout]:
    """Get vendor level country carveouts by vendor_contract_id.

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

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

        return {_hydrate_country_carveout(row) for row in rows}


def get_subaccount_carveouts(subaccount_id: int) -> set[CountryCarveout]:
    """Get subaccount country carveouts."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT c.id AS country_id, c.country_code
                FROM subaccount_territory_restriction str
                INNER JOIN country c
                    ON str.country_id = c.id
                WHERE str.subaccount_id = :subaccount_id
                """
            ),
            {"subaccount_id": subaccount_id},
        )

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

        return {_hydrate_country_carveout(row) for row in rows}


def get_product_carveouts(product_id: int) -> set[CountryCarveout]:
    """Get product country carveouts."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT c.id AS country_id, c.country_code
                FROM release_territory_restriction rtr
                INNER JOIN country c
                    ON rtr.country_id = c.id
                WHERE rtr.release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )

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

        return {_hydrate_country_carveout(row) for row in rows}


def _hydrate_country_carveout(data: RowMapping) -> CountryCarveout:
    return CountryCarveout(
        country_id=data.country_id,
        country_code=data.country_code,
    )


def delete_product_carveouts(product_id: int) -> None:
    """Delete product country carveouts."""
    with db_connector.db_session() as session:
        session.execute(
            text(
                """
                DELETE
                FROM release_territory_restriction
                WHERE release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )


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

    Args:
        product_id (int) : id of product or release
        upc (int): upc of product or release
        country_ids (list[int]) : list of country ID's
        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_territory_restriction
                    WHERE release_id = :product_id
                    """
                ),
                {"product_id": product_id},
            )

        if country_ids:
            insert_data = [
                {
                    "upc": upc,
                    "release_id": product_id,
                    "country_id": country_id,
                }
                for country_id in country_ids
            ]

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


def update_account_carveouts(vendor_contract_id: int, country_codes: list[str]) -> None:
    """Update Account level country carveouts.

    Args:
        vendor_contract_id (int) : id of active vendor contract
        country_codes (list[str]) : list of country codes
    """
    country_ids = []
    if country_codes:
        country_ids = country.get_country_ids(country_codes)

    with db_connector.db_session(transaction=True) as session:
        session.execute(
            text(
                """
                UPDATE vendor_contract
                SET territory_carve_out = :territory_carve_out
                WHERE id = :vendor_contract_id
                """
            ),
            {
                "territory_carve_out": ",".join(map(str, country_ids)),
                "vendor_contract_id": vendor_contract_id,
            },
        )