from __future__ import annotations

import os
import secrets
import shutil
from datetime import date, datetime
from pathlib import Path
from typing import Any

from dotenv import load_dotenv
from fastapi import Depends, FastAPI, File, Form, HTTPException, Request, UploadFile
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel
from sqlalchemy import Boolean, Date, DateTime, Integer, String, Text, create_engine, inspect as sqlalchemy_inspect, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, sessionmaker
from starlette.middleware.sessions import SessionMiddleware

BACKEND_DIR = Path(__file__).resolve().parents[1]
PROJECT_ROOT = BACKEND_DIR.parent
load_dotenv(BACKEND_DIR / ".env")

DATABASE_URL = os.getenv("DATABASE_URL", "mysql+pymysql:kineticcosmos_kanhaherbs_25//Karina@2002@127.0.0.1:3306/kineticcosmos_bmvpcollege?charset=utf8mb4")
upload_setting = Path(os.getenv("UPLOAD_DIR", "../public/uploads"))
UPLOAD_DIR = (upload_setting if upload_setting.is_absolute() else BACKEND_DIR / upload_setting).resolve()
UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class About(Base, TimestampMixin):
    __tablename__ = "abouts"
    id: Mapped[int] = mapped_column(primary_key=True)
    description: Mapped[str | None] = mapped_column(Text)
    location: Mapped[str | None] = mapped_column(Text)
    courses: Mapped[str | None] = mapped_column(Text)
    intake: Mapped[str | None] = mapped_column(String(255))
    image: Mapped[str | None] = mapped_column(String(255))


class Staff(Base, TimestampMixin):
    __tablename__ = "staffs"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    designation: Mapped[str | None] = mapped_column(String(255))
    type: Mapped[str] = mapped_column(String(40), default="teaching")


class HomeSlider(Base, TimestampMixin):
    __tablename__ = "home_sliders"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str | None] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    image: Mapped[str] = mapped_column(String(255))
    status: Mapped[str | None] = mapped_column(String(40), default="active")
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class NoticeBoard(Base, TimestampMixin):
    __tablename__ = "notice_boards"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    file: Mapped[str | None] = mapped_column(String(255))
    url: Mapped[str | None] = mapped_column(String(1000))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class CollegeInformation(Base, TimestampMixin):
    __tablename__ = "college_informations"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    image: Mapped[str | None] = mapped_column(String(255))
    file: Mapped[str | None] = mapped_column(String(255))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class Facility(Base, TimestampMixin):
    __tablename__ = "facilities"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    url: Mapped[str | None] = mapped_column(String(1000))
    image: Mapped[str | None] = mapped_column(String(255))
    pdf: Mapped[str | None] = mapped_column(String(255))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class Faculty(Base, TimestampMixin):
    __tablename__ = "faculty"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    image: Mapped[str | None] = mapped_column(String(255))
    file: Mapped[str | None] = mapped_column(String(255))
    url: Mapped[str | None] = mapped_column(String(1000))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class ImportantDownload(Base, TimestampMixin):
    __tablename__ = "important_downloads"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    url: Mapped[str | None] = mapped_column(String(1000))
    file: Mapped[str | None] = mapped_column(String(255))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class StudentArea(Base, TimestampMixin):
    __tablename__ = "students_area"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    file: Mapped[str | None] = mapped_column(String(255))
    url: Mapped[str | None] = mapped_column(String(1000))
    date: Mapped[date | None] = mapped_column(Date)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)


class Category(Base, TimestampMixin):
    __tablename__ = "categories"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255), unique=True)


class Gallery(Base, TimestampMixin):
    __tablename__ = "galleries"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str | None] = mapped_column(String(255))
    subtitle: Mapped[str | None] = mapped_column(String(255))
    image: Mapped[str] = mapped_column(String(255))
    date: Mapped[date | None] = mapped_column(Date)
    description: Mapped[str | None] = mapped_column(Text)
    is_delete: Mapped[bool] = mapped_column(Boolean, default=False)
    category_id: Mapped[int | None] = mapped_column(Integer, index=True)


class Contact(Base, TimestampMixin):
    __tablename__ = "contacts"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    phone: Mapped[str] = mapped_column(String(40))
    email: Mapped[str] = mapped_column(String(255))
    subject: Mapped[str] = mapped_column(String(255))
    message: Mapped[str | None] = mapped_column(Text)


class Complaint(Base, TimestampMixin):
    __tablename__ = "complaints"
    id: Mapped[int] = mapped_column(primary_key=True)
    full_name: Mapped[str] = mapped_column(String(255))
    phone: Mapped[str] = mapped_column(String(40))
    email: Mapped[str] = mapped_column(String(255))
    subject: Mapped[str] = mapped_column(String(255))
    security_key: Mapped[str | None] = mapped_column(String(255))
    message: Mapped[str] = mapped_column(Text)


