from streamlit_app.common.local_connection import get_connection_parameters
import sys
import path
from snowflake.snowpark import Session
from typing import List

from snowflake.snowpark.functions import (
    col,
    count,
    lit,
    to_date,
)


def basket_input_creation(
    sn_session: Session,
    store_ids: List[str],
    tables: List[str],
    stores_ref: str,
    orders_ref: str,
    products_ref: str,
    fans_ref: str
) -> bool:
    """
    Function to prepare data for Apriori algorithm
    :param sn_session: Snowpark session object
    :param store_ids: List of store ids used for data input
    :param tables: List of tables used for outputs
    :param stores_ref: Snowflake table containing stores data
    :param orders_ref: Snowflake table containing orders data
    :param products_ref: Snowflake table containing products data
    :param fans_ref: Snowflake table containing crm fans data
    :return: Boolean if process was successful or not
    """

    stores = (
        sn_session.table(stores_ref)
        .select("artist_id", "artist", "store_id", "territory")
        .filter((col("store_id").isin(store_ids)))
    )

    orders = (
        sn_session.table(orders_ref)
        .select(
            "store_id",
            "store_customer_id",
            "product_id",
            "order_id",
            "order_items_quantity",
            "customer_id",
            to_date(col("order_date_created")).alias("order_date"),
        )
        .filter(
            (col("order_total") > 0)
            & (col("delete_requested") == False)  # noqa: E712
            & (col("order_status") == "paid")
        )
    )

    products = sn_session.table(products_ref).select(
        "product_id", "product_name", col("type").alias("product_type")
    )

    fans = (
        sn_session.table(fans_ref)
        .select("age_range_c", "gender_c", "customer_id_c")
    )

    join = stores.join(orders, ["STORE_ID"])
    join1 = join.join(products, ["PRODUCT_ID"]).with_column("PURCHASE", lit(1))
    join2 = join1.join(fans,  join1.CUSTOMER_ID== fans.CUSTOMER_ID_C, how="left")

    product_analytics = join2.groupBy("artist", "territory", "product_type", "order_date").agg(
        count("product_id").alias("total_products")
    )

    product_name_analytics = join2.groupBy("artist", "territory", "product_name", "order_date").agg(
        count("product_id").alias("total_products")
    )

    product_gender_analytics = join2.groupBy("artist", "territory", "gender_c", "product_name", "order_date").agg(
        count("product_id").alias("total_products")
    )

    product_age_range_analytics = join2.groupBy("artist", "territory", "age_range_c", "product_name", "order_date").agg(
        count("product_id").alias("total_products")
    )

    join2.write.mode("overwrite").save_as_table(tables[0])
    product_analytics.write.mode("overwrite").save_as_table(tables[1])
    product_name_analytics.write.mode("overwrite").save_as_table(tables[2])
    product_gender_analytics.write.mode("overwrite").save_as_table(tables[3])
    product_age_range_analytics.write.mode("overwrite").save_as_table(tables[4])
    sn_session.close()

    return True


# 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()
#
# session.sproc.register(
#     basket_input_creation,
#     name="basket_input_creation",
#     stage_location="@STORED_PROCEDURES",
#     is_permanent=True,
#     execute_as="caller",
#     packages=[
#         "snowflake-snowpark-python==1.28.0",
#     ],
#     replace=True,
# )
#
# session.close()