"""Utility functions to support database interactions in tests."""

from datetime import datetime

from sqlalchemy import text

from vectororder.connectors.mysql import art_db_connector, dd_db_connector
from vectororder.models.schemas import EncodingOrderType


def create_job(
    *,
    encoding_queue_id: int = 123,
    encoding_queue_detail_id: int = 456,
    encoding_queue_detail_status: str = "ready_to_encode",
    upc: int = 199066946312,
    meta_update: str = "Y",
    order_type: str = "release",
    dms_master_master_id: int = 286,
    encoding_started: datetime = datetime.strptime(
        "2025-07-04 01:03:37", "%Y-%m-%d %H:%M:%S"
    ),
    delivery_started: datetime = datetime.strptime(
        "2025-07-04 01:03:37", "%Y-%m-%d %H:%M:%S"
    ),
) -> None:
    """Create a job in the database."""
    with dd_db_connector.db_session() as session:
        session.execute(
            text(
                """
                INSERT INTO encoding_queue (
                    encoding_queue_id,
                    meta_update,
                    encoding_order_type
                ) VALUES (
                    :encoding_queue_id,
                    :meta_update,
                    :order_type
                )
                """
            ),
            {
                "encoding_queue_id": encoding_queue_id,
                "meta_update": meta_update,
                "order_type": order_type,
            },
        )
        session.execute(
            text(
                """
                INSERT INTO encoding_queue_detail (
                    encoding_queue_detail_id,
                    encoding_queue_id,
                    upc,
                    dms_master_master_id,
                    status,
                    encoding_started,
                    delivery_started
                ) VALUES (
                    :encoding_queue_detail_id,
                    :encoding_queue_id,
                    :upc,
                    :dms_master_master_id,
                    :encoding_queue_detail_status,
                    :encoding_started,
                    :delivery_started
                )
                """
            ),
            {
                "encoding_queue_detail_id": encoding_queue_detail_id,
                "encoding_queue_id": encoding_queue_id,
                "upc": upc,
                "dms_master_master_id": dms_master_master_id,
                "encoding_queue_detail_status": encoding_queue_detail_status,
                "encoding_started": encoding_started,
                "delivery_started": delivery_started,
            },
        )


def create_delivery_history(
    *,
    upc: int = 199066946312,
    customer_master_master_id: int = 286,
) -> None:
    """Create a delivery history record in the database."""
    with art_db_connector.db_session() as session:
        session.execute(
            text(
                """
                INSERT INTO delivery_history (
                    upc,
                    customer_master_master_id,
                    encoder_id,
                    date_delivered,
                    package_size
                ) VALUES (
                    :upc,
                    :customer_master_master_id,
                    :encoder_id,
                    :date_delivered,
                    :package_size
                )
                """
            ),
            {
                "upc": upc,
                "customer_master_master_id": customer_master_master_id,
                "encoder_id": 18,
                "date_delivered": "2025-11-18",
                "package_size": 0,
            },
        )


def create_product(
    *,
    release_id: int = 123,
    project_id: int = 1,
    upc: int = 199066946312,
    distribution_format_id: int = 1,
    not_for_distribution: str = "N",
    release_status: str = "in_content",
) -> None:
    """Create a product in the database."""
    with art_db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE releases"))
        session.execute(
            text(
                """
                INSERT INTO releases (
                    release_id,
                    project_id,
                    upc,
                    distribution_format_id,
                    not_for_distribution,
                    release_status
                ) VALUES (
                    :release_id,
                    :project_id,
                    :upc,
                    :distribution_format_id,
                    :not_for_distribution,
                    :release_status
                )
                """
            ),
            {
                "release_id": release_id,
                "project_id": project_id,
                "upc": upc,
                "distribution_format_id": distribution_format_id,
                "not_for_distribution": not_for_distribution,
                "release_status": release_status,
            },
        )
        session.execute(
            text(
                """
                INSERT INTO distribution_format (
                    distribution_format_id,
                    distribution_format_media_id,
                    distribution_format_media_format_id,
                    display_flag,
                    context_type
                ) VALUES (
                    :distribution_format_id,
                    :distribution_format_media_id,
                    :distribution_format_media_format_id,
                    :display_flag,
                    :context_type
                )
                """
            ),
            [
                {
                    "distribution_format_id": 1,
                    "distribution_format_media_id": 68,
                    "distribution_format_media_format_id": 2,
                    "display_flag": "Y",
                    "context_type": "digital",
                },
                {
                    "distribution_format_id": 57,
                    "distribution_format_media_id": 69,
                    "distribution_format_media_format_id": 3,
                    "display_flag": "Y",
                    "context_type": "digital",
                },
                {
                    "distribution_format_id": 70,
                    "distribution_format_media_id": 2,
                    "distribution_format_media_format_id": 1,
                    "display_flag": "Y",
                    "context_type": "physical",
                },
            ],
        )


