"""Utils for database operations."""

import datetime

from account import config
from account.connectors import mysql
from account.models import (
    company_brand,
    country,
    feature,
    genre,
    owner,
    parent_company,
    region,
    service_tier,
    subaccount,
    supplier,
    vend_contact,
    vendor,
)

session = mysql._account_session()

subaccount_data = [
    {
        'subaccount_id': 1,
        'subaccount_uuid': '11387154-0267-11ef-82c8-4a2888760684',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 1',
        'description': 'Subaccount Description 1',
        'date_deleted': None,
        'country_id': 1,
        'commission_override': 0.5,
        'subaccount_split_type': 'Gross',
    },
    {
        'subaccount_id': 2,
        'subaccount_uuid': '3167fe36-0267-11ef-8440-4a2888760684',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 2',
        'description': 'Subaccount Description 2',
        'date_deleted': None,
        'country_id': 1,
        'commission_override': 0.6,
        'subaccount_split_type': 'Gross',
    },
    {
        'subaccount_id': 3,
        'subaccount_uuid': 'de327c69-8cbb-44d7-a7d9-441392400f21',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 3',
        'description': 'Subaccount Description 3',
        'date_deleted': None,
        'country_id': 2,
        'commission_override': 0.85,
        'subaccount_split_type': 'Gross',
    },
    {
        'subaccount_id': 4,
        'subaccount_uuid': '77975ab6-e4bd-4564-afcb-09cc8618d359',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 4',
        'description': 'Subaccount Description 4',
        'date_deleted': None,
        'country_id': 2,
        'commission_override': 0.4,
        'subaccount_split_type': 'Gross',
    },
    {
        'subaccount_id': 5,
        'subaccount_uuid': '56deca6e-0267-11ef-b0e7-4a2888760684',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 5',
        'description': 'Subaccount Description 5',
        'date_deleted': None,
        'country_id': 3,
        'commission_override': 0.5,
        'subaccount_split_type': 'Gross',
    },
    {
        'subaccount_id': 6,
        'subaccount_uuid': 'dc7afecc-4ae8-4b29-8949-bbdc58d6634a',
        'vendor_id': 1,
        'subaccount_name': 'Subaccount 6',
        'description': 'Subaccount Description 6',
        'date_deleted': datetime.datetime.now(),
        'country_id': 3,
        'commission_override': 0.5,
        'subaccount_split_type': 'Net',
    },
    {
        'subaccount_id': 7,
        'subaccount_uuid': 'b1e2f3a4-0000-4000-8000-000000000002',
        'vendor_id': 2,
        'subaccount_name': 'Subaccount 7',
        'description': 'Subaccount Description 7',
        'date_deleted': None,
        'country_id': 1,
        'commission_override': 0.5,
        'subaccount_split_type': 'Gross',
    },
]

