"""
Settings Frontend Views
"""

from datetime import datetime
from pathlib import Path
from fastapi import APIRouter, Request, HTTPException
from fastapi.responses import HTMLResponse
from fastapi.templating import Jinja2Templates
from pydantic import BaseModel

from app.core.config import settings
from app.core.database import get_db_connection, release_db_connection, execute_query_single

router = APIRouter()
templates = Jinja2Templates(directory="app")


@router.get("/settings", response_class=HTMLResponse, tags=["Frontend"])
async def settings_page(request: Request):
    """Render settings page"""
    default_dashboard_path = ""
    user_id = getattr(request.state, "user_id", None)

    if user_id:
        try:
            row = execute_query_single(
                """
                SELECT default_dashboard_path
                FROM user_dashboard_preferences
                WHERE user_id = %s
                """,
                (int(user_id),)
            )
            default_dashboard_path = (row or {}).get("default_dashboard_path") or ""
        except Exception:
            default_dashboard_path = ""

    return templates.TemplateResponse("settings/frontend/settings.html", {
        "request": request,
        "title": "Indstillinger",
        "default_dashboard_path": default_dashboard_path
    })


@router.get("/settings/migrations", response_class=HTMLResponse, tags=["Frontend"])
async def migrations_page(request: Request):
    """Render database migrations page"""
    migrations_dir = Path(__file__).resolve().parents[3] / "migrations"
    migrations = []

    if migrations_dir.exists():
        for migration_file in sorted(migrations_dir.glob("*.sql")):
            stat = migration_file.stat()
            migrations.append({
                "name": migration_file.name,
                "size_kb": round(stat.st_size / 1024, 1),
                "modified": datetime.fromtimestamp(stat.st_mtime).strftime("%Y-%m-%d %H:%M")
            })

    return templates.TemplateResponse("settings/frontend/migrations.html", {
        "request": request,
        "title": "Database Migrationer",
        "migrations": migrations,
        "db_user": settings.POSTGRES_USER,
        "db_name": settings.POSTGRES_DB,
        "db_container": "bmc-hub-postgres",
        "is_production": request.url.hostname not in ['localhost', '127.0.0.1', '0.0.0.0']
    })


class MigrationExecution(BaseModel):
    file_name: str


@router.post("/settings/migrations/execute", tags=["Frontend"])
def execute_migration(payload: MigrationExecution):
    """Execute a migration SQL file"""
    migrations_dir = Path(__file__).resolve().parents[3] / "migrations"
    migration_file = migrations_dir / payload.file_name

    if not migration_file.exists():
        raise HTTPException(status_code=404, detail="Migration file not found")

    # Determine the container runtime (Podman or Docker)
    migration_sql = migration_file.read_text()
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(migration_sql)
        conn.commit()
    except Exception as exc:
        conn.rollback()
        raise HTTPException(status_code=500, detail=f"Migration failed: {exc}")
    finally:
        release_db_connection(conn)

    return {"message": "Migration executed successfully"}
