import os
import pickle
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, Table, MetaData, inspect
from sqlalchemy.dialects.mysql import LONGTEXT

DB_USER = "laravel_user"
DB_PWD = "P4ssw0rd.96!"
DB_HOST = "127.0.0.1"
DB_DB = "laravel_db"
OUT_PATH = "estratti_serializzati"

def safe_columns(df):
    # Rinomina colonna "id" o "ID" (case-insensitive) in "id_csv"
    df.columns = [col if col.lower() != "id" else "id_csv" for col in df.columns]
    return df

engine = create_engine(f"mysql+pymysql://{DB_USER}:{DB_PWD}@{DB_HOST}/{DB_DB}?charset=utf8mb4")
metadata = MetaData()

def ensure_table(table_name, columns):
    insp = inspect(engine)
    if insp.has_table(table_name):
        print(f"[INFO] Tabella esistente: {table_name}")
        return
    cols = [Column('id', Integer, primary_key=True, autoincrement=True)]
    for col in columns:
        if col == "id":
            continue
        cols.append(Column(col, LONGTEXT))
    table = Table(
        table_name, metadata, *cols,
        mysql_engine='InnoDB',
        mysql_row_format='DYNAMIC'
    )
    table.create(engine)
    print(f"[OK] Tabella creata: {table_name}")

def import_table(tablename, df):
    df = safe_columns(df)
    ensure_table(tablename, df.columns)
    df.to_sql(tablename, engine, if_exists='append', index=False, method='multi', chunksize=1000)
    print(f"[OK] Importati {len(df)} record in {tablename}")

def main():
    for fname in os.listdir(OUT_PATH):
        if fname.endswith(".pkl") and fname != "all_tables.pkl":
            tablename = fname.replace(".pkl", "")
            df = pd.read_pickle(os.path.join(OUT_PATH, fname))
            import_table(tablename, df)

if __name__ == "__main__":
    main()