"""Delivery history model for AR delivery tables."""

from sqlalchemy import text

from deliveryhistory.connectors import mysql


GET_DELIVERED_STORES_COUNT = """
SELECT
  r.release_id as product_id,
  r.display_upc as display_upc,
  COUNT(DISTINCT(c.customer_master_master_id)) AS stores_count
FROM delivery_history dh
  INNER JOIN releases r ON dh.upc = r.upc
  INNER JOIN customer_master_master c ON
    dh.customer_master_master_id = c.customer_master_master_id
  WHERE r.release_status = 'in_content'
  AND dh.upc IN ({upcs})
  GROUP BY r.upc
"""

GET_DELIVERED_STORES_DETAILS = """
SELECT
  dh.date_delivered,
  c.customer_master_master_id as store_id
FROM delivery_history dh
  INNER JOIN customer_master_master c ON
    dh.customer_master_master_id = c.customer_master_master_id
WHERE dh.upc = :upc
"""

GET_DELIVERED_STORES_DETAILS_BY_STORE = GET_DELIVERED_STORES_DETAILS + """
  AND c.customer_master_master_id = :store_id
"""

GET_DELIVERY_JOBS = """
SELECT
  eqd.encoding_queue_detail_id AS job_id,
  eqd.dms_master_master_id AS store_id,
  eq.meta_update AS meta_update,
  eqd.status AS delivery_status
FROM encoding_queue_detail eqd
  INNER JOIN encoding_queue eq ON eqd.encoding_queue_id = eq.encoding_queue_id
WHERE eqd.upc = :upc
ORDER BY ISNULL(eqd.delivery_ended) ASC, eqd.delivery_ended ASC, eqd.encoding_queue_detail_id ASC
"""

GET_DELIVERY_JOBS_BY_STORE = """
SELECT
  eqd.encoding_queue_detail_id AS job_id,
  eqd.dms_master_master_id AS store_id,
  eq.meta_update AS meta_update,
  eqd.status AS delivery_status
FROM encoding_queue_detail eqd
  INNER JOIN encoding_queue eq ON eqd.encoding_queue_id = eq.encoding_queue_id
WHERE eqd.upc = :upc
  AND eqd.dms_master_master_id = :store_id
ORDER BY ISNULL(eqd.delivery_ended) ASC, eqd.delivery_ended ASC, eqd.encoding_queue_detail_id ASC
"""


def get_delivered_stores_count(upcs):
    """Get delivery report with count of stores for a list of upcs.

    Args:
        upcs (list): a list of upcs to get the delivery history for

    Returns:
        list: list of rows from DB

    """
    with mysql.ar_session_scope() as db_session:
        upcs_list = ','.join(upcs)
        result = db_session.execute(
            text(GET_DELIVERED_STORES_COUNT.format(upcs=upcs_list))).mappings().fetchall()
    return result


def get_delivered_stores_details(upc, store_id=None):
    """Get delivery report for a given upc, optionally filtered by store.

    Args:
        upc (str): a upc to get the delivery history for
        store_id (str, optional): a store id to filter the results by

    Returns:
        list: list of rows from DB

    """
    if store_id is not None:
        query = text(GET_DELIVERED_STORES_DETAILS_BY_STORE)
        params = {'upc': upc, 'store_id': store_id}
    else:
        query = text(GET_DELIVERED_STORES_DETAILS)
        params = {'upc': upc}

    with mysql.ar_session_scope() as db_session:
        result = db_session.execute(query, params).mappings().fetchall()
    return result


def get_delivery_jobs(upc, store_id=None):
    """Get delivery jobs for a given upc, optionally filtered by store.

    Returns the encoding_queue_detail job id (used to locate the ERN delivery
    file in S3) for each delivered store.

    Args:
        upc (str): a upc to get the delivery jobs for
        store_id (str, optional): a store id to filter the results by

    Returns:
        list: list of rows from DB, each holding job_id, store_id and meta_update

    """
    if store_id is not None:
        query = text(GET_DELIVERY_JOBS_BY_STORE)
        params = {'upc': upc, 'store_id': store_id}
    else:
        query = text(GET_DELIVERY_JOBS)
        params = {'upc': upc}

    with mysql.dd_session_scope() as db_session:
        result = db_session.execute(query, params).mappings().fetchall()
    return result