import os
import sys
from typing import List
from snowflake.snowpark.session import Session
from snowflake.snowpark.types import StringType, StructType, StructField
import geoip2.database
import os
import sys
import pandas as pd
from typing import List
import geoip2.database
from snowflake.snowpark.session import Session


# Procedure to create transient table from IP list
def create_geo_info_table(session: Session, table_name: str, ips: List[str]) -> str:
    # Path to GeoIP2 database
    path = os.path.join(sys._xoptions["snowflake_import_directory"], "GeoIP2-City.mmdb")
    geo_info_data = []
    # Process each IP to fetch country and city information
    for ip in ips:
        try:
            with geoip2.database.Reader(path) as reader:
                response = reader.city(ip)
                country = response.country.name
                city = response.city.name
        except Exception:
            country = None
            city = None
        geo_info_data.append([ip, country, city])

     # Convert results to a Pandas DataFrame
    result_df = pd.DataFrame(geo_info_data, columns=["ip", "country", "city"])

    schema = StructType([
        StructField("ip", StringType(), True),        # Column: IP (String)
        StructField("country", StringType(), True),  # Column: Country (String)
        StructField("city", StringType(), True)      # Column: City (String)
    ])

    # Save the DataFrame to the temporary table in Snowflake
    session.create_dataframe(result_df, schema=schema).write.save_as_table(
        table_name, mode="overwrite", table_type="transient", column_order="name"
    )

    return "Transient table " + table_name + "created and populated successfully."