CREATE TYPE tdiasemana AS ENUM
('lunes', 'martes', 'miercoles', 'jueves', 'viernes', 'sabado','domingo');

 CREATE TYPE thorario AS (
 diasem tdiasemana,
 horaini TIME
 );
 
 CREATE TABLE monitor(
 id SERIAL,
 nombrem VARCHAR(60) NOT NULL,
 telef CHAR(14),
 emails VARCHAR(50)[],
 PRIMARY KEY(id)
 );
 
 CREATE TABLE clase(
 id SERIAL,
 nombrec VARCHAR(50) NOT NULL,
 monitor_id INTEGER NOT NULL REFERENCES monitor(id),
 horario thorario[],
 PRIMARY KEY(id)
 );
 
 CREATE TABLE claseonline(
 appvc VARCHAR(50) NOT NULL,
 conexion VARCHAR(60)
 )INHERITS (clase);

INSERT INTO monitor VALUES(DEFAULT,'Eduardo Cano','610364579',
'{"edumonit@hotmail.com","eduprof@gmail.com"}');

INSERT INTO monitor VALUES(DEFAULT,'Mariló Hurtado','650382772',
'{"mlprof@yahoo.com","mlmon@gmail.com"}');

INSERT INTO clase VALUES(DEFAULT, 'pilates',1, ARRAY[ROW('lunes','8:00'),ROW('viernes','8:00')]::thorario[]
);
INSERT INTO clase VALUES(DEFAULT,'spinning',1, 
ARRAY[ROW('lunes','10:00'), ROW('viernes','10:00')]::thorario[]
);
INSERT INTO clase VALUES(DEFAULT,'yoga',1, 
ARRAY[ROW('lunes','12:00'), ROW('viernes','12:00')]::thorario[] );

INSERT INTO claseonline VALUES(DEFAULT,'step',2, 
ARRAY[ROW('martes','8:00'), ROW('jueves','8:00')]::thorario[],'Zoom','r1'
);
INSERT INTO claseonline VALUES(DEFAULT,'zumba',2, 
ARRAY[ROW('martes','10:00'), ROW('jueves','10:00')]::thorario[],'Meet','r2'
);
INSERT INTO claseonline VALUES(DEFAULT,'GAC',2, 
ARRAY[ROW('martes','12:00'), ROW('jueves','12:00')]::thorario[],'Teams','r3'
);

/*
 INSERT INTO claseonline VALUES
 (1,'boxeo', 2, ARRAY[ROW('martes','17:00'), ROW('jueves','17:00')] ::thorario[], 'Zoom','r4'); 

 INSERT INTO claseonline VALUES
 (DEFAULT,'baile',3,ARRAY[ROW('martes','19:00'),ROW('jueves','21:00')] ::thorario[], 'Meet','r5');
*/

CREATE OR REPLACE FUNCTION ChkIntegridClaseOnline() RETURNS TRIGGER AS $$ 
BEGIN 
IF EXISTS (SELECT * FROM clase WHERE id=NEW.id) THEN
 RAISE NOTICE 'Operación rechazada';
 RAISE NOTICE 'Duplicidad de clases: ya existe una clase con el identificador %',NEW.id;
 RETURN NULL;
END IF;
IF NOT EXISTS (SELECT * FROM monitor WHERE id=NEW.monitor_id) THEN
 RAISE NOTICE 'Operación rechazada';
 RAISE NOTICE 'Monitor inexistente: se debe dar de alta previamente al monitor %',NEW.monitor_id;
 RETURN NULL;
END IF;
RAISE NOTICE 'La operación de % ha sido aceptada',TG_OP;
RETURN NEW;
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER ChkIntegridClaseOnline BEFORE INSERT OR UPDATE ON claseonline 
FOR EACH ROW EXECUTE PROCEDURE ChkIntegridClaseOnline();