routes_admin.py

"""Admin-only routes for managing users and viewing audit log."""

import datetime
import re
from typing import Dict, List, Optional

import mariadb
from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel

from db_utils import get_db_connection, validate_table_and_columns, write_audit_log
from security import User, UserCreate, get_current_admin, get_password_hash
from settings import logger

router = APIRouter(prefix="/admin", tags=["admin"])


class AuditLogEntry(BaseModel):
    id: int
    actor: str
    action: str
    table_name: str
    row_id: Optional[int] = None
    details: Optional[str] = None
    created_at: datetime.datetime


@router.post("/users", response_model=User)
async def create_user(user: UserCreate, current_admin: User = Depends(get_current_admin)):
    role = user.role.lower()
    if role not in ("user", "admin"):
        raise HTTPException(status_code=400, detail="Role must be 'user' or 'admin'")

    hashed_password = get_password_hash(user.password)

    conn = get_db_connection()
    try:
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO users (username, password_hash, role) VALUES (%s, %s, %s)",
            (user.username, hashed_password, role),
        )
        conn.commit()
        user_id = cur.lastrowid
    except mariadb.Error as e:
        conn.rollback()
        logger.exception("Error creating user '%s'", user.username)
        raise HTTPException(status_code=400, detail=f"Error creating user: {e}")
    finally:
        conn.close()

    write_audit_log(current_admin.username, "create_user", "users", user_id, {"username": user.username, "role": role})
    return User(id=user_id, username=user.username, role=role)


@router.get("/users", response_model=List[User])
async def list_users(current_admin: User = Depends(get_current_admin)):
    conn = get_db_connection()
    try:
        cur = conn.cursor(dictionary=True)
        cur.execute("SELECT id, username, role FROM users")
        rows = cur.fetchall()
        return [User(id=row["id"], username=row["username"], role=row["role"]) for row in rows]
    finally:
        conn.close()


@router.get("/audit", response_model=List[AuditLogEntry])
async def get_audit_log(limit: int = 100, current_admin: User = Depends(get_current_admin)):
    if limit <= 0 or limit > 1000:
        raise HTTPException(status_code=400, detail="Limit must be between 1 and 1000")

    conn = get_db_connection()
    try:
        cur = conn.cursor(dictionary=True)
        cur.execute(
            "SELECT id, actor, action, table_name, row_id, details, created_at "
            "FROM audit_log ORDER BY created_at DESC, id DESC LIMIT %s",
            (limit,),
        )
        rows = cur.fetchall()
        result: List[AuditLogEntry] = []
        for row in rows:
            result.append(
                AuditLogEntry(
                    id=row["id"],
                    actor=row["actor"],
                    action=row["action"],
                    table_name=row["table_name"],
                    row_id=row.get("row_id"),
                    details=row.get("details"),
                    created_at=row["created_at"],
                )
            )
        return result
    except mariadb.Error as e:
        logger.exception("Error reading audit log")
        raise HTTPException(status_code=500, detail=f"Error reading audit log: {e}")
    finally:
        conn.close()


class ColumnInfo(BaseModel):
    name: str
    data_type: str
    is_nullable: bool
    is_primary_key: bool


class TableSchema(BaseModel):
    table: str
    columns: List[ColumnInfo]


class NewTableColumn(BaseModel):
    """Column definition for creating a new table.

    type is a logical type, mapped to MariaDB types:
    - "string"   -> VARCHAR(length or 255)
    - "text"     -> TEXT
    - "int"      -> INT
    - "bigint"   -> BIGINT
    - "float"    -> DOUBLE
    - "bool"     -> TINYINT(1)
    - "datetime" -> DATETIME
    - "date"     -> DATE
    - "time"     -> TIME

    Additional options:
    - nullable: whether the column can be NULL
    - unique: whether the column has a UNIQUE constraint
    - default: default value as string (validated and converted based on type)
    """

    name: str
    type: str
    nullable: bool = True
    length: Optional[int] = None
    unique: bool = False
    default: Optional[str] = None


class CreateTableRequest(BaseModel):
    table: str
    columns: List[NewTableColumn]


