from src import connection


def insert_backup_records(partner_id, upc_list, cursor):
    upcs_string_placeholder = ",".join(["%s"] * len(upc_list))
    sql_insert = """INSERT INTO delivery_history_deleted (delivery_id, encoder_id, upc, customer_master_master_id, date_delivered, package_size, inserted_datetime)
                     SELECT delivery_id, encoder_id, upc, customer_master_master_id, date_delivered, package_size, NOW()
                     FROM delivery_history
                     WHERE customer_master_master_id = %s
                     AND upc IN (%s)""" % (
        "%s",
        upcs_string_placeholder,
    )  # replace all UPC entries with %s placeholders
    insert_bindings = (partner_id,) + tuple(upc_list)  # create one tuple with all params
    cursor.execute(sql_insert, insert_bindings)


def delete_records(partner_id, upc_list, cursor):
    upcs_string_placeholder = ",".join(["%s"] * len(upc_list))
    sql_delete = """DELETE FROM delivery_history
                WHERE customer_master_master_id = %s
                AND upc IN (%s)""" % ("%s", upcs_string_placeholder)  # replace all UPC entries with %s placeholders
    delete_bindings = (partner_id,) + tuple(upc_list)  # create one tuple with all params
    cursor.execute(sql_delete, delete_bindings)


def clear_delivery_history_upcs(partner_id, upc_list):
    db_connection = connection.ar_db_connection()
    cursor = db_connection.cursor()
    try:
        insert_backup_records(partner_id, upc_list, cursor)
        delete_records(partner_id, upc_list, cursor)
        db_connection.commit()
    finally:
        try:
            cursor.close()
        except Exception:
            pass
        db_connection.close()