"""SQL queries for Renew Manager."""

# Step 1: per-store capacity.
# Returns one row per store with jobs_to_queue = LEAST(MAX_JOBS_PER_RUN - in_flight, count_new).
# HAVING drops stores with no capacity. STRAIGHT_JOIN forces the index-friendly
# join order (encoding_queue_detail → encoding_queue → dms_delivery_spec).
SQL_GET_STORE_CAPS = """
WITH counts AS (
    SELECT
        eqd.dms_master_master_id,
        COUNT(IF(eqd.status = 'new', 1, NULL)) AS count_new,
        COUNT(IF(eqd.status != 'new', 1, NULL)) AS count_in_flight
    FROM encoding_queue_detail eqd
    STRAIGHT_JOIN encoding_queue eq
        ON eq.encoding_queue_id = eqd.encoding_queue_id
    STRAIGHT_JOIN dms_delivery_spec dds
        ON dds.dms_master_master_id = eqd.dms_master_master_id
        AND dds.order_type = eq.encoding_order_type
        AND dds.delivery = 'Y'
        AND dds.encoding = 'Y'
    WHERE eqd.status IN (
        'new',
        'ready_to_encode',
        'encoding',
        'encoded',
        'ready_to_deliver',
        'queued_for_delivery',
        'delivering'
    )
      AND (eq.encoder_id, eq.encoding_order_type) IN (
          (17, 'release'),
          (18, 'release'),
          (18, 'ringtone'),
          (19, 'harddrive'),
          (23, 'release')
      )
    GROUP BY 1
)
SELECT
    counts.dms_master_master_id,
    LEAST(%s - counts.count_in_flight, counts.count_new) AS jobs_to_queue
FROM counts
HAVING jobs_to_queue > 0
"""

# Step 2: top 'new' jobs per capped store. The inner subquery ranks each
# store's 'new' jobs by (jp.priority, id) carrying only key columns, so the
# window sort stays narrow; the outer query joins the payload back for rows
# within each store's cap (r <= CASE ... END from step 1). FORCE INDEX
# (status_dms) pins the (status, dms) access path; the INNER JOIN on jp drops
# jobs with no job_priority entry.
SQL_GET_RANKED_JOBS = """
SELECT
    eqd.encoding_queue_detail_id,
    eqd.dms_master_master_id,
    eqd.upc,
    eqd.cd,
    eqd.track_id,
    eqd.clip_number,
    eq.encoder_id,
    eq.encoding_order_type,
    eq.meta_update,
    jp.priority AS encoding_order_priority,
    cmm.priority AS store_priority
FROM (
    SELECT
        eqd.encoding_queue_detail_id,
        eqd.dms_master_master_id,
        ROW_NUMBER() OVER (
            PARTITION BY eqd.dms_master_master_id
            ORDER BY jp.priority ASC, eqd.encoding_queue_detail_id ASC
        ) AS r
    FROM encoding_queue_detail eqd FORCE INDEX (status_dms)
    JOIN encoding_queue eq
        ON eq.encoding_queue_id = eqd.encoding_queue_id
    JOIN job_priority jp
        ON jp.encoding_queue_detail_id = eqd.encoding_queue_detail_id
    JOIN dms_delivery_spec dds
        ON dds.dms_master_master_id = eqd.dms_master_master_id
        AND dds.order_type = eq.encoding_order_type
        AND dds.delivery = 'Y'
        AND dds.encoding = 'Y'
    WHERE eqd.status = 'new'
      AND eqd.dms_master_master_id IN %s
      AND (eq.encoder_id, eq.encoding_order_type) IN (
          (17, 'release'),
          (18, 'release'),
          (18, 'ringtone'),
          (19, 'harddrive'),
          (23, 'release')
      )
) AS ranked
JOIN encoding_queue_detail eqd
    ON eqd.encoding_queue_detail_id = ranked.encoding_queue_detail_id
JOIN encoding_queue eq
    ON eq.encoding_queue_id = eqd.encoding_queue_id
JOIN job_priority jp
    ON jp.encoding_queue_detail_id = eqd.encoding_queue_detail_id
JOIN customer_master_master cmm
    ON cmm.customer_master_master_id = eqd.dms_master_master_id
WHERE ranked.r <= CASE ranked.dms_master_master_id
    {case_body}
END
ORDER BY store_priority ASC, ranked.r ASC
"""

SQL_SET_JOBS_STATUS = """UPDATE encoding_queue_detail
SET `status` = %s, `error_log` = NULL
WHERE encoding_queue_detail_id IN %s AND `status` = %s"""