﻿CREATE DOMAIN EPOCH_NOT_NORM2 AS DECIMAL (5, 2);

ALTER DOMAIN EPOCH_NOT_NORM2 RENAME TO EPOCH_NOT_NORM;

CREATE DOMAIN RAD_EPOCH_TYPE FLOAT (20) CHECK (VALUE BETWEEN 0E0 AND 2E0 * 3.1416E0);
commit;

CREATE DOMAIN RAD_EPOCH_NOT_NORM REAL;
CREATE DOMAIN TIDEDATE AS DATE CHECK (VALUE BETWEEN DATE '1994-01-01' AND DATE '2025-12-31');

CREATE DOMAIN TIDETIMESTAMP AS TIMESTAMP WITH TIME ZONE
 CHECK (VALUE BETWEEN TIMESTAMP( '1994-01-01 00:00:00+00:00')
 AND TIMESTAMP( '2025-12-31 23:59:59+00:00'));
 
commit;

CREATE DOMAIN DINNERTIME AS TIME  DEFAULT TIME '18:30:00'
 CONSTRAINT constr1 CHECK (VALUE BETWEEN TIME '17:30:00' AND TIME '19:00:00');
 
commit;

CREATE TABLE CONST_NOT_NORM (
 LOC_ID DECIMAL (7) NOT NULL,
 CONST_ID int NOT NULL,
 UNIQUE (LOC_ID, CONST_ID),
 AMPLITUDE DECIMAL,
 EPOCH EPOCH_NOT_NORM);
 
commit;


CREATE VIEW CONST_RAD_NOT_NORM (LOC_ID, CONST_ID,
 AMPLITUDE, EPOCH) AS
 SELECT LOC_ID, CONST_ID, AMPLITUDE,
 CAST ((EPOCH * 3.14159265358979E0 / 180E0) AS RAD_EPOCH_NOT_NORM)
 FROM CONST_NOT_NORM;

commit;

CREATE TABLE PENDING (
  LOC_ID DECIMAL (7) NOT NULL,
  FROMTIME TIDETIMESTAMP NOT NULL,
  TOTIME TIDETIMESTAMP NOT NULL,
  CHECK (FROMTIME <= TOTIME),
  JOB_ID INT PRIMARY KEY);
  
commit;

CREATE VIEW CHECK_PTS (CHECK_DATES, JOB_ID, FLAG) AS
 SELECT CAST (FROMTIME AS TIDEDATE), JOB_ID,
 CAST (0 AS INT) FROM PENDING
 UNION
 SELECT CAST (TOTIME AS TIDEDATE), JOB_ID,
 CAST (1 AS INT) FROM PENDING;
 
commit;

CREATE TABLE DINNER_CLUB (
 LOC_ID DECIMAL (7) NOT NULL,
 DINNER DINNERTIME);

commit;

/*u1*/INSERT INTO PENDING VALUES (  300, TIMESTAMP '1995-12-15 00:00:00-05:00',
 TIMESTAMP '1995-12-17 00:00:00-05:00', 0);
  
/*e*/INSERT INTO PENDING VALUES ( 101, TIMESTAMP '2025-12-30 19:00:00-05:00',
 TIMESTAMP '2025-12-31 19:00:00-05:00', 1); 
 
/*u1*/INSERT INTO PENDING VALUES (101, TIMESTAMP '2025-12-30 19:00:00-05:00',
 TIMESTAMP '2025-12-31 18:59:59-05:00', 1);
  
/*u1*/INSERT INTO PENDING VALUES (102, TIMESTAMP '1993-12-31 19:00:00-05:00',
 TIMESTAMP '1994-01-02 00:00:00-05:00', 2);
 
/*u1*/INSERT INTO DINNER_CLUB VALUES (0, TIME '17:30:00');

/*u1*/INSERT INTO DINNER_CLUB VALUES (1, CAST (TIME '18:00:00' AS DINNERTIME));

/*e*/INSERT INTO DINNER_CLUB VALUES(2, TIME '19:30:00');

commit;

