import pandas as pd
import mysql.connector
from flask import request, redirect, url_for

# Load Excel
df = pd.read_excel(
    r"C:\Projects Dashboard\config\LODOSO.xlsx",
    header=None
)

# Fix headers
df.columns = df.iloc[0]
df = df.iloc[1:].reset_index(drop=True)

# Normalize column names FIRST
df.columns = (
    df.columns
    .astype(str)
    .str.strip()
    .str.lower()
    .str.replace(r"[^\w]+", "_", regex=True)
    .str.strip("_")
)

print("FINAL COLUMNS:")
for c in df.columns:
    print(repr(c))

# Detect approved project cost column safely
for col in df.columns:
    if "approved" in col and "cost" in col:
        approved_cost_col = col
        break
else:
    raise ValueError("Approved project cost column not found")

import re

df[approved_cost_col] = (
    df[approved_cost_col]
    .astype(str)
    .str.replace(r"[^\d.]", "", regex=True)
    .replace("", None)
    .astype(float)
)


# Rename long column (after normalization)
df = df.rename(columns={
    "funds_available_in_the_specific_year": "funds_available_specific_year"
})

# Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="Jeju.2025",
    database="projects_db"
)

cursor = conn.cursor()

cols = ", ".join(df.columns)
placeholders = ", ".join(["%s"] * len(df.columns))
sql = f"INSERT INTO projects ({cols}) VALUES ({placeholders})"

cursor.executemany(sql, df.values.tolist())
conn.commit()

cursor.close()
conn.close()

print("Excel data successfully imported into MySQL")