vendor_data = [
    {
        'vendor_id': 1,
        'is_distributor': 'Y',
        'owner': 'TEST1',
        'country': 6,
        'status': 'signed',
        'transfer_pricing_country': None,
        'website': None,
        'est_total_tracks': None,
        'company': None,
        'company_brand_id': 1,
        'priority': None,
        'show_release_builder': None,
        'support_contact_email': None,
        'name': 'Test',
        'projected_first_year_revenue': None,
        'est_total_releases': 0,
        'label_summary': None,
        'is_owned': None,
        'last_update': None,
        'primary_genre': 12,
        'assigned_to': None,
        'region': 2,
        'wel_email_sender': None,
        'wel_email_send_date': None,
        'external_identifier_1': None,
        'date_created': None,
        'vendor_uuid': '87682992-bff2-40ff-aa75-18eb6214679e',
        'first_statement_period': 1,
        'relationship_notes': 'Note A',
    },
    {
        'vendor_id': 2,
        'is_distributor': 'N',
        'owner': 'TEST2',
        'country': 1,
        'status': 'signed',
        'transfer_pricing_country': None,
        'website': None,
        'est_total_tracks': None,
        'company': None,
        'company_brand_id': 2,
        'priority': None,
        'show_release_builder': None,
        'support_contact_email': None,
        'name': 'Test',
        'projected_first_year_revenue': None,
        'est_total_releases': 0,
        'label_summary': None,
        'is_owned': None,
        'last_update': None,
        'primary_genre': 12,
        'assigned_to': None,
        'region': 2,
        'wel_email_sender': None,
        'wel_email_send_date': None,
        'external_identifier_1': 'test_external_id',
        'date_created': None,
        'vendor_uuid': '6097ad5a-2186-4dbe-8286-82ce933d3538',
        'first_statement_period': 229,
        'relationship_notes': 'Note B',
    },
    {
        'vendor_id': 3,
        'is_distributor': 'Y',
        'owner': 'TEST3',
        'country': 6,
        'status': 'signed',
        'label_identifier': None,
        'contact_email': None,
        'migrated_to_abacus': 0,
        'quarterback_label_manager': None,
        'date_created': datetime.date(2023, 6, 8),
        'last_update': datetime.date(2023, 7, 10),
        'sap_vendor_id': None,
        'transfer_pricing_country': 'sme_france',
        'website': 'label.com',
        'est_total_tracks': 0,
        'company': 'TEST',
        'company_brand_id': 1,
        'priority': 3,
        'show_release_builder': 'Y',
        'support_contact_email': '',
        'name': 'Test',
        'projected_first_year_revenue': 0.000000,
        'est_total_releases': 1,
        'label_summary': 'Test Summary',
        'is_owned': 'No',
        'primary_genre': 20,
        'assigned_to': 1,
        'region': 2,
        'wel_email_sender': 1234,
        'wel_email_send_date': datetime.date(2023, 7, 10),
        'external_identifier_1': None,
        'vendor_uuid': 'e429b770-9db6-4f98-b48d-7d9b8ced7e6c',
        'first_statement_period': 265,
        'relationship_notes': 'Note A',
    },
    {
        'vendor_id': 4,
        'is_distributor': 'Y',
        'owner': 'TEST3',
        'country': 6,
        'status': 'signed',
        'label_identifier': None,
        'contact_email': None,
        'migrated_to_abacus': 0,
        'quarterback_label_manager': None,
        'date_created': datetime.date(2023, 6, 8),
        'last_update': datetime.date(2023, 7, 10),
        'sap_vendor_id': None,
        'transfer_pricing_country': 'sme_france',
        'website': 'label.com',
        'est_total_tracks': 0,
        'company': 'TEST another company brand',
        'company_brand_id': 2,
        'priority': 3,
        'show_release_builder': 'Y',
        'support_contact_email': '',
        'name': 'Test',
        'projected_first_year_revenue': 0.000000,
        'est_total_releases': 1,
        'label_summary': 'Test Summary',
        'is_owned': 'No',
        'primary_genre': 20,
        'assigned_to': 1,
        'region': 2,
        'wel_email_sender': 1234,
        'wel_email_send_date': datetime.date(2023, 7, 10),
        'external_identifier_1': None,
        'vendor_uuid': '42d022c2-50e4-4f5c-af30-f10dca6b8a50',
        'first_statement_period': 1,
        'relationship_notes': 'Note B',
    },
]

vendor_closers = [
    {'vendor_id': 1, 'orchadmin_user_id': 2},
    {'vendor_id': 1, 'orchadmin_user_id': 3},
    {'vendor_id': 2, 'orchadmin_user_id': 2},
    {'vendor_id': 3, 'orchadmin_user_id': 3},
]

owner_data = [
    {
        'owner_id': 1,
        'owner_abbrivation': 'TEST1',
        'owner_name': 'Test Owner Name 1',
        'owner_type': 'aggregator',
        'is_sme': 'Y',
    },
    {
        'owner_id': 2,
        'owner_abbrivation': 'TEST2',
        'owner_name': 'Test Owner Name 2',
        'owner_type': 'drr',
        'is_sme': 'N',
    },
]

supplier_data = [
    {'vendor_id': 1, 'store_id': 1},
    {'vendor_id': 1, 'store_id': 2},
    {'vendor_id': 2, 'store_id': 2},
]

vendor_restricted_features_data = [
    {'vendor_restricted_features_id': 1, 'vendor_id': 1, 'feature_id': 3}
]

