--CREATE TABLES
CREATE DATABASE RAPIDA

USE RAPIDA

CREATE TABLE AEROLINEA(
	CVE_AERO VARCHAR(1) PRIMARY KEY,
	NOMBRE VARCHAR(20)
)
CREATE TABLE VUELOS(
	CVE_VUELO VARCHAR(3) PRIMARY KEY,
	CVE_AERO VARCHAR(1) FOREIGN KEY REFERENCES AEROLINEA(CVE_AERO),
	HORARIO VARCHAR(5),
	CAPACIDAD INT,
	NO_CLIENTES INT
)
CREATE TABLE CLIENTES(
	CVE_CLIEN VARCHAR(3) PRIMARY KEY,
	NOMBRE VARCHAR(20),
	TELEFONO VARCHAR(10)
)
CREATE TABLE RESERVACIONES(
	CVE_VUELO VARCHAR(3) FOREIGN KEY REFERENCES VUELOS(CVE_VUELO),
	CVE_CLIEN VARCHAR(3) FOREIGN KEY REFERENCES CLIENTES(CVE_CLIEN)
)


--UNIDAD 3
--Crear logins
CREATE LOGIN USUARIO1
WITH PASSWORD = '123', DEFAULT_DATABASE=RAPIDA;
CREATE LOGIN USUARIO2
WITH PASSWORD = '123', DEFAULT_DATABASE=RAPIDA;

--Crear usuarios
--Nombre de usuario y el nombre del login pueden diferir
CREATE USER USUARIO1 FOR LOGIN USUARIO1;
CREATE USER USUARIO2 FOR LOGIN USUARIO2;

--Crear roles
CREATE ROLE Programador
GRANT SELECT ON CLIENTES TO Programador
GRANT UPDATE ON AEROLINEA TO Programador

--Asignar roles a usuarios
sp_addrolemember Programador, USUARIO1

--Asignar privilegios a usuarios
GRANT SELECT ON AEROLINEA TO USUARIO2
GRANT UPDATE ON CLIENTES TO USUARIO2
GRANT INSERT ON VUELOS TO USUARIO2

--UNIDAD 5
CREATE TABLE RESERVA_RES(
	CVE_VUELO VARCHAR(3) FOREIGN KEY REFERENCES VUELOS(CVE_VUELO),
	CVE_CLIEN VARCHAR(3) FOREIGN KEY REFERENCES CLIENTES(CVE_CLIEN),
	FECHA DATE,
	USUARIO VARCHAR(30),
	TERMINAL VARCHAR(30)
)

--Creacion de trigger para bitacora
CREATE TRIGGER BITACORA_RESERVA
ON RESERVACIONES
FOR INSERT
AS
BEGIN
INSERT INTO
RESERVA_RES(CVE_CLIEN,CVE_VUELO,FECHA,USUARIO,TERMINAL)
SELECT
CVE_CLIEN,CVE_VUELO,getdate() ,system_user,host_name() FROM INSERTED
END;

--Creacion de funcion mayus
CREATE SCHEMA bdo

CREATE FUNCTION bdo.MAYUS(@PALABRA VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
	SET @PALABRA=UPPER(@PALABRA)
	RETURN @PALABRA
END

--Creacion de procedimiento almacenado inserción
CREATE PROCEDURE INSERTAR_CLIENTES
@CVE_CLIEN VARCHAR(3),
@NOMBRE VARCHAR(20),
@TELEFONO VARCHAR(10)
AS
BEGIN
INSERT INTO CLIENTES
(CVE_CLIEN,NOMBRE,TELEFONO) VALUES
(@CVE_CLIEN,bdo.MAYUS(@NOMBRE),@TELEFONO)
END;

--Llenado con el procedimiento de inserción
EXEC INSERTAR_CLIENTES 902,'pedro',9999999999
EXEC INSERTAR_CLIENTES 950,'luis',9999999999
EXEC INSERTAR_CLIENTES 951,'mario',9999999999
EXEC INSERTAR_CLIENTES 952,'armida',9999999999
EXEC INSERTAR_CLIENTES 953,'valentina',9999999999
EXEC INSERTAR_CLIENTES 954,'oscar',9999999999
EXEC INSERTAR_CLIENTES 955,'francisco',9999999999

SELECT * FROM CLIENTES