CREATE DATABASE PRUEBA_IAS;

USE PRUEBA_IAS;

CREATE TABLE TECNICO(
CC_TECNICO INT NOT NULL PRIMARY KEY
-- NOMBRE VARCHAR(30)
);

CREATE TABLE TIPO_SERVICIO(
ID_TIPO_SERVICIO INT AUTO_INCREMENT PRIMARY KEY,
TIPO VARCHAR(15)
);

CREATE TABLE SERVICIO(
ID_SERVICIO INT AUTO_INCREMENT PRIMARY KEY,
ID_TIPO_SERVICIO INT NOT NULL,
FOREIGN KEY (ID_TIPO_SERVICIO) REFERENCES TIPO_SERVICIO(ID_TIPO_SERVICIO)
);

CREATE TABLE SEMANA(
ID_SEMANA INT AUTO_INCREMENT PRIMARY KEY,
NUMERO_SEMANA INT NOT NULL
);

CREATE TABLE TECNICO_SERVICIO(
ID_TECNICO_SERVICIO INT AUTO_INCREMENT PRIMARY KEY,
ID_TECNICO INT NOT NULL,
ID_SERVICIO INT NOT NULL,
ID_SEMANA INT NOT NULL,
CONSTRAINT FK_TECNICO_SERVICIO_TECNICO FOREIGN KEY (ID_TECNICO) REFERENCES TECNICO(CC_TECNICO),
CONSTRAINT FK_SERVICIO_TIPO_SERVICIO FOREIGN KEY (ID_SERVICIO) REFERENCES SERVICIO(ID_SERVICIO),
CONSTRAINT FK_ID_SEMANA_TIPO_SERVICIO FOREIGN KEY (ID_SEMANA) REFERENCES SEMANA(ID_SEMANA)
);

CREATE TABLE DIA(
ID_DIA INT AUTO_INCREMENT PRIMARY KEY,
DIA_SEMANA INT NOT NULL,
CONSTRAINT FK_DIA_SEMANA FOREIGN KEY (ID_DIA) REFERENCES SEMANA(ID_SEMANA)
);

CREATE TABLE HORA_ATENCION(
ID_HORA_ATENCION INT AUTO_INCREMENT PRIMARY KEY,
HORA_INICIO DATETIME NOT NULL,
HORA_FIN DATETIME NOT NULL,
HORA_NOCTURNA INT NOT NULL DEFAULT 0,
HORA_SABATINA INT NOT NULL DEFAULT 0,
HORA_DOMINICAL INT NOT NULL DEFAULT 0,
HORA_EXTRA INT NOT NULL DEFAULT 0,
HORA_EXTRA_NOCTURNA INT NOT NULL DEFAULT 0,
HORA_EXTRA_SABATINA INT NOT NULL DEFAULT 0,
HORA_EXTRA_DOMINICAL INT NOT NULL DEFAULT 0,
TOTAL_HORAS INT NOT NULL,
CONSTRAINT FK_HORA_DIA FOREIGN KEY (ID_HORA_ATENCION) REFERENCES DIA(ID_DIA)
);

INSERT INTO TECNICO VALUES 
(1223212),
(3323212),
(1022325),
(2122002);

INSERT INTO TIPO_SERVICIO VALUES
(1,'CORRIENTE'),
(2,'EMERGENCIA');

INSERT INTO SEMANA VALUES
(1,20),
(2,21),
(3,20),
(4,19);

INSERT INTO DIA VALUES
(1,1),
(2,1),
(3,5),
(4,4);

INSERT INTO HORA_ATENCION VALUES
(1,'2020/09/14 11:00:00','2020/09/14 12:00:00',0,0,0,0,0,0,0,1),
(2,'2020/09/14 14:00:00','2020/09/14 15:00:00',0,0,0,0,0,0,0,1),
(3,'2020/09/14 16:00:00','2020/09/14 17:00:00',0,0,0,0,0,0,0,1),
(4,'2020/09/14 18:00:00','2020/09/14 19:00:00',0,0,0,0,0,0,0,1);

INSERT INTO SERVICIO VALUES
(1,1),
(2,1),
(3,1),
(4,2);

INSERT INTO TECNICO_SERVICIO VALUES
(1,1223212,1,1),
(2,3323212,2,2),
(3,1022325,3,3),
(4,2122002,3,4);

-- PERMISOS DE ACCESO
USE PRUEBA_IAS;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

-- INSERTAR SERVICIO
USE PRUEBA_IAS;
BEGIN;
INSERT INTO SERVICIO (ID_TIPO_SERVICIO) VALUES
(1);
INSERT INTO SEMANA (NUMERO_SEMANA) VALUES 
(62);
INSERT INTO DIA (DIA_SEMANA) VALUES
(3);
INSERT INTO HORA_ATENCION  
(HORA_INICIO, HORA_FIN,HORA_NOCTURNA,HORA_SABATINA,HORA_DOMINICAL,HORA_EXTRA,HORA_EXTRA_NOCTURNA,HORA_EXTRA_SABATINA,HORA_EXTRA_DOMINICAL,TOTAL_HORAS)
 VALUES
('2020/09/15 11:00:00','2020/09/16 18:00:00',0,0,0,0,0,0,0,1);
INSERT INTO TECNICO_SERVICIO (ID_TECNICO, ID_SERVICIO, ID_SEMANA) VALUES
(1223212,
(SELECT MAX(ID_SERVICIO)  FROM SERVICIO),
(SELECT MAX(ID_SEMANA)  FROM SEMANA)
);
COMMIT;

-- CONSULTAR POR SEMANA Y CC_TECNICO
USE PRUEBA_IAS;
SELECT TS.ID_TECNICO, S.NUMERO_SEMANA, SUM(HA.HORA_EXTRA) as HORA_EXTRA, SUM(HA.HORA_SABATINA) as HORA_SABATINA, 
SUM(HA.HORA_DOMINICAL) as HORA_DOMINICAL, SUM(HA.HORA_EXTRA_NOCTURNA) as HORA_EXTRA_NOCTURNA, 
SUM(HA.HORA_EXTRA_SABATINA) as HORA_EXTRA_SABATINA, SUM(HA.TOTAL_HORAS) as TOTAL_HORAS
FROM 
HORA_ATENCION HA 
INNER JOIN DIA D ON HA.ID_HORA_ATENCION = D.ID_DIA
INNER JOIN SEMANA S ON D.ID_DIA = S.ID_SEMANA
INNER JOIN TECNICO_SERVICIO TS ON S.ID_SEMANA = TS.ID_SEMANA
WHERE ID_TECNICO = 3323212 AND NUMERO_SEMANA = 21

-- TOTAL HORAS POR SEMANA
USE PRUEBA_IAS;
SELECT SUM(HA.TOTAL_HORAS) AS TOTAL_HORAS, S.NUMERO_SEMANA, TS.ID_TECNICO
FROM 
HORA_ATENCION HA 
LEFT JOIN DIA D ON HA.ID_HORA_ATENCION = D.ID_DIA
LEFT JOIN SEMANA S ON D.ID_DIA = S.ID_SEMANA
LEFT JOIN TECNICO_SERVICIO TS ON S.ID_SEMANA = TS.ID_SEMANA
WHERE ID_TECNICO = 1223212 AND NUMERO_SEMANA = 38;

-- 