main.py

import csv
import io
from decimal import Decimal, InvalidOperation

from fastapi import FastAPI, UploadFile, File, Depends, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles
from sqlalchemy.orm import Session

from database import engine, get_db, Base
from models import Employee

Base.metadata.create_all(bind=engine)

app = FastAPI(title="Employee CSV Upload API")

# ---------------------------------------------------------------------------
# CORS – allow all origins so the plain HTML frontend can reach the API
# ---------------------------------------------------------------------------
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Serve the frontend at /
app.mount("/frontend", StaticFiles(directory="frontend", html=True), name="frontend")

# ---------------------------------------------------------------------------
# Required columns for the CSV
# ---------------------------------------------------------------------------
REQUIRED_COLUMNS = {"emp_id", "name", "age", "department", "salary", "city", "skills", "is_active"}

TRUE_VALUES = {"true", "yes", "1", "t"}
FALSE_VALUES = {"false", "no", "0", "f"}


def parse_boolean(value: str, row: int, field: str) -> bool:
    """Parse a string boolean value. Raises ValueError on invalid input."""
    v = value.strip().lower()
    if v in TRUE_VALUES:
        return True
    if v in FALSE_VALUES:
        return False
    raise ValueError(f"Row {row}: '{field}' must be a boolean (TRUE/FALSE), got '{value}'")


def parse_skills(value: str) -> list[str]:
    """Parse a comma-separated skills string into a list of non-empty strings."""
    skills = [s.strip() for s in value.split(",") if s.strip()]
    return skills


# ---------------------------------------------------------------------------
# Upload endpoint
# ---------------------------------------------------------------------------
@app.post("/api/upload")
async def upload_csv(file: UploadFile = File(...), db: Session = Depends(get_db)):
    # Validate file type
    if not file.filename.lower().endswith(".csv"):
        raise HTTPException(status_code=400, detail="Only .csv files are accepted.")

    content = await file.read()
    try:
        text = content.decode("utf-8")
    except UnicodeDecodeError:
        raise HTTPException(status_code=400, detail="File must be UTF-8 encoded.")

    reader = csv.DictReader(io.StringIO(text))

    # Validate headers
    if reader.fieldnames is None:
        raise HTTPException(status_code=400, detail="CSV file is empty or has no headers.")

    actual_columns = {col.strip() for col in reader.fieldnames}
    missing_headers = REQUIRED_COLUMNS - actual_columns
    if missing_headers:
        raise HTTPException(
            status_code=400,
            detail=f"CSV is missing required columns: {', '.join(sorted(missing_headers))}",
        )

    errors: list[dict] = []
    employees: list[Employee] = []
    seen_emp_ids: dict[int, int] = {}  # emp_id -> first row number

    for row_num, raw_row in enumerate(reader, start=2):  # row 1 is headers
        row = {k.strip(): (v.strip() if v else "") for k, v in raw_row.items()}
        row_errors: list[str] = []

        # Check for missing / empty required columns
        for col in REQUIRED_COLUMNS:
            if not row.get(col):
                row_errors.append(f"'{col}' is missing or empty")

        if row_errors:
            errors.append({"row": row_num, "errors": row_errors})
            continue

        # --- emp_id ---
        emp_id = None
        try:
            emp_id = int(row["emp_id"])
        except ValueError:
            row_errors.append(f"'emp_id' must be an integer, got '{row['emp_id']}'")

        # --- age ---
        age = None
        try:
            age = int(row["age"])
            if age <= 0:
                row_errors.append(f"'age' must be a positive integer, got '{row['age']}'")
        except ValueError:
            row_errors.append(f"'age' must be an integer, got '{row['age']}'")

        # --- salary ---
        salary = None
        try:
            salary = Decimal(row["salary"])
            if salary < 0:
                row_errors.append(f"'salary' must be a non-negative number, got '{row['salary']}'")
        except InvalidOperation:
            row_errors.append(f"'salary' must be a numeric value, got '{row['salary']}'")

        # --- is_active ---
        is_active = None
        try:
            is_active = parse_boolean(row["is_active"], row_num, "is_active")
        except ValueError as e:
            row_errors.append(str(e))

        # --- skills ---
        skills = parse_skills(row["skills"])
        if not skills:
            row_errors.append("'skills' must contain at least one skill")

        # --- duplicate emp_id check ---
        if emp_id is not None:
            if emp_id in seen_emp_ids:
                row_errors.append(
                    f"'emp_id' {emp_id} is a duplicate (first seen at row {seen_emp_ids[emp_id]})"
                )
            else:
                seen_emp_ids[emp_id] = row_num

        if row_errors:
            errors.append({"row": row_num, "errors": row_errors})
        else:
            employees.append(
                Employee(
                    emp_id=emp_id,
                    name=row["name"],
                    age=age,
                    department=row["department"],
                    salary=salary,
                    city=row["city"],
                    skills=skills,
                    is_active=is_active,
                )
            )

    # If any validation errors, return 400 without touching the DB
    if errors:
        raise HTTPException(status_code=400, detail=errors)

    # Bulk insert
    try:
        db.add_all(employees)
        db.commit()
    except Exception as exc:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Database error: {str(exc)}")

    return {
        "message": f"Successfully uploaded {len(employees)} employee record(s).",
        "inserted": len(employees),
    }