CREATE DATABASE practica01

GO
USE practica01
CREATE  ROLE Administradores
CREATE  ROLE Instructores
CREATE  ROLE Alumnos
GO

CREATE TABLE usuarios (

usuarioID       SMALLINT IDENTITY PRIMARY KEY,
username        VARCHAR(32) NOT NULL,
passw           CHAR(64),
foto            VARCHAR(32),
estado          BIT NOT NULL DEFAULT 1,

CONSTRAINT      UQ_username         UNIQUE(username),
CONSTRAINT      CH_usernameLEN_min  CHECK (LEN(username)>7),
CONSTRAINT      CH_usernameLEN_max  CHECK (LEN(username)<33),
CONSTRAINT      CH_passwLEN         CHECK (LEN(passw)   >7) --simepre la longitud es 64, es necesaria?


)


CREATE TABLE alumnos(
alumnoID        SMALLINT IDENTITY PRIMARY KEY,
usuarioID       SMALLINT,
nombre          VARCHAR(32) NOT NULL,
apellido        VARCHAR(32) NOT NULL,
fechaNacimiento DATE,
telefono1       VARCHAR(16),
telefono2       VARCHAR(16),
direccion       VARCHAR(64),
email           VARCHAR(64),
sexo            CHAR NOT NULL ,

CONSTRAINT FK_alumnos_usuarioID FOREIGN KEY(usuarioID) REFERENCES usuarios(usuarioID),
CONSTRAINT CH_alumnos_sexo      CHECK(sexo IN('M','F'))

)

CREATE TABLE instructores(
instructorID    TINYINT IDENTITY PRIMARY KEY,
usuarioID       SMALLINT,
nombre          VARCHAR(64) NOT NULL,
CUIT            VARCHAR(16) NOT NULL,
telefono        VARCHAR(16),
montoAcumulado  DECIMAL(6,2),

CONSTRAINT      FK_instructores_usuarioID   FOREIGN KEY (usuarioID) REFERENCES usuarios(usuarioID)
)

CREATE TABLE administradores(
administradorID TINYINT IDENTITY PRIMARY KEY,
usuarioID       SMALLINT,
nombre          VARCHAR(64) NOT NULL,
email           VARCHAR(64),

CONSTRAINT      FK_administradores_usuarioID    FOREIGN KEY (usuarioID) REFERENCES usuarios(usuarioID)

)

GO

CREATE PROCEDURE sp_administradores_insert
@username   VARCHAR(32),
@passw      VARCHAR(64),
@foto       VARCHAR(32),
@nombre     VARCHAR(64),
@email      VARCHAR(64)
AS
DECLARE @userID SMALLINT, @msg VARCHAR(256)

BEGIN TRY
    IF LEN(@passw)>=8
        BEGIN
            BEGIN TRANSACTION
                SET     @passw= HASHBYTES('SHA2_256',@passw)
                INSERT INTO usuarios (username, passw,foto) VALUES (@username,@passw,@foto)
                SET @userID= @@IDENTITY
                INSERT  INTO administradores (usuarioID, nombre,email) VALUES (@userID, @nombre,@email )


            COMMIT TRANSACTION
        END
    ELSE
        BEGIN

            SET     @msg= 'La longitud del password debe superar los 7 caracteres' ;
            THROW 60007, @msg , 16
        END
END TRY
BEGIN CATCH
    SET     @msg= ERROR_MESSAGE() ;
    THROW 60008, @msg , 16
    ROLLBACK TRANSACTION
END CATCH


GO

CREATE TRIGGER createLoginForUser ON usuarios AFTER INSERT
AS
DECLARE @username VARCHAR(32), @passw VARCHAR(64)
DECLARE @SQLString NVARCHAR(500), @ParmDefinition NVARCHAR(500);
--SENTENCIA DINAMICA
--Definicion sentencia
SET @SQLString =N'  CREATE LOGIN ['+@username+'] WITH PASSWORD= '+ @passw+ ' HASHED MUST_CHANGE  ,CHECK_EXPIRATION =  ON ,CHECK_POLICY  ON  , DEFAULT_DATABASE=[practica01];
                    CREATE USER ['+@username+'] FOR LOGIN ['+@username+'];
                    ALTER ROLE administradores ADD MEMBER ['+@username+'];';
--Definicion parametros
SET @ParmDefinition = N'@username VARCHAR(32), @passw VARCHAR(64)';
-- seteo de parametros
SELECT @username =username, @passw=passw FROM inserted
-- Ejecucion de sentencia

EXECUTE SP_EXECUTESQL   @SQLString, @ParmDefinition,
                        @username, @passw;


--no siempre asignare el usuario a administradores, es para poder probar

GO