import pandas as pd

from streamlit_app.common.local_connection import get_connection_parameters
from snowflake.snowpark import Session

from etl.main_metrics import create_sales_metrics
from etl.basket_creation import basket_input_creation
from etl.basket_analysis import basket_analysis
from etl.fan_insights import create_fan_insights


import sys
import path

# directory reach
directory = path.Path(__file__).abspath()
# setting path
sys.path.append(directory.parent.parent)


connection_params = get_connection_parameters("sme_merch")
session = Session.builder.configs(connection_params).create()


store_ids = ["S00030", "S00088", "S00113", "S00115", "S00121", "S00122", "S00123"]

artists = ["LISA", "SZA", "Rex Orange County"]

stores = "CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_STORES"
orders = "CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_ORDERS"
products = "CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_PRODUCTS"
fans = "DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.FAN_C"

# Define basket analysis tables
input_ref = "BASKET_ANALYSIS_INPUT"
output_ref = "BASKET_ANALYSIS_OUTPUT"
output_basket_metrics = "merch_basket_metrics"
output_basket_name_metrics = "merch_name_basket_metrics"
output_basket_gender_metrics = "merch_basket_metrics_gender"
output_basket_age_metrics = "merch_basket_metrics_age"
output_ref_assoc = "BASKET_ANALYSIS_OUTPUT_ASSOC"
output_ref_assoc_item = "BASKET_ANALYSIS_OUTPUT_ASSOC_ITEM"



tables = [input_ref, output_basket_metrics, output_basket_name_metrics, output_basket_gender_metrics, output_basket_age_metrics]
output_tables = [output_ref, output_ref_assoc, output_ref_assoc_item]

create_sales_metrics(session, store_ids)

session = Session.builder.configs(connection_params).create()
basket_input_creation(session, store_ids, tables, stores, orders, products, fans)

session = Session.builder.configs(connection_params).create()
basket_analysis(session, input_ref, output_tables, artists)

session = Session.builder.configs(connection_params).create()
create_fan_insights(session)