import os
import pandas as pd
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# 1. Load the variables from .env
load_dotenv()

# # 2. Get the URI from the environment variable
# uri = os.getenv("MYSQL_URI")

# # 3. Create the engine
# engine = create_engine(uri)

# try:
#     with engine.connect() as conn:
#         # In SQLAlchemy 2.0, you must wrap strings in text()
#         result = conn.execute(text("SELECT 1"))
#         print("✅ Connected successfully to the database!")
# except Exception as e:
#     print(f"❌ Connection failed: {e}")


DB_URI = os.getenv("MYSQL_URI")

engine = create_engine(DB_URI, pool_pre_ping=True)

# def insert_dataframe_ignore(df, table_name):
#     """
#     Insert DataFrame into MySQL using INSERT IGNORE
#     """
#     with engine.begin() as conn:
#         for _, row in df.iterrows():
#             columns = ", ".join(row.index)
#             placeholders = ", ".join([f":{col}" for col in row.index])

#             query = text(f"""
#                 INSERT IGNORE INTO {table_name} ({columns})
#                 VALUES ({placeholders})
#             """)

#             conn.execute(query, row.to_dict())


def insert_dataframe_ignore(df, table_name):
    with engine.begin() as conn:
        for i, (_, row) in enumerate(df.iterrows()):
            try:
                columns = ", ".join(row.index)
                placeholders = ", ".join([f":{col}" for col in row.index])

                query = text(f"""
                    INSERT IGNORE INTO {table_name} ({columns})
                    VALUES ({placeholders})
                """)

                clean_row = {
                    k: (None if pd.isna(v) else v)
                    for k, v in row.to_dict().items()
                }

                conn.execute(query, clean_row)

                # conn.execute(query, row.to_dict())

            except Exception as e:
                print(f"❌ Error on row {i}")
                print(row.to_dict())
                raise e