"""Territory model."""

from sqlalchemy import Column, Integer, MetaData, Table, UniqueConstraint, text

from carveouts.connectors.mysql import db_connector

service_opt_out_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",
    ),
)


def get_vendor_opt_outs(vendor_contract_id: int) -> set[int]:
    """Get vendor opt-outs from active contract.

    Returns:
        set[int]: list of distribution type ID's.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT vcdt.distribution_type_id
                FROM vendor_contract vc
                INNER JOIN vendor_contract_distribution_type vcdt
                    ON vc.id = vcdt.vendor_contract_id
                WHERE vc.id = :vendor_contract_id
                    AND vcdt.new_store_default = "Y"
                """
            ),
            {"vendor_contract_id": vendor_contract_id},
        )

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

        return {row.distribution_type_id for row in rows}


def get_product_opt_outs(product_id: int) -> set[int]:
    """Get vendor opt-outs from active contract.

    Returns:
        set[int]: list of distribution type ID's.
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT distribution_type_id
                FROM release_default_dms_master_restriction
                WHERE release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )

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

        return {row.distribution_type_id for row in rows}


def add_product_opt_outs(
    product_id: int,
    upc: int,
    distribution_types: list[int],
    delete_existing_opt_outs: bool = True,
) -> None:
    with db_connector.db_session(transaction=True) as session:
        if delete_existing_opt_outs:
            session.execute(
                text(
                    """
                    DELETE
                    FROM release_default_dms_master_restriction
                    WHERE release_id = :product_id
                    """
                ),
                {"product_id": product_id},
            )

        if distribution_types:
            insert_data = [
                {
                    "distribution_type_id": distribution_type_id,
                    "upc": upc,
                    "release_id": product_id,
                }
                for distribution_type_id in distribution_types
            ]

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