"""Database operations."""

import os
import pathlib
import pymysql

import queries

ar_connection = pymysql.connect(
    host=os.environ['AR_DB_HOST'],
    user=os.environ['AR_DB_USERNAME'],
    password=os.environ['AR_DB_PASSWORD'],
    database='art_relations',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor,
    autocommit=True
)

dd_connection = pymysql.connect(
    host=os.environ['DD_DB_HOST'],
    user=os.environ['DD_USER'],
    password=os.environ['DD_PASS'],
    database='direct_delivery',
    cursorclass=pymysql.cursors.DictCursor,
    autocommit=True
)


def check_connection(connection: pymysql.connections.Connection) -> None:
    """Verify db read connection."""
    _select(dd_connection, 'SELECT 1')


def select_product(upc: int) -> dict:
    """Select product metadata."""
    results = _select(
        ar_connection,
        queries.AR_SELECT_PRODUCT_BY_UPC,
        {
            'upc': upc
        }
    )
    if not results:
        raise ValueError(f'UPC {upc} not found in art_relations. Check seed data or verify the UPC exists in QA.')
    return results[0]


def select_encoding_queue(encoding_queue_id: int) -> dict:
    """Select encoding queue metadata."""
    return _select(
        dd_connection,
        queries.DD_SELECT_ENCODING_QUEUE,
        {
            'encoding_queue_id': encoding_queue_id
        }
    )[0]


def insert_encoding_queue(
        encoder_id: int, meta_update: bool, priority: int) -> dict:
    """Insert to dd encoding_queue table."""
    encoding_queue_id = _insert(
        dd_connection,
        queries.DD_INSERT_ENCODING_QUEUE,
        {
            'priority': 1,
            'encoding_order_type': 'release',
            'encoder_id': encoder_id,
            'meta_update': 'Y' if meta_update else 'N'
        }
    )
    return select_encoding_queue(encoding_queue_id)


def select_encoding_queue_detail(encoding_queue_detail_id: int) -> dict:
    """Select encoding queue detail metadata."""
    return _select(
        dd_connection,
        queries.DD_SELECT_ENCODING_QUEUE_DETAIL,
        {
            'encoding_queue_detail_id': encoding_queue_detail_id
        }
    )[0]


def insert_encoding_queue_detail(
        encoding_queue_id: int,
        upc: int, status: str, dms_master_master_id: int) -> dict:
    """Insert to dd encoding_queue_detail table."""
    encoding_queue_detail_id = _insert(
        dd_connection,
        queries.DD_INSERT_ENCODING_QUEUE_DETAIL,
        {
            'encoding_queue_id': encoding_queue_id,
            'upc': upc,
            'status': status,
            'dms_master_master_id': dms_master_master_id
        }
    )
    _insert(
        dd_connection,
        queries.DD_INSERT_JOB_PRIORITY,
        {
            'encoding_queue_detail_id': encoding_queue_detail_id,
            'rule_matches': 3,
            'priority': 1
        }
    )
    return select_encoding_queue_detail(encoding_queue_detail_id)


def update_encoding_queue_detail_status(
        encoding_queue_detail_id: int,
        status: str) -> None:
    """Update status of encoding queue detail."""
    _update(
        dd_connection,
        queries.DD_UPDATE_ENCODING_QUEUE_DETAIL_STATUS,
        {
            'encoding_queue_detail_id': encoding_queue_detail_id,
            'status': status
        }
    )


def select_delivery_spec(dms_id: int) -> dict:
    """Select dms delivery spec for single store."""
    return _select(
        dd_connection,
        queries.DD_SELECT_DELIVERY_SPEC,
        {
            'dms_master_master_id': dms_id
        }
    )[0]


def insert_batch(encoding_queue_detail_id: int) -> int:
    """Create entries for batch."""
    encoding_queue_detail = select_encoding_queue_detail(
        encoding_queue_detail_id
    )
    batch_id = _insert(
        dd_connection,
        queries.DD_INSERT_DELIVERY_BATCH,
        {
            'dms_master_master_id': encoding_queue_detail['dms_master_master_id'],  # noqa:E501
            'priority': 1,
            'remote_folder': f'dev_{encoding_queue_detail_id}',
            'order_type': encoding_queue_detail['order_type']
        }
    )
    _insert(
        dd_connection,
        queries.DD_INSERT_DELIVERY_BATCH_DETAIL,
        {
            'delivery_batch_id': batch_id,
            'encoding_queue_detail_id': encoding_queue_detail_id
        }
    )
    return batch_id


def update_batch_status(batch_id: int, status: str) -> None:
    """Update batch status by id."""
    _update(
        dd_connection,
        queries.DD_UPDATE_BATCH_STATUS,
        {
            'delivery_batch_id': batch_id,
            'status': status
        }
    )


_SEEDS_DIR = pathlib.Path(__file__).parent.parent / 'data_seeds'


def _split_sql(sql: str) -> list[str]:
    """Split SQL on semicolons, ignoring those inside single or double quotes."""
    statements, current, in_quote = [], [], None
    for char in sql:
        if in_quote:
            current.append(char)
            if char == in_quote:
                in_quote = None
        elif char in ("'", '"'):
            in_quote = char
            current.append(char)
        elif char == ';':
            stmt = ''.join(current).strip()
            if stmt:
                statements.append(stmt)
            current = []
        else:
            current.append(char)
    if stmt := ''.join(current).strip():
        statements.append(stmt)
    return statements


def seed_local_stack() -> None:
    """Seed art_relations and direct_delivery DBs for local stack testing."""
    seeds = [
        (ar_connection, _SEEDS_DIR / 'art_relations' / 'seed.sql'),
        (dd_connection, _SEEDS_DIR / 'direct_delivery' / 'seed.sql'),
    ]
    for connection, path in seeds:
        with open(path, 'r') as f:
            sql = f.read()
        with connection.cursor() as cursor:
            for statement in _split_sql(sql):
                statement = statement.strip()
                if statement:
                    try:
                        cursor.execute(statement)
                    except Exception as e:
                        print(f'Seed error in {path}: {e}\nStatement: {statement[:200]}')
                        raise
        print(f'Seeded {path}')


def _insert(
        connection: pymysql.connections.Connection,
        sql_str: str,
        params: dict = {}) -> int:
    with connection.cursor() as cursor:
        cursor.execute(sql_str, params)
        return cursor.lastrowid


def _update(
        connection: pymysql.connections.Connection,
        sql_str: str,
        params: dict = {}) -> None:
    with connection.cursor() as cursor:
        cursor.execute(sql_str, params)


def _select(
        connection: pymysql.connections.Connection,
        sql_str: str,
        params: dict = {}) -> list[dict]:
    with connection.cursor() as cursor:
        cursor.execute(sql_str, params)
        return cursor.fetchall()