"""Data layer for orders."""

from typing import Any

from sqlalchemy import (
    TEXT,
    Column,
    Date,
    DateTime,
    Enum,
    ForeignKey,
    Integer,
    MetaData,
    Table,
    func,
    insert,
    select,
    text,
    update,
)
from sqlalchemy.exc import OperationalError
from sqlalchemy.orm import Session
from werkzeug.exceptions import NotFound

from vectororder import config
from vectororder.api import app
from vectororder.connectors.dynamodb import ddb_connector
from vectororder.connectors.mysql import art_db_connector
from vectororder.constants import error, fields
from vectororder.exceptions import OrderNotFoundError
from vectororder.models.schemas import OrderData, OrderStatus

metadata = MetaData()
encoding_order_table = Table(
    "encoding_order",
    metadata,
    Column("encoding_order_id", Integer, primary_key=True),
    Column("encoder_id", Integer),
    Column("entry_date", DateTime),
    Column("order_status", Enum("open", "close")),
    Column("orchadmin_user_id", Integer),
    Column("dms_list", TEXT),
    Column("encoder_delivery_date", Date),  # this is the order closed date
    Column("priority", Integer),
    Column("meta_update", Enum("Y", "N")),
)

encoding_order_detail_table = Table(
    "encoding_order_detail",
    metadata,
    Column("encoding_order_detail_id", Integer, primary_key=True),
    Column(
        "encoding_order_id",
        Integer,
        ForeignKey("encoding_order_table.encoding_order_id"),
    ),
    Column("upc", Integer),
)


encoding_order_detail_dms_table = Table(
    "encoding_order_detail_dms",
    metadata,
    Column(
        "encoding_order_detail_id",
        Integer,
        ForeignKey("encoding_order_detail_table.encoding_order_detail_id"),
        primary_key=True,
    ),
    Column("dms_customer_id", Integer, primary_key=True),
)

customer_master_table = Table(
    "customer_master",
    metadata,
    Column("customer_id", Integer, primary_key=True),
    Column("customer_master_master_id", Integer),
)


def get_order_from_dynamo(order_id: int) -> dict[str, Any]:
    """Get vector order from DynamoDB.

    Args:
        order_id (int): Vector order ID.
    Returns:
        dict: vector order
    """
    result = ddb_connector.get_item(
        config.DDB_ORDERS_TABLE, fields.DDB_ORDERS_TABLE_KEY, str(order_id)
    )
    if not result:
        raise NotFound(error.ERROR_MESSAGE_ORDER_NOT_FOUND)
    return result


def get_orders_from_dynamodb(order_ids: list[int]) -> list[dict[str, Any]]:
    """Get vector order from DynamoDB.

    Args:
        order_ids (list): Vector order ID.

    Returns:
        response.Response: DynamoDB vector orders iterator
        wrapped in owsresponse.Response.
    """
    projection_expression = ",".join(
        [
            fields.DDB_ORDERS_TABLE_KEY,
            fields.ORDER_CREATED_AT,
            fields.ORDER_ENCODER_ID,
            fields.ORDER_USER_ID,
            fields.ORDER_PRIORITY,
            fields.ORDER_METADATA_UPDATE,
        ]
    )
    result = ddb_connector.batch_get_items(
        config.DDB_ORDERS_TABLE,
        fields.DDB_ORDERS_TABLE_KEY,
        [str(order_id) for order_id in order_ids],
        projection_expression,
        True,
    )
    return result


def create_order(order_data: OrderData) -> int:
    """Create vector order."""
    insert_stmt = insert(encoding_order_table).values(
        dms_list=",".join(list(map(str, order_data.store_ids))),
        encoder_id=order_data.encoder_id,
        entry_date=func.now(),
        meta_update=("Y" if order_data.is_meta_update else "N"),
        order_status=order_data.order_status.value,
        orchadmin_user_id=order_data.user_id,
        priority=order_data.priority,
        encoder_delivery_date=func.current_date()
        if order_data.order_status == OrderStatus.CLOSED
        else None,
    )

    with art_db_connector.db_session(transaction=True) as session:
        session.execute(insert_stmt)
        result = session.execute(
            text(
                """
                SELECT LAST_INSERT_ID();
                """
            )
        )
        last_inserted_id = result.scalar_one()

        if last_inserted_id and order_data.upcs:
            _add_order_details(last_inserted_id, order_data.upcs, session)

        return int(last_inserted_id)


def update_order(
    order_id: int, upcs: set[int], order_status: OrderStatus | None
) -> None:
    """Update an order."""
    with art_db_connector.db_session(transaction=True) as session:
        try:
            result = session.execute(
                select(encoding_order_table)
                .where(
                    encoding_order_table.c.encoding_order_id == order_id,
                    encoding_order_table.c.order_status == OrderStatus.OPEN.value,
                )
                .with_for_update(nowait=True)
            )

            if result.one_or_none():
                if upcs:
                    # compare against existing UPC's in this order
                    result = session.execute(
                        select(encoding_order_detail_table.c.upc).where(
                            encoding_order_detail_table.c.encoding_order_id == order_id
                        )
                    )
                    existing_upcs = {row["upc"] for row in result.mappings()}
                    new_upcs = upcs - existing_upcs

                    if new_upcs:
                        _add_order_details(order_id, new_upcs, session)

                if order_status == OrderStatus.CLOSED:
                    session.execute(
                        update(encoding_order_table)
                        .where(encoding_order_table.c.encoding_order_id == order_id)
                        .values(
                            order_status=order_status.value,
                            encoder_delivery_date=func.current_date(),
                        )
                    )
            else:
                raise OrderNotFoundError(
                    f"Order {order_id} not found or already closed."
                )
        except OperationalError as e:
            app.logger.warning(f"Error locking order {order_id} for update.")
            raise e


def _add_order_details(order_id: int, upcs: set[int], session: Session) -> None:
    """Add order details."""
    insert_detail_data = [
        {
            "encoding_order_id": order_id,
            "upc": upc,
        }
        for upc in upcs
    ]
    session.execute(encoding_order_detail_table.insert(), insert_detail_data)

    select_stmt = (
        select(
            customer_master_table.c.customer_id.label("dms_customer_id"),
            encoding_order_detail_table.c.encoding_order_detail_id,
        )
        .join(
            encoding_order_table,
            func.find_in_set(
                customer_master_table.c.customer_master_master_id,
                encoding_order_table.c.dms_list,
            ),
        )
        .join(
            encoding_order_detail_table,
            encoding_order_detail_table.c.encoding_order_id
            == encoding_order_table.c.encoding_order_id,
        )
        .where(encoding_order_table.c.encoding_order_id == order_id)
        .group_by(
            encoding_order_detail_table.c.upc,
            customer_master_table.c.customer_master_master_id,
        )
    )

    session.execute(
        insert(encoding_order_detail_dms_table)
        .from_select(["dms_customer_id", "encoding_order_detail_id"], select_stmt)
        .prefix_with("IGNORE")
    )