--Proyecto Final
--Sistema de Gestión para Gimnasio
--Estudiante:Gloriana Barrantes Rojas

--Creación de Tablas
CREATE TABLE ROLES (
    id_rol INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    descripcion VARCHAR(255)
);

CREATE TABLE USUARIOS (
    id_usuario INT PRIMARY KEY,
    id_rol INT NOT NULL,
    nombre_usuario VARCHAR(50) NOT NULL,
    correo VARCHAR(100) NOT NULL,
    contraseña VARCHAR(255) NOT NULL,
    FOREIGN KEY (id_rol) REFERENCES ROLES(id_rol)
);

CREATE TABLE MEMBRESIAS (
    id_membresia INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    duracion INT NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    descripcion VARCHAR(255)
);

CREATE TABLE CLIENTES (
    id_cliente INT PRIMARY KEY,
    id_membresia INT NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    telefono VARCHAR(20),
    correo VARCHAR(100) NOT NULL,
    fecha_registro DATE NOT NULL,
    FOREIGN KEY (id_membresia) REFERENCES MEMBRESIAS(id_membresia)
);

CREATE TABLE ENTRENADORES (
    id_entrenador INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    especialidad VARCHAR(100),
    telefono VARCHAR(20),
    correo VARCHAR(100) NOT NULL
);

CREATE TABLE CLASES (
    id_clase INT PRIMARY KEY,
    id_entrenador INT NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    horario TIME NOT NULL,
    capacidad INT NOT NULL,
    FOREIGN KEY (id_entrenador) REFERENCES ENTRENADORES(id_entrenador)
);

CREATE TABLE PAGOS (
    id_pago INT PRIMARY KEY,
    id_cliente INT NOT NULL,
    monto DECIMAL(10,2) NOT NULL,
    fecha_pago DATE NOT NULL,
    metodo_pago VARCHAR(50) NOT NULL,
    estado VARCHAR(20) NOT NULL,
    FOREIGN KEY (id_cliente) REFERENCES CLIENTES(id_cliente)
);

CREATE TABLE INSCRIPCIONES (
    id_inscripcion INT PRIMARY KEY,
    id_cliente INT NOT NULL,
    id_clase INT NOT NULL,
    fecha_inscripcion DATE NOT NULL,
    FOREIGN KEY (id_cliente) REFERENCES CLIENTES(id_cliente),
    FOREIGN KEY (id_clase) REFERENCES CLASES(id_clase)
);

--Inserts de cada tabla
--ROLES
INSERT INTO ROLES (id_rol, nombre, descripcion) VALUES
    (1, 'Administrador', 'Control total del sistema'),
    (2, 'Recepcionista', 'Gestiona clientes y pagos'),
    (3, 'Entrenador', 'Imparte clases'),
    (4, 'Cliente', 'Consulta información'),
    (5, 'Supervisor', 'Supervisa operaciones'),
    (6, 'Gerente', 'Administra el gimnasio'),
    (7, 'Asistente', 'Apoya en recepción'),
    (8, 'Contador', 'Gestiona finanzas'),
    (9, 'Instructor', 'Dirige actividades'),
    (10, 'Invitado', 'Acceso limitado');

--MEMBRESIAS
INSERT INTO MEMBRESIAS (id_membresia, nombre, duracion, precio, descripcion) VALUES
    (1,'Básica',30,25000,'Acceso al gimnasio'),
    (2,'Premium',30,35000,'Incluye clases'),
    (3,'VIP',30,45000,'Acceso completo'),
    (4,'Trimestral',90,70000,'Tres meses'),
    (5,'Semestral',180,130000,'Seis meses'),
    (6,'Anual',365,240000,'Un año'),
    (7,'Estudiante',30,18000,'Descuento'),
    (8,'Adulto Mayor',30,15000,'Tarifa especial'),
    (9,'Corporativa',30,22000,'Convenio empresa'),
    (10,'Familiar',30,50000,'Hasta cuatro personas');

--ENTRENADORES
INSERT INTO ENTRENADORES (id_entrenador, nombre, apellido, especialidad, telefono, correo) VALUES
    (1,'Carlos','Ramírez','Pesas','88880001','carlos@gym.com'),
    (2,'Laura','Jiménez','Yoga','88880002','laura@gym.com'),
    (3,'José','Vargas','CrossFit','88880003','jose@gym.com'),
    (4,'Ana','Solano','Pilates','88880004','ana@gym.com'),
    (5,'David','Mora','Cardio','88880005','david@gym.com'),
    (6,'María','Rojas','Spinning','88880006','maria@gym.com'),
    (7,'Luis','Arias','Natación','88880007','luis@gym.com'),
    (8,'Andrea','Castro','Zumba','88880008','andrea@gym.com'),
    (9,'Pablo','Soto','Funcional','88880009','pablo@gym.com'),
    (10,'Sofía','León','Boxeo','88880010','sofia@gym.com');

--USUARIOS
INSERT INTO USUARIOS (id_usuario,id_rol,nombre_usuario,correo,contraseña) VALUES
    (1,1,'admin','admin@gym.com','admin123'),
    (2,2,'recepcion1','recepcion@gym.com','rece123'),
    (3,3,'trainer1','trainer1@gym.com','train123'),
    (4,3,'trainer2','trainer2@gym.com','train123'),
    (5,4,'cliente1','cliente1@gmail.com','cliente123'),
    (6,4,'cliente2','cliente2@gmail.com','cliente123'),
    (7,5,'supervisor','super@gym.com','super123'),
    (8,6,'gerente','gerente@gym.com','ger123'),
    (9,8,'contador','conta@gym.com','cont123'),
    (10,7,'asistente','asis@gym.com','asis123');

