UniSierra Eats SQLite Schema: Four Tables and Relationships

CREATE TABLE IF NOT EXISTS Roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT UNIQUE NOT NULL
);

INSERT OR IGNORE INTO Roles (id, nombre) VALUES (1, 'Administrador'), (2, 'Estudiante');

CREATE TABLE IF NOT EXISTS Usuarios (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    correo TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    rol_id INTEGER,
    FOREIGN KEY (rol_id) REFERENCES Roles (id)
);

INSERT OR IGNORE INTO Usuarios (id, nombre, correo, password, rol_id)
VALUES (1, 'Admin Principal', 'admin@unisierra.edu.mx', 'admin123', 1);

CREATE TABLE IF NOT EXISTS Productos (
    id_producto INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    precio REAL NOT NULL,
    precioNivel TEXT NOT NULL,
    descripcion TEXT,
    imagen TEXT,
    calificacion REAL DEFAULT 0.0,
    numResenas INTEGER DEFAULT 0,
    categoria TEXT NOT NULL
);

SELECT p.*,
       IFNULL(AVG(r.calificacion), 0) AS calificacion,
       COUNT(r.id) AS numResenas
FROM Productos p
LEFT JOIN Resenas r ON p.id_producto = r.producto_id
GROUP BY p.id_producto;

CREATE TABLE IF NOT EXISTS Resenas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    usuario_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    calificacion INTEGER NOT NULL CHECK(calificacion >= 1 AND calificacion <= 5),
    comentario TEXT,
    fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
    estado TEXT DEFAULT 'activa',
    FOREIGN KEY (usuario_id) REFERENCES Usuarios (id),
    FOREIGN KEY (producto_id) REFERENCES Productos (id_producto)
);