MODELS: dict[str, type[Base]] = {
    "sliders": HomeSlider, "notices": NoticeBoard, "about": About, "staff": Staff,
    "college-information": CollegeInformation, "facilities": Facility, "faculty": Faculty,
    "downloads": ImportantDownload, "students-area": StudentArea, "categories": Category,
    "gallery": Gallery,
}
MODELS.update({"slider": HomeSlider, "notice": NoticeBoard, "college-info": CollegeInformation, "category": Category})
REQUIRED_FIELDS = {
    "slider": {"title", "image"}, "sliders": {"title", "image"},
    "notice": {"title"}, "notices": {"title"},
    "college-info": {"title"}, "college-information": {"title"},
    "facilities": {"title"}, "faculty": {"title"}, "downloads": {"title"},
    "students-area": {"title"}, "categories": {"name"}, "category": {"name"},
    "gallery": {"title", "image", "category_id"},
}

app = FastAPI(title="BMVP College API", version="1.0.0")
app.add_middleware(SessionMiddleware, secret_key=os.getenv("SESSION_SECRET", "development-only-change-me"), same_site="lax", https_only=False)
app.add_middleware(CORSMiddleware, allow_origins=[os.getenv("FRONTEND_ORIGIN", "http://localhost:3000")], allow_credentials=True, allow_methods=["*"], allow_headers=["*"])
app.mount("/uploads", StaticFiles(directory=str(UPLOAD_DIR)), name="uploads")


def db_session():
    with SessionLocal() as session:
        yield session


def serialize(row: Base) -> dict[str, Any]:
    return {column.name: getattr(row, column.name) for column in row.__table__.columns}


def active_query(model: type[Base], db: Session):
    query = select(model)
    if "is_delete" in model.__table__.columns:
        query = query.where(model.is_delete.is_(False))
    return db.scalars(query.order_by(model.id.desc())).all()


def require_admin(request: Request):
    if not request.session.get("admin"):
        raise HTTPException(status_code=401, detail="Admin login required")


@app.on_event("startup")
def startup():
    Base.metadata.create_all(engine)
    slider_columns = {column["name"] for column in sqlalchemy_inspect(engine).get_columns("home_sliders")}
    if "subtitle" not in slider_columns:
        with engine.begin() as connection:
            connection.execute(text("ALTER TABLE home_sliders ADD COLUMN subtitle VARCHAR(255) NULL"))


@app.get("/api/health")
def health(db: Session = Depends(db_session)):
    db.execute(select(1))
    return {"ok": True, "database": "mysql"}


@app.get("/api/public/gallery-categories")
def public_gallery_categories(db: Session = Depends(db_session)):
    categories = db.scalars(select(Category).order_by(Category.name)).all()
    galleries = active_query(Gallery, db)
    by_category: dict[int, list[dict[str, Any]]] = {}
    for gallery in galleries:
        if gallery.category_id is not None:
            by_category.setdefault(gallery.category_id, []).append(serialize(gallery))
    return [{**serialize(category), "galleries": by_category.get(category.id, [])} for category in categories]


@app.get("/api/public/{resource}")
def public_collection(resource: str, db: Session = Depends(db_session)):
    if resource not in MODELS:
        raise HTTPException(404, "Unknown resource")
    if resource == "notices":
        rows = db.scalars(select(NoticeBoard).where(NoticeBoard.is_delete.is_(False)).order_by(NoticeBoard.date.desc(), NoticeBoard.id.desc())).all()
        return [serialize(row) for row in rows]
    if resource == "facilities":
        rows = db.scalars(select(Facility).where(Facility.is_delete.is_(False)).order_by(Facility.title)).all()
        return [serialize(row) for row in rows]
    if resource == "about":
        rows = db.scalars(select(About).order_by(About.id.asc())).all()
        return [serialize(row) for row in rows]
    if resource == "staff":
        rows = db.scalars(select(Staff).order_by(Staff.id.asc())).all()
        return [serialize(row) for row in rows]
    return [serialize(row) for row in active_query(MODELS[resource], db)]


@app.get("/api/public/{resource}/{item_id}")
def public_item(resource: str, item_id: int, db: Session = Depends(db_session)):
    if resource not in MODELS:
        raise HTTPException(404, "Unknown resource")
    row = db.get(MODELS[resource], item_id)
    if not row or ("is_delete" in row.__table__.columns and row.is_delete):
        raise HTTPException(404, "Not found")
    return serialize(row)


@app.post("/api/contact", status_code=201)
def contact(payload: dict[str, Any], db: Session = Depends(db_session)):
    for field in ("name", "phone", "email", "subject"):
        if not str(payload.get(field, "")).strip():
            raise HTTPException(422, f"{field} is required")
    row = Contact(**{k: payload.get(k) for k in ("name", "phone", "email", "subject", "message")})
    db.add(row); db.commit()
    return {"message": "Message sent successfully!"}


