"""
SQL to check encoding order discrepancies.

Identifies closed encoding orders where UPCs exist in the
art_relations.encoding_order_detail table but are missing
from the direct_delivery.encoding_queue_detail table.
"""

SQL_ENCODING_ORDER = """
SELECT CONCAT(u.f_name, ' ',u.l_name) as created_by,
    LISTAGG(DISTINCT encoding_order_detail.upc, ',') as upc_list,
    xy.*
FROM (
    SELECT encoding_order.encoding_order_id,
        COUNT(DISTINCT encoding_order_detail.upc) AS eo_upcs_count,
        COUNT(DISTINCT encoding_queue_detail.upc) AS eq_upcs_count
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.encoding_order
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.encoding_order_detail
        ON encoding_order_detail.encoding_order_id =
            encoding_order.encoding_order_id
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.encoding_order_detail_dms
        ON encoding_order_detail_dms.encoding_order_detail_id =
            encoding_order_detail.encoding_order_detail_id
    INNER JOIN orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue
        ON encoding_queue.encoding_order_id =
            encoding_order.encoding_order_id
    LEFT JOIN orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue_detail
        ON encoding_queue_detail.encoding_queue_id =
            encoding_queue.encoding_queue_id
        AND encoding_queue_detail.upc = encoding_order_detail.upc
    WHERE encoding_order.order_status = 'close'
        AND encoding_order.processed = 'Y'
        AND encoding_order.entry_date BETWEEN '{}' AND '{}'
    GROUP BY encoding_order.encoding_order_id
) AS xy
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.encoding_order
    ON encoding_order.encoding_order_id =
        xy.encoding_order_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.encoding_order_detail
        ON encoding_order_detail.encoding_order_id =
            encoding_order.encoding_order_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users u
    ON u.id = encoding_order.orchadmin_user_id
INNER JOIN orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue
    ON encoding_queue.encoding_order_id = encoding_order.encoding_order_id
LEFT JOIN orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue_detail
    ON encoding_queue_detail.encoding_queue_id =
            encoding_queue.encoding_queue_id
        AND encoding_queue_detail.upc = encoding_order_detail.upc
WHERE xy.eo_upcs_count <> xy.eq_upcs_count
        AND encoding_queue_detail.upc is NULL
GROUP BY xy.encoding_order_id, xy.eo_upcs_count,
xy.eq_upcs_count, u.f_name, u.l_name
ORDER BY 2 """