"""Find missing physical/digital delivery delays."""

from src.connection import snowflake as connection
from src.sql.encoding_order_delay import SQL_RESULT_MISSING_ENCODING_ORDER, SQL_SP_ENCODING_ORDER_CHECK


def run(query_type, start_date, end_date):
    """Find missing physical/digital delivery delays."""
    records = get_records(query_type, start_date, end_date)
    if records and records.rowcount:
        return False, format_message(records)
    return True, None


def get_records(query_type, start_date, end_date):
    """Get records of missing encoding orders."""
    results = connection.query(SQL_SP_ENCODING_ORDER_CHECK.format(start_date, end_date, query_type))
    single_row = results.fetchone()
    if not single_row or not single_row["SP_EOCHECK"]:
        return None
    return connection.query(SQL_RESULT_MISSING_ENCODING_ORDER.format(single_row["SP_EOCHECK"]))


def format_message(records):
    """Generate error message from records."""
    message = []
    for row in records:
        message.append(
            f"RELEASE_ID: {row['RELEASE_ID']}, UPC: {row['UPC']}, REASON: {row['REASON']}, DMS_LIST: {row['DMS_LIST']}"
        )
    return "\n".join(message)