/*************************************************************************************
 * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
 *************************************************************************************/

/* Alte Tabellen (in der richtigen Reihenfolge) löschen:

DROP VIEW highscore;
DROP VIEW enduserscore;

DROP TABLE score;
DROP TABLE game;
DROP TABLE enduser;

DROP TABLE dummy;
*/


CREATE TABLE enduser
  (e_id            INTEGER      NOT NULL,
   e_name          VARCHAR( 30) NOT null,
   e_email         VARCHAR(100) NOT NULL,
   e_password_md5  VARCHAR( 32) NOT null,
   e_verified      BOOLEAN      NOT null,
   e_verify_code   VARCHAR( 32),

   CONSTRAINT pk_enduser
     PRIMARY KEY (e_id),

   CONSTRAINT unique_e_email
     UNIQUE (e_email),

   CONSTRAINT unique_e_name_e_pos
     UNIQUE (e_name)
  );

CREATE TABLE game
  (g_id                            INTEGER     NOT NULL,
   g_title                         VARCHAR(50) NOT NULL,
   g_max_nr_of_levels              INTEGER     NOT NULL,
   g_max_nr_of_enduserscore_entries INTEGER     NOT NULL,

   CONSTRAINT pk_g_title
     PRIMARY KEY (g_id),

   CONSTRAINT unique_g_title
     UNIQUE (g_title)
  );
                
CREATE TABLE score
  (e_id          INTEGER                  NOT NULL,
   g_id          INTEGER                  NOT NULL,
   s_time        TIMESTAMP WITH TIME ZONE NOT NULL,
   s_level       INTEGER                  NOT NULL DEFAULT 1,
   s_score       INTEGER                  NOT NULL,

   CONSTRAINT pk_s_score
     PRIMARY KEY (e_id, g_id, s_time),

   CONSTRAINT fk_s_score_enduser
     FOREIGN KEY (e_id) REFERENCES enduser (e_id),

   CONSTRAINT fk_s_score_game
     FOREIGN KEY (g_id) REFERENCES game (g_id)
  );

/*
DELETE FROM score;
DELETE FROM game;
DELETE FROM enduser;
*/

INSERT INTO enduser(e_id, e_name, e_email, e_password_md5, e_verified) 
VALUES (0, 'test', 'kowa@hs-augsburg.de', '098f6bcd4621d373cade4e832627b4f6', false);
INSERT INTO enduser(e_id, e_name, e_email, e_password_md5, e_verified)
VALUES (1, 'kowa', 'wolfgang@kowarschick.de', 'fc07c3f222be5b149b3d4094bd278ecb', true);

INSERT INTO game(g_id, g_title, g_max_nr_of_levels, g_max_nr_of_enduserscore_entries)
VALUES (1, 'WKSnake 1', 1, 20);
INSERT INTO game(g_id, g_title, g_max_nr_of_levels, g_max_nr_of_enduserscore_entries)
VALUES (2, 'SKTetris', 100, 20);

INSERT INTO score(e_id, g_id, s_time, s_score, s_level)
VALUES (1, 1, current_timestamp - interval '1 day', 1234, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (1, 1, current_timestamp, 879, 1);

INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '5 days', 1111, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '4 days', 20, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 1, current_timestamp - interval '3 days', 1579, 1);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 2, current_timestamp - interval '2 days', 142, 3);
INSERT INTO score(e_id, g_id, s_time, s_score, s_level)  
VALUES (0, 2, current_timestamp, 87, 2);

/***********************************************************************
 * Views
 ***********************************************************************/

/*
DROP VIEW highscore;
DROP VIEW enduserscore;
 */

CREATE VIEW enduserscore (e_id, e_name, e_email, e_password_md5, e_verified, g_id, g_title, s_score, s_level, s_time) AS
SELECT   e_id, e_name, e_email, e_password_md5, e_verified, g_id, g_title, s_score, s_level, s_time
FROM     enduser NATURAL JOIN score NATURAL JOIN game
ORDER BY g_id, s_score DESC, s_level DESC
;

CREATE VIEW highscore (e_id, e_name, g_id, g_title, s_score, s_level, s_time) AS
SELECT   e_id, e_name, g_id, g_title, s_score, s_level, s_time
FROM     enduserscore
WHERE    e_verified = TRUE
;

/***********************************************************************
 * Dummy-Tabelle. Diese Tabelle kann für Abfragen eingesetzt 
 * werden, für deren Beantwortung eigentlich gar keine Tabelle
 * benötigt wird. Laut SQL-Standard muss immer eine Tabelle
 * in der FROM-Klausel einer SELECT-Anweisung angegeben werden.
 *
 * In PostgreSQL ist es auch möglich, SELECT-Anweisungen ohne 
 * zugehörige FROM-Klausel anzugeben. Dies ist allerdings nicht
 * standard-konform.
 ***********************************************************************/

CREATE TABLE dummy
(
 id INTEGER NOT NULL,
 CONSTRAINT e_dummy
   PRIMARY KEY (id)
);

INSERT INTO dummy
VALUES (1);

/***********************************************************************
 * Rights
 ***********************************************************************/

REVOKE ALL ON game FROM PUBLIC;
GRANT SELECT ON game TO GROUP iam;

REVOKE ALL ON enduser FROM PUBLIC;
GRANT SELECT ON enduser TO GROUP iam;

REVOKE ALL ON score FROM PUBLIC;
GRANT SELECT ON score TO GROUP iam;

REVOKE ALL ON dummy FROM PUBLIC;
GRANT SELECT ON dummy TO GROUP iam;

ALTER DATABASE web_highscore OWNER TO highscore;

ALTER TABLE game   OWNER TO highscore;
ALTER TABLE enduser OWNER TO highscore;
ALTER TABLE score  OWNER TO highscore;

ALTER TABLE dummy  OWNER TO highscore;

ALTER TABLE enduserscore OWNER TO highscore;
ALTER TABLE highscore   OWNER TO highscore;