@app.post("/api/complaints", status_code=201)
def complaint(payload: dict[str, Any], db: Session = Depends(db_session)):
    for field in ("full_name", "phone", "email", "subject", "message"):
        if not str(payload.get(field, "")).strip():
            raise HTTPException(422, f"{field} is required")
    row = Complaint(**{k: payload.get(k) for k in ("full_name", "phone", "email", "subject", "message")})
    db.add(row); db.commit()
    return {"message": "Complaint submitted successfully"}


class LoginPayload(BaseModel):
    userid: str
    password: str


@app.post("/api/admin/login")
def login(payload: LoginPayload, request: Request):
    if not secrets.compare_digest(payload.userid, os.getenv("ADMIN_USER", "bmvpadmin")) or not secrets.compare_digest(payload.password, os.getenv("ADMIN_PASSWORD", "bmvpadmin")):
        raise HTTPException(401, "Invalid Credentials")
    request.session["admin"] = True
    return {"ok": True}


@app.post("/api/admin/logout")
def logout(request: Request):
    request.session.clear()
    return {"ok": True}


@app.get("/api/admin/session")
def admin_session(request: Request):
    return {"authenticated": bool(request.session.get("admin"))}


@app.get("/api/admin/dashboard")
def admin_dashboard(request: Request):
    require_admin(request)
    return []


@app.get("/api/admin/{resource}")
def admin_collection(resource: str, request: Request, db: Session = Depends(db_session)):
    require_admin(request)
    if resource not in MODELS: raise HTTPException(404, "Unknown resource")
    return [serialize(row) for row in active_query(MODELS[resource], db)]


@app.post("/api/admin/{resource}", status_code=201)
def create_item(resource: str, payload: dict[str, Any], request: Request, db: Session = Depends(db_session)):
    require_admin(request)
    model = MODELS.get(resource)
    if not model: raise HTTPException(404, "Unknown resource")
    allowed = {c.name for c in model.__table__.columns} - {"id", "created_at", "updated_at"}
    missing = [field for field in REQUIRED_FIELDS.get(resource, set()) if payload.get(field) in (None, "")]
    if missing:
        raise HTTPException(422, f"Required fields: {', '.join(sorted(missing))}")
    row = model(**coerce_payload(model, payload, allowed))
    db.add(row); db.commit(); db.refresh(row)
    return serialize(row)


@app.put("/api/admin/{resource}/{item_id}")
def update_item(resource: str, item_id: int, payload: dict[str, Any], request: Request, db: Session = Depends(db_session)):
    require_admin(request)
    model = MODELS.get(resource)
    if not model: raise HTTPException(404, "Unknown resource")
    row = db.get(model, item_id)
    if not row: raise HTTPException(404, "Not found")
    allowed = {c.name for c in model.__table__.columns} - {"id", "created_at", "updated_at"}
    for key, value in coerce_payload(model, payload, allowed).items():
        setattr(row, key, value)
    db.commit(); db.refresh(row)
    return serialize(row)


@app.delete("/api/admin/{resource}/{item_id}")
def delete_item(resource: str, item_id: int, request: Request, db: Session = Depends(db_session)):
    require_admin(request)
    model = MODELS.get(resource)
    if not model: raise HTTPException(404, "Unknown resource")
    row = db.get(model, item_id)
    if not row: raise HTTPException(404, "Not found")
    if "is_delete" in row.__table__.columns: row.is_delete = True
    else: db.delete(row)
    db.commit()
    return {"ok": True}


def coerce_payload(model: type[Base], payload: dict[str, Any], allowed: set[str]) -> dict[str, Any]:
    columns = {column.name: column for column in model.__table__.columns}
    result = {}
    for key, value in payload.items():
        if key not in allowed:
            continue
        if value == "":
            value = None
        if value is not None and isinstance(columns[key].type, Date) and isinstance(value, str):
            value = date.fromisoformat(value)
        if value is not None and isinstance(columns[key].type, Boolean):
            value = value if isinstance(value, bool) else str(value).lower() in {"1", "true", "yes", "on"}
        result[key] = value
    return result


@app.post("/api/admin/uploads/{folder}")
async def upload(folder: str, request: Request, file: UploadFile = File(...)):
    require_admin(request)
    if folder not in {"about", "college", "downloads", "facilities", "faculty", "gallery", "notice", "slider", "students"}:
        raise HTTPException(400, "Invalid upload folder")
    suffix = Path(file.filename or "upload.bin").suffix.lower()
    if suffix not in {".png", ".jpg", ".jpeg", ".gif", ".webp", ".pdf"}:
        raise HTTPException(400, "Unsupported file type")
    target_dir = UPLOAD_DIR / folder
    target_dir.mkdir(parents=True, exist_ok=True)
    name = f"{int(datetime.utcnow().timestamp())}_{secrets.token_hex(4)}{suffix}"
    with (target_dir / name).open("wb") as destination:
        shutil.copyfileobj(file.file, destination)
    return {"file": name, "url": f"/uploads/{folder}/{name}"}