routers/review.py

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

from models import Review

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

"""
        CREATE TABLE IF NOT EXISTS review (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            rating INTEGER NOT NULL,
            date_posted DATETIME NOT NULL DEFAULT (datetime('now')),
            teacher_id INTEGER NOT NULL,
            user_id INTEGER,
            FOREIGN KEY (teacher_id) REFERENCES teacher(id),
            FOREIGN KEY (user_id) REFERENCES user(id)
        )
    """


@router.get("/")
def get_reviews():
    """
    Get all reviews
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM review")
        reviews = cursor.fetchall()
        conn.close()
        return reviews
    except Exception as e:
        return []


@router.get("/{review_id}")
def get_review(review_id: int):
    """
    Get a review by id
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM review WHERE id=?", (review_id,))
        review = cursor.fetchone()
        conn.close()
        # retornar en formato json
        review = Review(
            id=review[0],
            content=review[1],
            rating=review[2],
            date_posted=review[3],
            teacher_id=review[4],
            user_id=review[5],
        )
        return review
    except Exception as e:
        return []


class ReviewPost(BaseModel):
    content: str
    rating: int
    date_posted: datetime = Field(default_factory=datetime.now)
    teacher_id: int


@router.post("/")
def create_review(r: ReviewPost):
    """
    Create a review
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO review (content, rating, teacher_id, user_id) VALUES (?, ?, ?, ?)",
            (r.content, r.rating, r.teacher_id, 1),
        )
        conn.commit()
        conn.close()
        return {"message": "Review created"}
    except Exception as e:
        return {"message": "Error creating review"}


@router.get("/search_review/{teacher_id}")
def get_reviews_by_teacher(teacher_id: int):
    """
    Get all reviews for a teacher
    """
    try:
        conn = sqlite3.connect("site.db")
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM review WHERE teacher_id=?", (teacher_id,))
        reviews = cursor.fetchall()
        conn.close()
        return reviews
    except Exception as e:
        return []