import sqlite3


def create_db():
    # Conexión a la base de datos
    conn = sqlite3.connect("site.db")
    cursor = conn.cursor()

    # Crear tablas
    cursor.execute(
        """
        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
        )
    """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS teacher (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            department TEXT NOT NULL
        )
    """
    )

    cursor.execute(
        """
        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)
        )
    """
    )

    conn.commit()
    conn.close()