"""SQL queries for art_relations."""

# get encoding orders with details
OBTAIN_ENCODING_ORDERS = """
    SELECT
        eo.encoding_order_id,
        eo.priority,
        eo.encoder_id,
        eo.meta_update,
        eo.entry_date,
        eo.orchadmin_user_id,
        eod.upc,
        cm.customer_master_master_id
    FROM (
        SELECT
            eo_limit.encoding_order_id,
            eo_limit.priority,
            eo_limit.encoder_id,
            eo_limit.meta_update,
            eo_limit.entry_date,
            eo_limit.orchadmin_user_id
        FROM encoding_order eo_limit
        WHERE encoder_id IN :in_values
            AND order_status = "close"
            AND processed <> "Y"
            AND `type` = "track"
            AND EXISTS (
                SELECT 1
                FROM encoding_order_detail eod_ex
                INNER JOIN encoding_order_detail_dms eodd_ex
                    ON eodd_ex.encoding_order_detail_id =
                       eod_ex.encoding_order_detail_id
                INNER JOIN customer_master cm_ex
                    ON cm_ex.customer_id = eodd_ex.dms_customer_id
                WHERE eod_ex.encoding_order_id = eo_limit.encoding_order_id
            )
        ORDER BY eo_limit.encoding_order_id
        LIMIT :limit) AS eo
    INNER JOIN encoding_order_detail eod
        ON eo.encoding_order_id = eod.encoding_order_id
    INNER JOIN encoding_order_detail_dms eodd
        ON eodd.encoding_order_detail_id = eod.encoding_order_detail_id
    INNER JOIN customer_master cm
        ON cm.customer_id = eodd.dms_customer_id
    ORDER BY eo.encoding_order_id;
    """

# update encoding order status
UPDATE_ENCODING_ORDER_STATUS = """
    UPDATE encoding_order
    SET processed = :status
    WHERE encoding_order_id = :order_id
    """