/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 0, 0.021E0, 151.6);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 1, 0.324E0, 144.5);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 2, 0.134E0, 385.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 3, 0.181E0, 40.9);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 4, 0.037E0, 150.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 5, 3.143E0, 352.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 6, 0.000E0, 50.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 7, 0.104E0, 242.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 8, 0.031E0, 158.6);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 9, 0.000E0, 133.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 10, 0.744E0, 322.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 11, 0.087E0, 307.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 12, 0.260E0, 130.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 13, 0.011E0, 158.7);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 14, 0.107E0, 140.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 15, 0.043E0, 114.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 16, 0.007E0, 116.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 17, 0.004E0, 383.2);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 18, 0.000E0, 17.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 19, 0.488E0, 383.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 20, 0.000E0, 69.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 21, 0.000E0, 103.5);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 22, 0.053E0, 365.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 23, 0.053E0, 37.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 24, 0.023E0, 297.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 25, 0.138E0, 328.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 26, 0.010E0, 124.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 27, 0.000E0, 50.6);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 28, 0.000E0, 49.4);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 29, 0.000E0, 66.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 30, 0.000E0, 67.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 31, 0.000E0, 35.7);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 32, 0.073E0, 285.0);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 33, 0.033E0, 257.3);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 34, 0.000E0, 0.6);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 35, 0.056E0, 128.8);
/*u1*/INSERT INTO CONST_NOT_NORM VALUES (300, 36, 0.038E0, 97.7);
commit;


/*r4*/SELECT COUNT(*) FROM CONST_RAD_NOT_NORM WHERE EPOCH >= 6.2831853E0;

/*e*/SELECT YEAR( CHECK_DATES) FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 0;
/*e*/SELECT YEAR( CHECK_DATES) FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 1;
/*u3*/DELETE FROM PENDING;

/*u1*/INSERT INTO PENDING VALUES (  300, TIMESTAMP '1995-12-15 00:00:00-05:00',
 TIMESTAMP '1995-12-17 00:00:00-05:00', 0);
/*u1*/INSERT INTO PENDING VALUES (101, TIMESTAMP '2020-12-30 19:00:00-05:00',
 TIMESTAMP '2021-12-31 18:59:59-05:00', 1);  
/*u1*/INSERT INTO PENDING VALUES (102, TIMESTAMP '1995-12-31 19:00:00-05:00',
 TIMESTAMP '1996-01-02 00:00:00-05:00', 2);

/*r1996*/SELECT YEAR( CHECK_DATES) FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 1;


ALTER DOMAIN DINNERTIME DROP CONSTRAINT constr1;
/*u1*/INSERT INTO DINNER_CLUB VALUES(6, TIME '19:30:00');
ALTER DOMAIN DINNERTIME ADD CONSTRAINT constr1 CHECK (VALUE BETWEEN TIME '17:30:00' AND TIME '21:00:00');
/*u1*/INSERT INTO DINNER_CLUB VALUES(7, TIME '20:30:00');
/*e*/INSERT INTO DINNER_CLUB VALUES(8, TIME '22:30:00');
commit;

/*u1*/INSERT INTO DINNER_CLUB(LOC_ID) VALUES(10);
/*c0*/SELECT * FROM DINNER_CLUB WHERE DINNER IS NULL;

ALTER DOMAIN DINNERTIME DROP DEFAULT
ALTER DOMAIN DINNERTIME SET DEFAULT TIME '20:30:00';
/*u1*/INSERT INTO DINNER_CLUB(LOC_ID) VALUES(10);
commit;
--TODO/*c2*/SELECT * FROM DINNER_CLUB WHERE DINNER IS NULL;

/*e*/ALTER DOMAIN DINNERTIME RENAME TO DINNERTIME_TYPE;

DROP TABLE DINNER_CLUB;
ALTER DOMAIN DINNERTIME RENAME TO DINNERTIME_TYPE;

CREATE TABLE DINNER_CLUB (
 LOC_ID DECIMAL (7) NOT NULL,
 DINNER DINNERTIME_TYPE);

commit;

DROP DOMAIN DINNERTIME_TYPE CASCADE;
COMMIT;
--/*e*/INSERT INTO DINNER_CLUB VALUES(15, TIME '22:30:00');

drop view CONST_RAD_NOT_NORM;

DROP DOMAIN EPOCH_NOT_NORM CASCADE;
COMMIT;
DROP DOMAIN RAD_EPOCH_TYPE CASCADE;
COMMIT;
DROP DOMAIN RAD_EPOCH_NOT_NORM CASCADE;
COMMIT;
/*e*/DROP DOMAIN TIDEDATE CASCADE;
drop view CHECK_PTS;
DROP DOMAIN TIDEDATE CASCADE;
COMMIT;



DROP DOMAIN TIDETIMESTAMP CASCADE;
COMMIT;

CREATE TYPE EPOCH_NOT_NORM1 AS DECIMAL(5,3)
COMMIT;

DROP TYPE EPOCH_NOT_NORM1;
 
commit;