# app.py

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import Union
import sqlite3
import os
import pandas as pd # Import pandas for CSV reading

# Define the Pydantic model for Item creation/update
class Item(BaseModel):
    date: str
    time: str
    lat: float
    lon: float
    depth: float
    ML: float
    nstn: int
    dmin: float
    gap: int
    trms: float
    ERH: float
    ERZ: float
    fixed: str
    nph: int
    quality: str

# Define the Pydantic model for Item update specifically (allowing partial updates)
class ItemUpdate(BaseModel):
    date: Union[str, None] = None
    time: Union[str, None] = None
    lat: Union[float, None] = None
    lon: Union[float, None] = None
    depth: Union[float, None] = None
    ML: Union[float, None] = None
    nstn: Union[int, None] = None
    dmin: Union[float, None] = None
    gap: Union[int, None] = None
    trms: Union[float, None] = None
    ERH: Union[float, None] = None
    ERZ: Union[float, None] = None
    fixed: Union[str, None] = None
    nph: Union[int, None] = None
    quality: Union[str, None] = None

app = FastAPI()

# Define the path for your SQLite database file
DATABASE_FILE = os.path.join(os.getcwd(), "data.db")
CSV_FILE_PATH = "GDMScatalog.csv" # Path to your CSV file

def get_db_connection():
    """Establishes and returns a SQLite database connection."""
    try:
        conn = sqlite3.connect(DATABASE_FILE)
        conn.row_factory = sqlite3.Row
        return conn
    except sqlite3.Error as err:
        print(f"Error connecting to database: {err}")
        raise HTTPException(status_code=500, detail="Database connection error")

@app.on_event("startup")
async def startup_event():
    """
    Initializes the database: creates the 'items' table if it doesn't exist.
    Also, populates the database with data from the CSV file if the table is empty.
    This runs once when the FastAPI application starts.
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Create table if it doesn't exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                time TEXT NOT NULL,
                lat REAL NOT NULL,
                lon REAL NOT NULL,
                depth REAL NOT NULL,
                ML REAL NOT NULL,
                nstn INTEGER NOT NULL,
                dmin REAL NOT NULL,
                gap INTEGER NOT NULL,
                trms REAL NOT NULL,
                ERH REAL NOT NULL,
                ERZ REAL NOT NULL,
                fixed TEXT NOT NULL,
                nph INTEGER NOT NULL,
                quality TEXT NOT NULL
            )
        """)
        conn.commit()
        print(f"Database table 'items' checked/created successfully at {DATABASE_FILE}.")

        # Check if the table is empty and populate from CSV
        cursor.execute("SELECT COUNT(*) FROM items")
        count = cursor.fetchone()[0]

        if count == 0:
            print("Table 'items' is empty. Populating from CSV file...")
            if os.path.exists(CSV_FILE_PATH):
                try:
                    df = pd.read_csv(CSV_FILE_PATH)
                    # Convert DataFrame to a list of tuples for insertion
                    # Ensure the order of columns matches the INSERT query
                    data_to_insert = [
                        (row['date'], row['time'], row['lat'], row['lon'], row['depth'],
                         row['ML'], row['nstn'], row['dmin'], row['gap'], row['trms'],
                         row['ERH'], row['ERZ'], row['fixed'], row['nph'], row['quality'])
                        for index, row in df.iterrows()
                    ]

                    insert_query = """
                        INSERT INTO items (date, time, lat, lon, depth, ML, nstn, dmin, gap, trms, ERH, ERZ, fixed, nph, quality)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """
                    cursor.executemany(insert_query, data_to_insert)
                    conn.commit()
                    print(f"Successfully inserted {len(data_to_insert)} records from {CSV_FILE_PATH}.")
                except Exception as e:
                    print(f"Error populating database from CSV: {e}")
                    conn.rollback() # Rollback if CSV insertion fails
            else:
                print(f"CSV file not found at {CSV_FILE_PATH}. Database not populated.")
        else:
            print(f"Table 'items' already contains {count} records. Skipping CSV import.")

    except Exception as e:
        print(f"Error during database startup: {e}")
    finally:
        if conn:
            conn.close()

