"""SQL constants."""

from vectororder.constants import fields

DD_SELECT_DELIVERY_SPEC = """
SELECT
    dds.dms_delivery_spec_id,
    dds.dms_master_master_id,
    dds.order_type,
    dds.delivery,
    dds.encoding,
    dds.metadata_update
FROM dms_delivery_spec dds
WHERE
    dds.dms_master_master_id = :dms_master_master_id
    AND dds.order_type = :order_type
"""

# select list of EQD which have duplicates -
# rows with the same upc, store ID and bigger EQD_ID (newer)
DD_SELECT_EQD_DUPLICATES = """
    SELECT DISTINCT eqd_check.encoding_queue_detail_id
    FROM encoding_queue_detail eqd_check
    INNER JOIN encoding_queue eq_check
        ON eq_check.encoding_queue_id = eqd_check.encoding_queue_id
    INNER JOIN encoding_queue_detail eqd_newer
        ON eqd_newer.upc = eqd_check.upc
        AND eqd_newer.dms_master_master_id = eqd_check.dms_master_master_id
        AND eqd_newer.cd <> eqd_check.cd
        AND eqd_newer.track_id <> eqd_check.track_id
        AND eqd_newer.clip_number <> eqd_check.clip_number
        AND eqd_newer.encoding_queue_detail_id > eqd_check.encoding_queue_detail_id
        AND eqd_newer.status NOT IN ("cancelled", "system_cancelled")
    INNER JOIN encoding_queue eq_newer
        ON eq_newer.encoding_queue_id = eqd_newer.encoding_queue_id
        AND eq_newer.encoding_order_type = eq_check.encoding_order_type
        AND eq_newer.meta_update = "N"
    WHERE eqd_check.encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# select EQD statuses
DD_SELECT_JOB_STATUSES = """
     SELECT
        eqd.encoding_queue_detail_id,
        eqd.upc,
        eqd.dms_master_master_id,
        eqd.status,
        eqd.encoding_started,
        eqd.delivery_started,
        eq.meta_update,
        eq.encoding_order_type
     FROM encoding_queue_detail eqd
     INNER JOIN encoding_queue eq
        ON eqd.encoding_queue_id = eq.encoding_queue_id
     WHERE eqd.encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# update EQD to re-encode
DD_UPDATE_JOB_REENCODE = """
    UPDATE encoding_queue_detail
    SET status = "new", error_log = "", updated_oa = "N"
    WHERE encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# update EQD to re-deliver
DD_UPDATE_JOB_REDELIVER = """
    UPDATE encoding_queue_detail eqd
    LEFT JOIN direct_delivery_location ddl
        ON eqd.encoding_queue_detail_id = ddl.encoding_queue_detail_id
    SET
        eqd.status = IF(
            ddl.encoding_queue_detail_id IS NULL
            OR
            DATEDIFF(CURRENT_TIMESTAMP(), eqd.encoding_ended) > 30,
            "new",
            "encoded"
        ),
        eqd.error_log = "",
        eqd.updated_oa = "N"
    WHERE eqd.encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# set status = cancelled for EQD
DD_UPDATE_JOB_CANCEL = """
    UPDATE encoding_queue_detail
    SET status = "cancelled"
    WHERE encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# delete locations for EQD list
DD_DELETE_LOCATION = """
    DELETE FROM direct_delivery_location
    WHERE encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

DD_DELETE_LOCATION_IF_NEW = """
    DELETE ddl
    FROM direct_delivery_location ddl
    JOIN encoding_queue_detail eqd
        ON ddl.encoding_queue_detail_id = eqd.encoding_queue_detail_id
        AND eqd.status = "new"
    WHERE ddl.encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# select EQD key fields
DD_SELECT_JOB_AR_KEY = """
    SELECT
        eqd.encoding_queue_detail_id,
        eq.encoding_order_id,
        eqd.upc,
        eqd.dms_master_master_id
    FROM encoding_queue eq
    JOIN encoding_queue_detail eqd
        ON eq.encoding_queue_id = eqd.encoding_queue_id
    WHERE eqd.encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

# delete AR EODD records filter template
AR_DELETE_EODD_WHERE = """
    (eod.encoding_order_id = {} AND eod.upc = {}
    AND cm.customer_master_master_id = {})
"""

# delete AR EOD filter template
AR_DELETE_EOD_WHERE = """
    (eod.encoding_order_id = {} AND eod.upc = {})
"""

# delete delivery_batch_detail records by job ID
DD_DELETE_DELIVERY_BATCH_DETAIL = """
    DELETE FROM delivery_batch_detail
    WHERE encoding_queue_detail_id IN :{}
""".format(fields.SQL_ID_LIST)

DD_GET_HAS_DELIVERED_JOB = """
SELECT EXISTS(
    SELECT 1
    FROM encoding_queue_detail eqd
    WHERE eqd.upc = :upc
        AND eqd.dms_master_master_id = :dms_master_master_id
        AND eqd.status = 'delivered'
) AS has_delivered_job;
"""

AR_GET_HAS_DELIVERY_HISTORY = """
SELECT EXISTS(
    SELECT 1
    FROM delivery_history dh
    WHERE dh.upc = :upc
        AND dh.customer_master_master_id = :customer_master_master_id
) AS has_delivery_history;
"""

AR_GET_PRODUCT = """
SELECT
    r.release_id,
    r.upc,
    r.distribution_format_id,
    r.not_for_distribution,
    r.release_status,
    df.context_type
FROM releases r
LEFT JOIN distribution_format df
    ON df.distribution_format_id = r.distribution_format_id
WHERE r.upc = :upc;
"""

AR_GET_TRACKS = """
SELECT
    t.id,
    t.track_type,
    t.offer_type
FROM track t
WHERE t.release_id = :release_id;
"""

AR_GET_STORE = """
SELECT
    cmm.hd_only
FROM customer_master_master cmm
WHERE customer_master_master_id = :customer_master_master_id;
"""

AR_GET_STORE_CLASSIFICATION = """
SELECT cd.classification
FROM store_classification_detail scd
INNER JOIN classification_detail cd ON scd.classification_detail_id = cd.id
WHERE scd.store_id = :store_id;
"""

AR_GET_VENDOR = """
SELECT
    v.vendor_id,
    v.name,
    v.label_identifier,
    v.api_vendor_id
FROM releases r
JOIN project pj
    ON r.project_id = pj.project_id
JOIN vendor v
    ON pj.vendor_id = v.vendor_id
WHERE r.upc = :upc;
"""

DD_GET_ENCODING_PROFILE = """
SELECT dep.dms_master_master_id, ep.format
FROM encoding_profile ep
INNER JOIN  dms_encoding_profile dep ON ep.encoding_profile_id = dep.encoding_profile_id
WHERE dep.dms_master_master_id = :dms_id
AND ep.format IN :encoding_formats
GROUP BY ep.format;
"""