@router.post("/tables")
async def create_table(req: CreateTableRequest, current_admin: User = Depends(get_current_admin)):
    """Create a new table with an auto-increment 'id' primary key and custom columns.

    Admins specify table name and a list of columns (name, type, nullable, optional length).
    """

    # Basic name validation to avoid SQL injection via identifiers
    identifier_re = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")

    table_name = req.table.strip()
    if not table_name or not identifier_re.match(table_name):
        raise HTTPException(status_code=400, detail="Invalid table name. Use letters, numbers and '_' and do not start with a digit.")

    if not req.columns:
        raise HTTPException(status_code=400, detail="At least one column must be defined")
    if len(req.columns) > 50:
        raise HTTPException(status_code=400, detail="Too many columns (max 50)")

    # Validate columns and build SQL fragments
    seen_cols = set()
    user_columns_sql: List[str] = []

    def map_type(col: NewTableColumn) -> str:
        t = col.type.strip().lower()
        if t in {"string", "varchar"}:
            length = col.length or 255
            if length <= 0 or length > 65535:
                raise HTTPException(status_code=400, detail=f"Invalid length for column '{col.name}'")
            return f"VARCHAR({length})"
        if t in {"text"}:
            return "TEXT"
        if t in {"int", "integer"}:
            return "INT"
        if t in {"bigint"}:
            return "BIGINT"
        if t in {"float", "double", "number"}:
            return "DOUBLE"
        if t in {"bool", "boolean"}:
            return "TINYINT(1)"
        if t in {"datetime", "timestamp"}:
            return "DATETIME"
        if t in {"date"}:
            return "DATE"
        if t in {"time"}:
            return "TIME"
        raise HTTPException(status_code=400, detail=f"Unsupported column type '{col.type}' for column '{col.name}'")

    def build_default_sql(col: NewTableColumn) -> Optional[str]:
        if col.default is None or col.default == "":
            return None
        raw = col.default.strip()
        # Disallow semicolons to avoid multi-statement injection
        if ";" in raw:
            raise HTTPException(status_code=400, detail=f"Invalid default value for column '{col.name}'")
        t = col.type.strip().lower()
        # String-like
        if t in {"string", "varchar", "text"}:
            escaped = raw.replace("'", "''")
            return f"'{escaped}'"
        # Numeric
        if t in {"int", "integer", "bigint"}:
            try:
                int(raw)
            except ValueError:
                raise HTTPException(status_code=400, detail=f"Default for column '{col.name}' must be an integer")
            return raw
        if t in {"float", "double", "number"}:
            try:
                float(raw)
            except ValueError:
                raise HTTPException(status_code=400, detail=f"Default for column '{col.name}' must be a number")
            return raw
        # Bool
        if t in {"bool", "boolean"}:
            lower = raw.lower()
            if lower in {"1", "true", "yes"}:
                return "1"
            if lower in {"0", "false", "no"}:
                return "0"
            raise HTTPException(status_code=400, detail=f"Default for column '{col.name}' must be boolean (true/false)")
        # Date / time / datetime – keep as string, lightly validated
        if t in {"datetime", "timestamp", "date", "time"}:
            escaped = raw.replace("'", "''")
            return f"'{escaped}'"
        # Fallback (should not be reached because map_type guards types)
        escaped = raw.replace("'", "''")
        return f"'{escaped}'"

    for col in req.columns:
        col_name = col.name.strip()
        if not col_name or not identifier_re.match(col_name):
            raise HTTPException(status_code=400, detail=f"Invalid column name '{col.name}'. Use letters, numbers and '_' and do not start with a digit.")
        if col_name.lower() == "id":
            raise HTTPException(status_code=400, detail="Column name 'id' is reserved for the primary key")
        if col_name in seen_cols:
            raise HTTPException(status_code=400, detail=f"Duplicate column name '{col_name}'")
        seen_cols.add(col_name)

        sql_type = map_type(col)
        nullable_sql = " NULL" if col.nullable else " NOT NULL"
        unique_sql = " UNIQUE" if col.unique else ""
        default_sql = build_default_sql(col)
        default_clause = f" DEFAULT {default_sql}" if default_sql is not None else ""
        user_columns_sql.append(f"`{col_name}` {sql_type}{nullable_sql}{unique_sql}{default_clause}")

    conn = get_db_connection()
    try:
        cur = conn.cursor()
        # Check if table already exists
        cur.execute(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
            (table_name,),
        )
        if cur.fetchone():
            raise HTTPException(status_code=400, detail="Table already exists")

        # Build CREATE TABLE statement with an auto-increment primary key 'id'
        columns_sql = ["`id` INT AUTO_INCREMENT PRIMARY KEY"] + user_columns_sql
        create_sql = f"CREATE TABLE `{table_name}` (" + ", ".join(columns_sql) + ")"
        cur.execute(create_sql)
        conn.commit()

        # Audit log
        details: Dict[str, object] = {
            "columns": [
                {
                    "name": c.name,
                    "type": c.type,
                    "nullable": c.nullable,
                    "length": c.length,
                    "unique": c.unique,
                    "default": c.default,
                }
                for c in req.columns
            ]
        }
        write_audit_log(current_admin.username, "create_table", table_name, None, details)

        return {
            "table": table_name,
            "columns": [
                {"name": "id", "data_type": "INT", "is_nullable": False, "is_primary_key": True}
            ]
            + [
                {
                    "name": c.name,
                    "data_type": map_type(c),
                    "is_nullable": c.nullable,
                    "is_primary_key": False,
                    "unique": c.unique,
                    "default": c.default,
                }
                for c in req.columns
            ],
        }
    except mariadb.Error as e:
        conn.rollback()
        logger.exception("Error creating table '%s'", table_name)
        raise HTTPException(status_code=400, detail=f"Error creating table: {e}")
    finally:
        conn.close()


@router.get("/schema", response_model=List[TableSchema])
async def get_schema(current_admin: User = Depends(get_current_admin), table: Optional[str] = None):
    """Return schema information (tables and columns) for the current database."""
    conn = get_db_connection()
    try:
        cur = conn.cursor(dictionary=True)
        params: List[str] = []
        sql = (
            "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY "
            "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()"
        )
        if table:
            sql += " AND TABLE_NAME = %s"
            params.append(table)
        sql += " ORDER BY TABLE_NAME, ORDINAL_POSITION"
        cur.execute(sql, params)
        rows = cur.fetchall()
        schemas: Dict[str, List[ColumnInfo]] = {}
        for row in rows:
            tname = row["TABLE_NAME"]
            cols = schemas.setdefault(tname, [])
            cols.append(
                ColumnInfo(
                    name=row["COLUMN_NAME"],
                    data_type=row["DATA_TYPE"],
                    is_nullable=row["IS_NULLABLE"] == "YES",
                    is_primary_key=row.get("COLUMN_KEY") == "PRI",
                )
            )
        return [TableSchema(table=t, columns=c) for t, c in schemas.items()]
    except mariadb.Error as e:
        logger.exception("Error reading schema information")
        raise HTTPException(status_code=500, detail=f"Error reading schema: {e}")
    finally:
        conn.close()