-------- creacion de db-------------------

create table Alumno (
	idAlumno int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NomA varchar(35) NOT NULL,
	ApA varchar(35) NOT NULL,
	AmA varchar(35) NOT NULL,
	ConA varchar(35) NOT NULL,
	Matricula varchar(60) NOT NULL UNIQUE
);


create table Docente (
	idDocente int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NomD varchar(35) NOT NULL,
	ApD varchar(35) NOT NULL,
	AmD varchar(35) NOT NULL,
	Alias varchar(35) NOT NULL,
	ConD varchar(60)
);

create table Materia (
	idMateria int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NomM varchar(50) NOT NULL,
	Creditos int
);

create table Grupo (
	idAlumno int,
	idGrupo int
);

create table Avance (
	Fecha varchar(35) NOT NULL,
	idAlumno int NOT NULL,
	idReto int NOT NULL
);

create table DatosGrupo(
idGrupo int NOT NULL,
idMateria int NOT NULL,
idDocente int NOT NULL,
Grado int,
Grupo char(1),
Turno varchar(10)
);


create table Reto (
	idReto int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	NomR varchar(50) NOT NULL,
  Nivel varchar(10) NOT NULL,
	IdSticker int NOT NULL,
	Puntaje int NOT NULL,
	Duracion float,
	TipoReto varchar(10) NOT NULL
);


---------- creacion de procedimientos -------------


DELIMITER //
CREATE PROCEDURE proc_insert_avance (IN Fecha varchar(35), IN idAlumno int,IN idReto int, IN time varchar(10))
BEGIN
    INSERT INTO Avance (Fecha,idAlumno,idReto, tiempo) values (Fecha,idAlumno,idReto, time);
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_get_avance (IN alumno int, IN reto int)
BEGIN
	SELECT * FROM Avance WHERE idAlumno=alumno AND idReto=reto;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_insert_reto (IN idReto int, IN NomR varchar(50),IN Nivel varchar(10), IN IdSticker int, IN Puntaje int, IN Duracion float,  IN TipoReto varchar(10))
BEGIN
    INSERT INTO Reto (idReto,NomR,Nivel,IdSticker,Puntaje,Duracion, TipoReto) values (idReto,NomR,Nivel,IdSticker,Puntaje,Duracion, TipoReto);
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_insert_alumno (IN nombre varchar(35), IN apellidop varchar(35), IN apellidom varchar(35), IN contraseña varchar(35), IN matricula varchar(60))
BEGIN
    INSERT INTO Alumnos (NomA, ApA, AmA, ConA, Matricula) values (nombre , apellidop, apellidom, contraseña, matricula);
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_insert_docente (IN nombre varchar(35), IN apellidop varchar(35), IN apellidom varchar(35), IN als varchar(15),  IN contraseña varchar(65))
BEGIN
    INSERT INTO Docente (NomA, ApA, AmA, Alias, ConA) values (nombre , apellidop, apellidom, als, contraseña);
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_insert_grupo (IN materia varchar(15), IN alias varchar(35), IN grado int, IN grupo char(1), IN turno char(1))
BEGIN
    INSERT INTO DatosGrupo (Materia, idDocente, Grado, Grupo, Turno) values (materia, ( SELECT idDocente FROM Docente WHERE Alias = alias), grado, grupo, turno);
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_update_alumno (IN identificador int ,IN new_nombre varchar(35), IN new_apellidop varchar(35), IN new_apellidom varchar(35), IN new_contraseña varchar(35), IN new_matricula varchar(60))
BEGIN
    UPDATE Alumnos SET NomA = new_nombre , ApA = new_apellidop, AmA = new_apellidom , ConA = new_contraseña, Matricula = new_matricula WHERE idAlumno = identificador;
END

// DELIMITER ;

DELIMITER //
CREATE PROCEDURE proc_update_grupo (IN base varchar(15), IN new_nom varchar(15), IN alias varchar(10), IN gra int, IN gru char(1), IN turn char(1))
BEGIN
    UPDATE DatosGrupo SET Materia = new_nombre , idDocente = ( SELECT idDocente FROM Docente WHERE Alias = alias), Grado = gra , Grupo = gru, Turno = turn WHERE Materia = base;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_update_docente (IN base varchar(15), IN new_nom varchar(15), IN ap varchar(15), IN am varchar(15), IN pasw varchar(60), IN new_al varchar(15))
BEGIN
    UPDATE Docente SET NomD = new_nom, ApD = ap, AmD = am, ConD = pasw, Alias = new_al WHERE Alias = base;
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_delete_alumno (IN matricula varchar(10))
BEGIN
    DELETE FROM Alumno WHERE Matricula = matricula;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_unlock_sticker (IN alumno int)
BEGIN
	SELECT * FROM Avance WHERE idAlumno=alumno;
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_delete_grupo (IN mat varchar(15))
BEGIN
	DELETE FROM Grupo WHERE idGrupo = (SELECT idGrupo FROM DatosGrupo WHERE Materia = mat);
    DELETE FROM DatosGrupo WHERE Materia=mat;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_delete_docente (IN id int)
BEGIN
    DELETE FROM Docente WHERE idDocente = id;
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_select_alumno (IN in_matricula varchar(60))
BEGIN
    SELECT * FROM Alumnos WHERE Matricula = in_matricula;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_select_grupos ()
BEGIN
    SELECT * FROM DatosGrupo;
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_select_docentes ()
BEGIN
    SELECT * FROM Docente;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_ax_select_docentes (IN als varchar(20))
BEGIN
    SELECT * FROM Docente WHERE Alias=als;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE proc_select_all_alumno ()
BEGIN
    SELECT * FROM Alumnos;
END

// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_login_as_alumno (IN identificador VARCHAR(60), IN contraseña VARCHAR(35) )
BEGIN
    SELECT * FROM Alumnos WHERE Matricula = identificador AND ConA = contraseña;
END

// DELIMITER ;


DELIMITER //
CREATE PROCEDURE registrar_avance( IN fch varchar(35), IN idalm int, IN idrt int, IN tm float )
  BEGIN
    DECLARE ptn float;
    SELECT tiempo INTO ptn FROM Avance WHERE idAlumno = idalm AND idReto = idrt LIMIT 1;
    IF ptn IS NULL THEN
      INSERT INTO Avance  (Fecha, idAlumno, idReto, tiempo) values (fch, idalm, idrt, tm);
    ELSE
      IF (ptn < tm) THEN
        UPDATE Avance  SET tiempo = tm, Fecha = fch WHERE idAlumno = idalm AND idReto = idrt LIMIT 1;
      END IF;
    END IF;
  END
// DELIMITER ;



DELIMITER //
CREATE PROCEDURE proc_login_as_docente (IN identificador VARCHAR(60), IN contraseña VARCHAR(35) )
BEGIN
    SELECT * FROM Docentes WHERE Alias = identificador AND ConD = contraseña;
END

// DELIMITER ;


---------- Llamadas a procedimientos ----------
CALL proc_insert_avance('1','1','10-10-2301');

CALL proc_insert_alumno('Jesus Said','Llamas','Manriquez','1234','M1529092');

CALL proc_update_alumno (1, 'JESUS SAID','LLAMAS', 'MANRIQUEZ', 'NEWPASW', 'N1234');

CALL proc_delete_alumno (1);

CALL proc_select_all_alumno();
CALL proc_select_alumno('M1529092')

CALL proc_login_as_alumno('M15290902', '1234')
CALL proc_login_as_docente('mas13', '123')