import os
import pandas as pd
import sqlite3
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
DATA_DIR = BASE_DIR / "data"

DATA_DIR.mkdir(exist_ok=True)

excel_path = DATA_DIR / "TaskbookExport_20251001_050840202.xlsx"
db_path = DATA_DIR / "nasa_data.db"

if not excel_path.exists():
    raise FileNotFoundError(f"excel file not found at: {excel_path}\n"
                            f"uploaded it or placed it in the /data folder")

conn = sqlite3.connect(db_path)
print(f"connected to SQLite DB: {db_path}")

df = pd.read_excel(excel_path)
print(f"total records loaded: {len(df)}")
print(f"shape: {df.shape}")


# print(df.columns.tolist())

cols_to_keep = [
    "Task_id",
    "Project Title",
    "Task Abstract/Description",
    "Research Impact/Earth Benefit",
    "Fiscal Year",
    "PI First Name",
    "PI Last Name"
]


missing = [c for c in cols_to_keep if c not in df.columns]
if missing:
    raise ValueError(f"missing expected columns in Excel: {missing}")

copy_df = df[cols_to_keep].copy()

#fname lname to name
copy_df["PI_Name"] = copy_df["PI First Name"].fillna("") + " " + copy_df["PI Last Name"].fillna("")
copy_df.drop(["PI First Name", "PI Last Name"], axis=1, inplace=True)

print("sample cleaned rows:")
print(copy_df.head(3))

copy_df.to_sql("projects", conn, if_exists="replace", index=False)
print("'projects' table stored in DB")

cursor = conn.cursor()

cursor.execute("SELECT COUNT(*) FROM projects")
total_rows = cursor.fetchone()[0]
print(f"total projects in DB: {total_rows}")

cursor.execute("SELECT * FROM projects LIMIT 5")
for row in cursor.fetchall():
    print("--->", row)

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print("tables in db:", cursor.fetchall())

conn.close()
print("db connection closed")