"""Utils for database operations.

Note: this is strange to have methods for tests that is in the application
directory. It probably should be in tests since we aren't looking to get
metrics like code coverage.
"""

from sqlalchemy import (
    BigInteger,
    Column,
    Date,
    DateTime,
    Integer,
    MetaData,
    String,
    Table,
    UniqueConstraint,
    func,
    text,
)

from carveouts.connectors.mysql import db_connector

contracts_table_data = [
    # Happy path: dms_carve_out customer_id 1 doesn't exist in customer_master,
    # ids 2 and 3 resolve to iTunes US/GB; territory_carve_out resolves to US/GB.
    {
        "id": 10,
        "vendor_id": 7123,
        "dms_carve_out": "1,2,3",
        "territory_carve_out": "1,3",
        "cont_start": func.now(),
    },
    # Empty-string CSVs.
    {
        "id": 11,
        "vendor_id": 7123,
        "dms_carve_out": "",
        "territory_carve_out": "",
        "cont_start": func.now(),
    },
    # NULL CSVs.
    {
        "id": 12,
        "vendor_id": 7123,
        "dms_carve_out": None,
        "territory_carve_out": None,
        "cont_start": func.now(),
    },
]

vendor = [
    {"vendor_id": 7123, "status": "signed"},
    {"vendor_id": 1111, "status": "signed"},
]

vendor_contract_distribution_type_data = [
    {"vendor_contract_id": 10, "distribution_type_id": 1, "new_store_default": "Y"},
]

vendor_dms_master_restriction_data = [
    {
        "customer_master_master_id": 1,
        "distribution_type_id": 1,
        "vendor_contract_id": 10,
    }
]

customer_master_master_data = [
    {"customer_name": "iTunes/Apple", "customer_master_master_id": 1},
    {"customer_name": "Amazon Digital Services", "customer_master_master_id": 187},
    {"customer_name": "Spotify", "customer_master_master_id": 286},
    {"customer_name": "non-existent store", "customer_master_master_id": 101},
]

customer_master_data = [
    {
        "customer_id": 2,
        "customer_master_master_id": 1,
        "name": "iTunes US",
        "territory": 1,
    },
    {
        "customer_id": 3,
        "customer_master_master_id": 1,
        "name": "iTunes GB",
        "territory": 3,
    },
    {
        "customer_id": 4,
        "customer_master_master_id": 1,
        "name": "iTunes FR",
        "territory": 6,
    },
    {
        "customer_id": 5,
        "customer_master_master_id": 187,
        "name": "Amazon US",
        "territory": 1,
    },
    {
        "customer_id": 6,
        "customer_master_master_id": 187,
        "name": "Amazon GB",
        "territory": 3,
    },
    {
        "customer_id": 7,
        "customer_master_master_id": 187,
        "name": "Amazon FR",
        "territory": 6,
    },
]

country_data = [
    {
        "id": 1,
        "name": "USA",
        "country_code": "US",
    },
    {
        "id": 2,
        "name": "Canada",
        "country_code": "CA",
    },
    {
        "id": 3,
        "name": "United Kingdom",
        "country_code": "GB",
    },
    {
        "id": 6,
        "name": "France",
        "country_code": "FR",
    },
]

releases = [
    {
        "release_id": 123,
        "upc": 1234567890,
        "artist_id": 1,
        "release_name": "test release",
        "label": "My new label",
        "subaccount_id": 1,
    },
    {
        "release_id": 124,
        "upc": 1234567899,
        "artist_id": 1,
        "release_name": "test release 1",
        "label": "My new label 1",
        "subaccount_id": 1,
    },
]

release_dms_master_restriction = [
    {
        "restriction_id": 1,
        "customer_master_master_id": 1,
        "distribution_type_id": 1,
        "upc": 1234567890,
        "updated_by": 89,
        "release_id": 123,
    }
]

release_dms_restriction = [
    {"restriction_id": 1, "upc": 1234567890, "dms_customer_id": 2, "release_id": 123}
]

release_territory_restriction = [
    {"restriction_id": 1, "upc": 1234567890, "country_id": 1, "release_id": 123}
]

subaccount = [
    {
        "subaccount_id": 1,
        "vendor_id": 7123,
    },
    {
        "subaccount_id": 2,
        "vendor_id": 2222,
    },
]