@app.get("/")
async def root():
    """Root endpoint for the API."""
    return {"message": "Welcome to the API! https://cwadayi-sqlite-api.hf.space/items/"}

@app.post("/items/")
async def create_item(item: Item):
    """Creates a new item in the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query = """
            INSERT INTO items (date, time, lat, lon, depth, ML, nstn, dmin, gap, trms, ERH, ERZ, fixed, nph, quality)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        cursor.execute(query, (
            item.date,
            item.time,
            item.lat,
            item.lon,
            item.depth,
            item.ML,
            item.nstn,
            item.dmin,
            item.gap,
            item.trms,
            item.ERH,
            item.ERZ,
            item.fixed,
            item.nph,
            item.quality
        ))
        conn.commit()
        return {"message": "Item created successfully", "id": cursor.lastrowid}
    except Exception as e:
        conn.rollback()
        raise HTTPException(status_code=500, detail=f"Error creating item: {e}")
    finally:
        cursor.close()
        conn.close()

@app.get("/items/")
async def read_items():
    """Retrieves all items from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM items")
        items = cursor.fetchall()
        return {"items": [dict(item) for item in items]}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error reading items: {e}")
    finally:
        cursor.close()
        conn.close()

@app.get("/items/{item_id}")
async def read_item(item_id: int):
    """Retrieves a single item by its ID."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM items WHERE id = ?", (item_id,))
        item = cursor.fetchone()
        if item is None:
            raise HTTPException(status_code=404, detail="Item not found")
        return dict(item)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error reading item: {e}")
    finally:
        cursor.close()
        conn.close()

@app.put("/items/{item_id}")
async def update_item(item_id: int, item: ItemUpdate):
    """Updates an existing item by its ID."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        updates = []
        params = []

        if item.date is not None:
            updates.append("date = ?")
            params.append(item.date)
        if item.time is not None:
            updates.append("time = ?")
            params.append(item.time)
        if item.lat is not None:
            updates.append("lat = ?")
            params.append(item.lat)
        if item.lon is not None:
            updates.append("lon = ?")
            params.append(item.lon)
        if item.depth is not None:
            updates.append("depth = ?")
            params.append(item.depth)
        if item.ML is not None:
            updates.append("ML = ?")
            params.append(item.ML)
        if item.nstn is not None:
            updates.append("nstn = ?")
            params.append(item.nstn)
        if item.dmin is not None:
            updates.append("dmin = ?")
            params.append(item.dmin)
        if item.gap is not None:
            updates.append("gap = ?")
            params.append(item.gap)
        if item.trms is not None:
            updates.append("trms = ?")
            params.append(item.trms)
        if item.ERH is not None:
            updates.append("ERH = ?")
            params.append(item.ERH)
        if item.ERZ is not None:
            updates.append("ERZ = ?")
            params.append(item.ERZ)
        if item.fixed is not None:
            updates.append("fixed = ?")
            params.append(item.fixed)
        if item.nph is not None:
            updates.append("nph = ?")
            params.append(item.nph)
        if item.quality is not None:
            updates.append("quality = ?")
            params.append(item.quality)

        if not updates:
            raise HTTPException(status_code=400, detail="No fields to update provided")

        query = f"UPDATE items SET {', '.join(updates)} WHERE id = ?"
        params.append(item_id)

        cursor.execute(query, tuple(params))
        conn.commit()

        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Item not found")
        return {"message": "Item updated successfully"}
    except HTTPException:
        raise
    except Exception as e:
        conn.rollback()
        raise HTTPException(status_code=500, detail=f"Error updating item: {e}")
    finally:
        cursor.close()
        conn.close()

@app.delete("/items/{item_id}")
async def delete_item(item_id: int):
    """Deletes an item by its ID."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM items WHERE id = ?", (item_id,))
        conn.commit()
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Item not found")
        return {"message": "Item deleted successfully"}
    except Exception as e:
        conn.rollback()
        raise HTTPException(status_code=500, detail=f"Error deleting item: {e}")
    finally:
        cursor.close()
        conn.close()