# Import python packages
import uuid

import streamlit as st
from snowflake.snowpark.context import get_active_session

from utils import get_env, get_jwt_token, delete_product_by_upc

session = get_active_session()

if 'env' not in st.session_state:
    st.session_state.env = get_env()

if 'jwt' not in st.session_state:
    st.session_state.jwt = get_jwt_token()

releases_query = """
    SELECT distinct 
        r.UPC,
        r.RELEASE_ID as PRODUCT_ID,
        RELEASE_NAME,
        v.name as LABEL_NAME,
        r.NOT_FOR_DISTRIBUTION,
        r.DISTRIBUTION
    FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES r
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.project p ON r.project_id = p.project_id
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor v ON v.vendor_id = p.vendor_id
"""

backup_query = """
insert into delete_art_relations_{table}_backup
select * , '{deletion_request_id}' from ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.{table}
where release_id = {release_id}
"""

backup_tables = ['track',
                 'release_artist',
                 'release_status',
                 'release_subgenre',
                 'mkt_priority',
                 'release_logging',
                 'release_manual_adjustment',
                 'release_grid',
                 'release_subtitles',
                 'release_approval_queue',
                 'releases']

user = st.user.user_name

if 'run_id' not in st.session_state:
    st.session_state.run_id = uuid.uuid4()

if 'stage' not in st.session_state:
    st.session_state.stage = 0

st.write(f"""
Hello {user}, \n
The purpose of this application is to delete a product and all product related data from art_relations database.\n
This process will only delete SMEAnalyticsDummy products.\n
Note: Deletion is PERMANENT and cannot be undone. Please ensure you are deleting the correct Product.
""")


def log_info(text, upc):
    log_query = f"""
    insert into DELETE_SME_PRODUCT_LOG(run_id, user_name, log_message, upc)
    values('{st.session_state.run_id}','{user}', '{text}' , '{upc}')
    """
    session.sql(log_query).collect()


def get_external_function_name():
    return f'{st.session_state.env.lower()}-lambda-content-delete-product'


def set_state(i):
    if i == 0:
        st.session_state.stage = 1
    if st.session_state.stage < i:
        st.session_state.stage = i


def click_delete_product_button():
    set_state(4)


with st.form("input_data"):
    st.write("Choose UPC or Product_id to be deleted")
    upc = st.text_input("Enter upc to delete")
    st.write("OR")
    product_id = st.text_input("Enter product_id to delete")
    submit_form = st.form_submit_button(
        'Check if product is SMEAnalyticsDummy',
        on_click=set_state, args=[0])

if upc != '':
    try:
        int(upc)
    except ValueError:
        st.session_state.stage = 0
        st.write("UPC should be a numeric")

if st.session_state.stage >= 1:
    if upc != '':
        st.write(" UPC ", upc)
        releases_query = releases_query + ' where r.upc = ? '
        query_param = upc
        set_state(2)
    elif product_id != '':
        st.write(" Product_Id ", product_id)
        releases_query = releases_query + ' where r.release_id = ?'
        query_param = product_id
        set_state(2)
    else:
        st.write("Please set UPC or Product_Id")

if st.session_state.stage >= 2:
    df = session.sql(releases_query, params=[query_param]).to_pandas()
    st.write(df)
    nfd = df['NOT_FOR_DISTRIBUTION'].get(0)
    st.write('NOT_FOR_DISTRIBUTION column :', nfd)

    products_upc = df['UPC'].get(0)
    product_product_id = df['PRODUCT_ID'].get(0)

    if nfd == 'SMEAnalyticsDummy':
        st.write('Click below to confirm delete')
        set_state(3)
    else:
        st.write(
            'Products is NOT from SMEAnalyticsDummy we can not delete it!!!!')

if st.session_state.stage >= 3:
    st.button(f"PERMANENTLY DELETE upc : {products_upc}",
              on_click=click_delete_product_button, type="primary")

if st.session_state.stage >= 4:
    st.write('Deleteing product : ')
    deletion_request_id = uuid.uuid4()

    log_info(
        f'Deleteing product . product_id: {product_product_id} , upc : {products_upc}, request_id {deletion_request_id}', upc)

    log_info('Start art_relations tables backup', upc)
    for table in backup_tables:
        session.sql(backup_query.format(table=table,
                                        deletion_request_id=deletion_request_id,
                                        release_id=product_product_id)) \
            .to_pandas()

    result = delete_product_by_upc(products_upc)

    log_info(
        f"deletion request_id : {deletion_request_id}. result : {result.text}", upc)

    st.write('result : ', result.text)