subaccount_dms_master_restriction = [
    {
        "dms_master_restriction_id": 1,
        "customer_master_master_id": 1,
        "subaccount_id": 1,
    }
]

subaccount_dms_restriction = [
    {"dms_restriction_id": 1, "dms_id": 2, "subaccount_id": 1}
]

subaccount_territory_restriction = [
    {"territory_restriction_id": 1, "country_id": 1, "subaccount_id": 1}
]

release_default_dms_master_restriction = [
    {
        "default_restriction_id": 1,
        "distribution_type_id": 1,
        "upc": 1234567890,
        "release_id": 123,
    }
]

# create table model definitions
meta = MetaData()
vendor_table = Table(
    "vendor",
    meta,
    Column("vendor_id", Integer),
    Column("company_brand_id", Integer, default=0),
    Column("status", String),
)
vendor_contract_table = Table(
    "vendor_contract",
    meta,
    Column("id", Integer),
    Column("vendor_id", Integer),
    Column("dms_carve_out", String),
    Column("territory_carve_out", String),
    Column("cont_start", Date),
)

vendor_dms_master_restriction_table = Table(
    "vendor_dms_master_restriction",
    meta,
    Column("customer_master_master_id", Integer),
    Column("distribution_type_id", Integer),
    Column("vendor_contract_id", Integer),
    Column("updated_by", Integer),
    UniqueConstraint(
        "vendor_contract_id",
        "customer_master_master_id",
        "distribution_type_id",
        name="unique_vendor_restriction",
    ),
)
customer_master_master_table = Table(
    "customer_master_master",
    meta,
    Column("customer_name", String),
    Column("customer_master_master_id", Integer),
)
customer_master_table = Table(
    "customer_master",
    meta,
    Column("customer_id", Integer),
    Column("name", String),
    Column("customer_master_master_id", Integer),
    Column("territory", Integer),
)
country_table = Table(
    "country",
    meta,
    Column("id", Integer),
    Column("name", String),
    Column("country_code", String),
    Column("abbrivation", String, default=""),  # column needs to be dropped
)
releases_table = Table(
    "releases",
    meta,
    Column("release_id", Integer),
    Column("upc", BigInteger),
    Column("artist_id", Integer),
    Column("release_name", String),
    Column("label", String),
    Column("subaccount_id", Integer),
    Column("project_id", Integer, default=0),
)
release_dms_master_restriction_table = Table(
    "release_dms_master_restriction",
    meta,
    Column("restriction_id", Integer),
    Column("customer_master_master_id", Integer),
    Column("distribution_type_id", Integer),
    Column("upc", BigInteger),
    Column("updated_by", Integer),
    Column("release_id", 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",
    ),
)
release_dms_restriction_table = Table(
    "release_dms_restriction",
    meta,
    Column("restriction_id", Integer),
    Column("upc", BigInteger),
    Column("dms_customer_id", Integer),
    Column("release_id", Integer),
    UniqueConstraint(
        "upc",
        "dms_customer_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "dms_customer_id",
        name="new_unique_key",
    ),
)
release_territory_restriction_table = Table(
    "release_territory_restriction",
    meta,
    Column("restriction_id", Integer),
    Column("upc", BigInteger),
    Column("country_id", Integer),
    Column("release_id", Integer),
    UniqueConstraint(
        "upc",
        "country_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "country_id",
        name="new_unique_key",
    ),
)
subaccount_table = Table(
    "subaccount",
    meta,
    Column("subaccount_id", Integer),
    Column("vendor_id", Integer),
    Column("subaccount_name", String, default=""),
    Column("date_created", DateTime, default=func.now()),
)
subaccount_dms_restriction_table = Table(
    "subaccount_dms_restriction",
    meta,
    Column("dms_restriction_id", Integer),
    Column("dms_id", Integer),
    Column("subaccount_id", Integer),
    Column("updated_by", Integer, default=0),
)
subaccount_territory_restriction_table = Table(
    "subaccount_territory_restriction",
    meta,
    Column("territory_restriction_id", Integer),
    Column("country_id", Integer),
    Column("subaccount_id", Integer),
    Column("updated_by", Integer, default=0),
)
vendor_contract_distribution_type_table = Table(
    "vendor_contract_distribution_type",
    meta,
    Column("vendor_contract_id", Integer),
    Column("distribution_type_id", Integer),
    Column("new_store_default", String),
)
release_default_dms_master_restriction_table = Table(
    "release_default_dms_master_restriction",
    meta,
    Column("default_restriction_id", Integer),
    Column("distribution_type_id", Integer),
    Column("upc", BigInteger),
    Column("release_id", Integer),
    UniqueConstraint(
        "upc",
        "distribution_type_id",
        name="old_unique_key",
    ),
    UniqueConstraint(
        "release_id",
        "distribution_type_id",
        name="new_unique_key",
    ),
)
subaccount_dms_master_restriction_table = Table(
    "subaccount_dms_master_restriction",
    meta,
    Column("dms_master_restriction_id", Integer),
    Column("customer_master_master_id", Integer),
    Column("subaccount_id", Integer),
    Column("updated_by", Integer, default=0),
)