def create_track(
    *,
    id_: int = 456,
    release_id: int = 123,
    track_type: str = "music",
    offer_type: str = "all",
) -> None:
    """Create a track in the database."""
    with art_db_connector.db_session() as session:
        session.execute(
            text(
                """
                INSERT INTO track (
                    id,
                    release_id,
                    track_type,
                    offer_type
                ) VALUES (
                    :id,
                    :release_id,
                    :track_type,
                    :offer_type
                )
                """
            ),
            {
                "id": id_,
                "release_id": release_id,
                "track_type": track_type,
                "offer_type": offer_type,
            },
        )


def create_store(
    *,
    customer_master_master_id: int = 286,
    hd_only: str = "N",
) -> None:
    """Create a store in the database."""
    with art_db_connector.db_session() as session:
        session.execute(
            text(
                """
                INSERT INTO customer_master_master (
                    customer_master_master_id,
                    hd_only,
                    customer_name
                ) VALUES (
                    :customer_master_master_id,
                    :hd_only,
                    :customer_name
                )
                """
            ),
            {
                "customer_master_master_id": customer_master_master_id,
                "hd_only": hd_only,
                "customer_name": "",
            },
        )
        session.execute(
            text(
                """
                INSERT INTO customer_master_master_distribution_type (
                    customer_master_master_id,
                    distribution_type_id,
                    distribution_features_ids
                ) VALUES (
                    :customer_master_master_id,
                    :distribution_type_id,
                    :distribution_features_ids
                )
                """
            ),
            {
                "customer_master_master_id": customer_master_master_id,
                "distribution_type_id": 1,
                "distribution_features_ids": "1,2,3,4,5,6,7,8",
            },
        )


def create_vendor(
    *,
    vendor_id: int = 1,
    name: str = "Test Vendor",
    label_identifier: str = "Catalog",
    api_vendor_id: int | None = None,
) -> None:
    """Create a vendor in the database."""
    with art_db_connector.db_session(turn_off_foreign_key_constraint=True) as session:
        session.execute(
            text(
                """
                INSERT INTO vendor (
                    vendor_id,
                    name,
                    label_identifier,
                    api_vendor_id,
                    company_brand_id
                ) VALUES (
                    :vendor_id,
                    :name,
                    :label_identifier,
                    :api_vendor_id,
                    :company_brand_id
                )
                """
            ),
            {
                "vendor_id": vendor_id,
                "name": name,
                "label_identifier": label_identifier,
                "api_vendor_id": api_vendor_id,
                "company_brand_id": 0,
            },
        )


def create_project(*, project_id: int = 1, vendor_id: int = 1) -> None:
    """Create a project in the database."""
    with art_db_connector.db_session() as session:
        session.execute(
            text(
                """
                INSERT INTO project (
                    project_id,
                    project_name,
                    vendor_id,
                    subaccount_id,
                    created_date_utc,
                    updated_date_utc,
                    deletions
                ) VALUES (
                    :project_id,
                    :project_name,
                    :vendor_id,
                    :subaccount_id,
                    CURRENT_TIMESTAMP,
                    CURRENT_TIMESTAMP,
                    :deletions
                )
                """
            ),
            {
                "project_id": project_id,
                "project_name": "",
                "vendor_id": vendor_id,
                "subaccount_id": 0,
                "deletions": "N",
            },
        )


def create_delivery_spec(
    *, dms_id: int, order_type: EncodingOrderType, metadata_update: str
) -> None:
    with dd_db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE dms_delivery_spec"))
        session.execute(
            text(
                """
                INSERT INTO dms_delivery_spec (
                    dms_master_master_id,
                    order_type,
                    metadata_update,
                    delivery,
                    encoding
                ) VALUES (
                    :dms_id,
                    :order_type,
                    :metadata_update,
                    :delivery,
                    :encoding
                )
                """
            ),
            {
                "dms_id": dms_id,
                "order_type": order_type.value,
                "metadata_update": metadata_update,
                "delivery": "Y",
                "encoding": "Y",
            },
        )


def create_encoding_profile(
    *, encoding_profile_id: int, encoding_profile_format: str
) -> None:
    """Create a encoding_profile in the database."""
    with dd_db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE encoding_profile;"))
        session.execute(
            text(
                """
                INSERT INTO encoding_profile (
                   encoding_profile_id,
                   format
                ) VALUES (
                   :encoding_profile_id,
                   :encoding_profile_format
                )
                """
            ),
            {
                "encoding_profile_id": encoding_profile_id,
                "encoding_profile_format": encoding_profile_format,
            },
        )


def create_dms_encoding_profile(
    *, dms_encoding_profile_id: int, dms_master_master_id: int, encoding_profile_id: int
) -> None:
    """Create a dms_encoding_profile in the database."""
    with dd_db_connector.db_session(
        transaction=True, turn_off_foreign_key_constraint=True
    ) as session:
        session.execute(text("TRUNCATE TABLE dms_encoding_profile;"))
        session.execute(
            text(
                """
                INSERT INTO dms_encoding_profile (
                    id,
                    dms_master_master_id,
                    encoding_profile_id
                ) VALUES (
                    :dms_encoding_profile_id,
                    :dms_master_master_id,
                    :encoding_profile_id
                )
                """
            ),
            {
                "dms_encoding_profile_id": dms_encoding_profile_id,
                "dms_master_master_id": dms_master_master_id,
                "encoding_profile_id": encoding_profile_id,
            },
        )