vend_contact_data = [
    {'id': 1, 'vendor_id': 1, 'subaccount_id': 1, 'active': 'N'},
    {'id': 2, 'vendor_id': 1, 'subaccount_id': 1, 'active': 'Y'},
    {'id': 3, 'vendor_id': 1, 'subaccount_id': 2, 'active': 'N'},
    {'id': 4, 'vendor_id': 1, 'subaccount_id': 2, 'active': 'Y'},
]

features_data = [
    {'feature_id': 1, 'feature_name': 'One'},
    {'feature_id': 2, 'feature_name': 'Two'},
    {'feature_id': 3, 'feature_name': 'Three'},
]

orchadmin_users_data = [
    {'id': 1, 'f_name': 'test1', 'l_name': 'user1', 'active': 'Y'},
    {'id': 2, 'f_name': 'test', 'l_name': 'user', 'active': 'Y'},
    {'id': 3, 'f_name': 'test2', 'l_name': 'user2', 'active': 'N'},
]

orchadmin_user_roles_data = [
    {'id': 3, 'orchadmin_user_id': 2, 'orchadmin_role_id': 3},
    {'id': 4, 'orchadmin_user_id': 3, 'orchadmin_role_id': 4},
]

genre_data = [
    {'genre_id': 20, 'genre': 'Blues'},
    {'genre_id': 12, 'genre': 'Classical'},
]

country_data = [
    {'id': 200, 'name': 'Aruba', 'country_code': 'AW', 'iso3166a3': 'ABW'},
    {'id': 28, 'name': 'Bangladesh', 'country_code': 'BD', 'iso3166a3': 'BGD'},
    {'id': 6, 'name': 'France', 'country_code': 'FR', 'iso3166a3': 'FRA'},
]

region_data = [
    {'region_id': 4, 'region_code': 'OZ', 'region': 'Australia/NZ'},
    {'region_id': 2, 'region_code': 'CA', 'region': 'Canada'},
]

soundscan_data = [{'vendor_id': 5, 'country_id': 1, 'soundscan_code': 't1', 'subaccount_id': None}]

parent_company_data = [
    {
        'id': 1,
        'name': 'sme',
        'uuid': 'f1594122-7f99-4916-b103-08b0444c7b46',
        'display_name': 'Sony Music',
    },
    {
        'id': 2,
        'name': 'theorchard',
        'uuid': '955a1bbd-b623-4ea1-ab5f-8d6620c442fb',
        'display_name': 'The Orchard',
    },
]

company_brand_data = [
    {
        'id': 1,
        'name': 'test_theorchard',
        'uuid': 'd25a4cd1-e820-45f2-be5c-56edcfeb8298',
        'display_name': 'The Orchard (Test)',
        'logo_url': 'https://cdn.theorchard.io/assets/orchard/icons/brand.svg',
        'parent_company_id': 2,
    },
    {
        'id': 2,
        'name': 'test_awal',
        'uuid': '31f4f0f0-cbb4-4a2c-9eb0-d7288c5a2588',
        'display_name': 'AWAL (Test)',
        'logo_url': 'https://cdn.theorchard.io/assets/awal/icons/brand.svg',
        'parent_company_id': 2,
    },
    {
        'id': 3,
        'name': 'test_sme',
        'uuid': '9b36a627-4be9-4762-b790-5628676f5f90',
        'display_name': 'Sony Music (Test)',
        'logo_url': None,
        'parent_company_id': 1,
    },
]

service_tier_data = [
    {
        'name': 'tier-1',
        'uuid': '1ed7aac0-ceb6-4c09-9166-afda8f349316',
        'display_name': 'Tier 1',
    },
    {'name': 'tier-2', 'uuid': '2ed7aac0-ceb6-4c09-9166-afda8f349316', 'display_name': 'Tier 2'},
]

vendor_service_tier_data = [
    {
        'vendor_id': 1,
        'service_tier_uuid': '1ed7aac0-ceb6-4c09-9166-afda8f349316',
    },
    {
        'vendor_id': 2,
        'service_tier_uuid': '2ed7aac0-ceb6-4c09-9166-afda8f349316',
    },
]


def create_tables():
    """Create tables defined in account.models."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    mysql.BaseModel.metadata.drop_all(mysql._account_engine)
    mysql.BaseModel.metadata.create_all(mysql._account_engine)


def seed_vend_contact_table():
    """Insert test data into vend_contact table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(vend_contact.VendContact.__table__.insert().values(vend_contact_data))
    session.commit()
    session.close()