--CLIENTES
INSERT INTO CLIENTES (id_cliente,id_membresia,nombre,apellido,telefono,correo,fecha_registro) VALUES
    (1,2,'Gloriana','Barrantes','88881111','glori@gmail.com','2026-01-10'),
    (2,1,'Juan','Pérez','88882222','juan@gmail.com','2026-01-12'),
    (3,3,'María','López','88883333','maria@gmail.com','2026-01-15'),
    (4,4,'Pedro','Gómez','88884444','pedro@gmail.com','2026-02-01'),
    (5,5,'Ana','Vargas','88885555','ana@gmail.com','2026-02-05'),
    (6,6,'Luis','Mora','88886666','luis@gmail.com','2026-02-12'),
    (7,7,'Carla','Rojas','88887777','carla@gmail.com','2026-02-18'),
    (8,8,'Diego','Campos','88888888','diego@gmail.com','2026-03-01'),
    (9,9,'Sofía','Castro','88889999','sofia@gmail.com','2026-03-05'),
    (10,10,'Andrés','León','88890000','andres@gmail.com','2026-03-10');

--CLASES
INSERT INTO CLASES (id_clase,id_entrenador,nombre,horario,capacidad) VALUES
    (1,1,'Pesas','08:00:00',20),
    (2,2,'Yoga','09:00:00',15),
    (3,3,'CrossFit','10:00:00',18),
    (4,4,'Pilates','11:00:00',12),
    (5,5,'Cardio','12:00:00',25),
    (6,6,'Spinning','13:00:00',20),
    (7,7,'Natación','14:00:00',10),
    (8,8,'Zumba','15:00:00',30),
    (9,9,'Funcional','16:00:00',18),
    (10,10,'Boxeo','17:00:00',15);

--PAGOS
INSERT INTO PAGOS (id_pago,id_cliente,monto,fecha_pago,metodo_pago,estado) VALUES
    (1,1,35000,'2026-01-10','Tarjeta','Pagado'),
    (2,2,25000,'2026-01-12','Efectivo','Pagado'),
    (3,3,45000,'2026-01-15','SINPE','Pagado'),
    (4,4,70000,'2026-02-01','Tarjeta','Pagado'),
    (5,5,130000,'2026-02-05','Transferencia','Pagado'),
    (6,6,240000,'2026-02-12','Tarjeta','Pagado'),
    (7,7,18000,'2026-02-18','SINPE','Pendiente'),
    (8,8,15000,'2026-03-01','Efectivo','Pagado'),
    (9,9,22000,'2026-03-05','Tarjeta','Pagado'),
    (10,10,50000,'2026-03-10','Transferencia','Pagado');

--INSCRIPCIONES
INSERT INTO INSCRIPCIONES (id_inscripcion,id_cliente,id_clase,fecha_inscripcion) VALUES
    (1,1,2,'2026-01-11'),
    (2,2,1,'2026-01-13'),
    (3,3,3,'2026-01-16'),
    (4,4,4,'2026-02-02'),
    (5,5,5,'2026-02-06'),
    (6,6,6,'2026-02-13'),
    (7,7,7,'2026-02-19'),
    (8,8,8,'2026-03-02'),
    (9,9,9,'2026-03-06'),
    (10,10,10,'2026-03-11');

--Consultas
SELECT ' ';
SELECT '1. SELECT: mostrar todos los clientes';
SELECT * FROM CLIENTES;

SELECT ' ';
SELECT '2. WHERE: clientes con membresía Premium'; 
SELECT * FROM CLIENTES
WHERE id_membresia = 2;

SELECT ' ';
SELECT '3. ORDER BY: ordenar membresías por precio de mayor a menor';
SELECT * FROM MEMBRESIAS
ORDER BY precio DESC;

SELECT ' ';
SELECT '4. GROUP BY: cantidad de clientes por membresía';
SELECT id_membresia, COUNT(*) AS cantidad_clientes
FROM CLIENTES
GROUP BY id_membresia;

SELECT ' ';
SELECT 'JOIN: clientes con el nombre de su membresía';
SELECT c.nombre, c.apellido, m.nombre AS membresia
FROM CLIENTES c
JOIN MEMBRESIAS m ON c.id_membresia = m.id_membresia;

SELECT ' ';
SELECT '6. JOIN: clases con su entrenador';
SELECT cl.nombre AS clase, e.nombre AS entrenador, e.apellido
FROM CLASES cl
JOIN ENTRENADORES e ON cl.id_entrenador = e.id_entrenador;

SELECT ' ';
SELECT '7. COUNT: cantidad total de clientes';
SELECT COUNT(*) AS total_clientes
FROM CLIENTES;

SELECT ' ';
SELECT '8. SUM: total de pagos realizados';
SELECT SUM(monto) AS total_pagos
FROM PAGOS
WHERE estado = 'Pagado';

SELECT ' ';
SELECT '9. AVG: promedio del precio de las membresías';
SELECT AVG(precio) AS promedio_precio
FROM MEMBRESIAS;

SELECT ' ';
SELECT '10. MAX y MIN: membresía más cara y más barata';
SELECT MAX(precio) AS precio_mayor,
       MIN(precio) AS precio_menor
FROM MEMBRESIAS;

--BONUS: VIEW
SELECT ' ';
SELECT 'BONUS: VIEW';
CREATE VIEW vista_clientes_membresias AS
SELECT 
    c.id_cliente,
    c.nombre,
    c.apellido,
    c.correo,
    m.nombre AS membresia,
    m.precio
FROM CLIENTES c
JOIN MEMBRESIAS m ON c.id_membresia = m.id_membresia;

SELECT * FROM vista_clientes_membresias;