import sqlite3
from typing import Optional
from fastapi import APIRouter
from pydantic import BaseModel, Field

from models import User

router = APIRouter(
    prefix="/user",
    tags=["User"],
    responses={404: {"description": "Not found"}},
)

"""
        CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL
        )
    """


@router.get("/")
def get_users():
    """
    Get all users
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM user")
        users = cursor.fetchall()
        conn.close()
        users = [User(id=u[0], username=u[1], email=u[2], password=u[3]) for u in users]
        return users
    except Exception as e:
        return []


@router.get("/{user_id}")
def get_user(user_id: int):
    """
    Get a user by id
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM user WHERE id=?", (user_id,))
        user = cursor.fetchone()
        conn.close()
        user = User(id=user[0], username=user[1], email=user[2], password=user[3])
        return user
    except Exception as e:
        return []


class UserPost(BaseModel):
    id: Optional[int] = None
    username: str = Field(..., max_length=20)
    email: str = Field(..., max_length=120)
    password: str = Field(..., max_length=60)


@router.post("/")
def create_user(u: User):
    """
    Create a user
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO user (username, email, password) VALUES (?, ?, ?)",
            (u.username, u.email, u.password),
        )
        conn.commit()
        conn.close()
        return {"message": "User created"}
    except Exception as e:
        return {"message": "Error"}