def seed_subaccount_table():
    """Insert test data into subaccount table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(subaccount.Subaccount.__table__.insert().values(subaccount_data))
    session.commit()
    session.close()


def seed_vendor_table():
    """Insert test data for distributors into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return
    session.execute(vendor.Vendor.__table__.insert().values(vendor_data))
    session.commit()
    session.close()


def seed_vendor_closers_table():
    """Insert test data for distributors into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return
    session.execute(vendor.VendorClosers.__table__.insert().values(vendor_closers))
    session.commit()
    session.close()


def seed_owner_table():
    """Insert test data for owners into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(owner.Owner.__table__.insert().values(owner_data))
    session.commit()
    session.close()


def seed_physical_supplychain_table():
    """Insert test data into table.

    the table is vendor_physical_supplychain_default_selection
    """
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(supplier.Supplier.__table__.insert().values(supplier_data))
    session.commit()
    session.close()


def seed_vendor_restricted_features_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(
        feature.VendorRestrictedFeatures.__table__.insert().values(vendor_restricted_features_data)
    )
    session.commit()
    session.close()


def seed_features_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(feature.Features.__table__.insert().values(features_data))
    session.commit()
    session.close()


def seed_vendor_assigned_to_data():
    """Insert test data into the table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute("""
        INSERT INTO `vendor`
        (`vendor_id`, `is_distributor`, `owner`, `country`, `status`, `assigned_to`)
        VALUES
        (24, 'Y', 'TEST1', 1, 'signed', 1);
    """)
    session.execute("""
        INSERT INTO `orchadmin_users`
        (id, f_name, l_name)
        VALUES
        (1, 'foo', 'bar');
    """)
    session.execute("""
            INSERT INTO `soundscan_codes`
            (vendor_id, country_id, soundscan_code, subaccount_id)
            VALUES
            (5, 1, 't1', NULL);
        """)
    session.commit()
    session.close()


def seed_vendor_secondary_internal_contact_data():
    """Insert test data into the table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute("""
        INSERT INTO `vendor`
        (`vendor_id`, `is_distributor`, `owner`, `country`,
            `status`, `assigned_to`, `quarterback_label_manager`)
        VALUES
        (13, 'Y', 'TEST1', 1, 'signed', 3, 4);
    """)
    session.execute("""
        INSERT INTO `vendor`
        (`vendor_id`, `is_distributor`, `owner`, `country`,
            `status`, `assigned_to`, `quarterback_label_manager`)
        VALUES
        (14, 'Y', 'TEST2', 1, 'signed', 3, NULL);
    """)
    session.execute("""
        INSERT INTO `orchadmin_users`
        (id, f_name, l_name)
        VALUES
        (3, 'foo', 'bar');
    """)
    session.execute("""
        INSERT INTO `orchadmin_users`
        (id, f_name, l_name)
        VALUES
        (4, 'baz', 'qux');
    """)
    session.commit()
    session.close()


def seed_genre_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(genre.Genre.__table__.insert().values(genre_data))
    session.commit()
    session.close()


def seed_country_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(country.Country.__table__.insert().values(country_data))
    session.commit()
    session.close()


def seed_region_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(region.Region.__table__.insert().values(region_data))
    session.commit()
    session.close()


def seed_soundscan_codes_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(vendor.SoundScanCode.__table__.insert().values(soundscan_data))
    session.commit()
    session.close()


def seed_parent_company_table():
    """Insert test data into parent_company."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(parent_company.ParentCompany.__table__.insert().values(parent_company_data))
    session.commit()
    session.close()


def seed_company_brand_table():
    """Insert test data into table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(company_brand.CompanyBrand.__table__.insert().values(company_brand_data))
    session.commit()
    session.close()


def seed_service_tier_table():
    """Insert test data into service_tier table."""

    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(service_tier.ServiceTier.__table__.insert().values(service_tier_data))
    session.commit()
    session.close()


def seed_vendor_service_tier_table():
    """Insert test data into vendor_service_tier table."""

    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    session.execute(service_tier.vendor_service_tier.insert().values(vendor_service_tier_data))
    session.commit()
    session.close()