def seed_vendor() -> None:
    """Insert test data into vendor table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE vendor;"))
        session.execute(vendor_table.insert().values(vendor))


def seed_vendor_contract_table() -> None:
    """Insert test data into vendor_contract table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE vendor_contract;"))
        session.execute(vendor_contract_table.insert().values(contracts_table_data))


def seed_vendor_dms_master_restriction() -> None:
    """Insert test data into vendor_dms_master_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE vendor_dms_master_restriction;"))
        session.execute(
            vendor_dms_master_restriction_table.insert().values(
                vendor_dms_master_restriction_data
            )
        )


def seed_customer_master_master() -> None:
    """Insert test data into customer_master_master table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE customer_master_master;"))
        session.execute(
            customer_master_master_table.insert().values(customer_master_master_data)
        )


def seed_customer_master() -> None:
    """Insert test data into customer_master table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE customer_master;"))
        session.execute(customer_master_table.insert().values(customer_master_data))


def seed_country() -> None:
    """Insert test data into country table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE country;"))
        session.execute(country_table.insert().values(country_data))


def seed_releases() -> None:
    """Insert data to releases table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE releases;"))
        session.execute(releases_table.insert().values(releases))


def seed_release_dms_master_restriction() -> None:
    """Insert data to release_dms_master_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE release_dms_master_restriction;"))
        session.execute(
            release_dms_master_restriction_table.insert().values(
                release_dms_master_restriction
            )
        )


def seed_release_dms_restriction() -> None:
    """Insert data to release_dms_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE release_dms_restriction;"))
        session.execute(
            release_dms_restriction_table.insert().values(release_dms_restriction)
        )


def seed_release_territory_restriction() -> None:
    """Insert data to release_territory_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE release_territory_restriction;"))
        session.execute(
            release_territory_restriction_table.insert().values(
                release_territory_restriction
            )
        )


def seed_vendor_contract_distribution_type() -> None:
    """Insert data to vendor_contract_distribution_type table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE vendor_contract_distribution_type;"))
        session.execute(
            vendor_contract_distribution_type_table.insert().values(
                vendor_contract_distribution_type_data
            )
        )


def seed_release_default_dms_master_restriction() -> None:
    """Insert data to release_default_dms_master_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE release_default_dms_master_restriction;"))
        session.execute(
            release_default_dms_master_restriction_table.insert().values(
                release_default_dms_master_restriction
            )
        )


def seed_subaccount() -> None:
    """Insert data to subaccount table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE subaccount;"))
        session.execute(subaccount_table.insert().values(subaccount))


def seed_subaccount_dms_restriction() -> None:
    """Insert data to subaccount_dms_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE subaccount_dms_restriction;"))
        session.execute(
            subaccount_dms_restriction_table.insert().values(subaccount_dms_restriction)
        )


def seed_subaccount_territory_restriction() -> None:
    """Insert data to subaccount_territory_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE subaccount_territory_restriction;"))
        session.execute(
            subaccount_territory_restriction_table.insert().values(
                subaccount_territory_restriction
            )
        )


def seed_subaccount_dms_master_restriction() -> None:
    """Insert data to subaccount_dms_master_restriction table."""
    with db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE subaccount_dms_master_restriction;"))
        session.execute(
            subaccount_dms_master_restriction_table.insert().values(
                subaccount_dms_master_restriction
            )
        )