/*
SQL Script for Troupe

Author: Yang HU ( yah14@pitt.edu )

This database is using MySQL
*/

/* Mysql syntax : disable foreign key*/
SET FOREIGN_KEY_CHECKS=0;

/*PRAGMA foreign_keys=OFF;*/

/*
Delete statements:
To delete the existing database, so that we can always start from a fresh database
*/
/*BEGIN TRANSACTION;*/
DROP TABLE IF EXISTS actors;
DROP TABLE IF EXISTS acts;
DROP TABLE IF EXISTS scenes;
DROP TABLE IF EXISTS stage_objects;
DROP TABLE IF EXISTS plays;
DROP TABLE IF EXISTS stage_sets;
DROP TABLE IF EXISTS play_act_fact;
DROP TABLE IF EXISTS act_scene_fact;
DROP TABLE IF EXISTS stage_set_stage_object_fact;
DROP TABLE IF EXISTS participation;
DROP TABLE IF EXISTS roles;
/*COMMIT;*/

/*
Create statements:
To create a fresh database
*/

/*BEGIN TRANSACTION;*/

CREATE TABLE actors (
  id            int NOT NULL PRIMARY KEY,
  first_name  varchar(50),
  last_name   varchar(50),
  password      varchar(50),
  status        varchar(50),
  address       varchar(50),
  birthdate     date,
  gender        varchar(50)
);

CREATE TABLE acts (
  act_id           int PRIMARY KEY,
  act_description  varchar(50)
);

CREATE TABLE plays (
  play_id           int PRIMARY KEY,
  play_description  varchar(50)
);

CREATE TABLE roles (
  role_id    int PRIMARY KEY,
  name         varchar(50),
  description  varchar(50)
);

CREATE TABLE scenes (
  scene_id           int PRIMARY KEY,
  scene_description  varchar(50)
);

CREATE TABLE stage_objects (
  stage_object_id  int PRIMARY KEY,
  name         varchar(50),
  description  varchar(50),
  location     varchar(50)
);

CREATE TABLE stage_sets (
  stage_set_id    int PRIMARY KEY,
  dscription  varchar(50)
);

CREATE TABLE act_scene_fact (
  act_id    int,
  scene_id  int,
  /* Foreign keys */
  FOREIGN KEY (scene_id)
    REFERENCES scenes(scene_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (act_id)
    REFERENCES acts(act_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE participation (
  actor_id      int,
  scene_id      int,
  role_id       int,
  stage_set_id  int,
  play_id       int,
  act_id        int,
  /* Foreign keys */
  FOREIGN KEY (stage_set_id)
    REFERENCES stage_sets(stage_set_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (actor_id)
    REFERENCES actors(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (scene_id)
    REFERENCES scenes(scene_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (role_id)
    REFERENCES roles(role_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (play_id)
    REFERENCES plays(play_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (act_id)
    REFERENCES acts(act_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE play_act_fact (
  play_id  int,
  act_id   int,
  /* Foreign keys */
  FOREIGN KEY (act_id)
    REFERENCES acts(act_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (play_id)
    REFERENCES plays(play_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE stage_set_stage_object_fact (
  stage_set_id     int,
  stage_object_id  int,
  /* Foreign keys */
  FOREIGN KEY (stage_object_id)
    REFERENCES stage_objects(stage_object_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (stage_set_id)
    REFERENCES stage_sets(stage_set_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
/*COMMIT;*/

/*
Insert statements:
To populate the databse. For each relation, the insert statements will insert a few tuples (record) to populate it.
*/

/*BEGIN TRANSACTION;*/
/* Data for table act-scene-fact */
INSERT INTO act_scene_fact (act_id, scene_id) VALUES (1, 1);
INSERT INTO act_scene_fact (act_id, scene_id) VALUES (1, 2);
INSERT INTO act_scene_fact (act_id, scene_id) VALUES (2, 3);
INSERT INTO act_scene_fact (act_id, scene_id) VALUES (2, 4);



/* Data for table actors */
INSERT INTO actors (id, first_name, last_name, password, status, address, birthdate, gender) VALUES (1, 'John', 'Doe', '123', 'actor', '123 Oak', '1970-02-16', 'Male');
INSERT INTO actors (id, first_name, last_name, password, status, address, birthdate, gender) VALUES (2, 'Rose', 'Lee', '456', 'admin', '55 S. Bouquet', '1991-05-07', 'Female');
INSERT INTO actors (id, first_name, last_name, password, status, address, birthdate, gender) VALUES (3, 'Chris', 'Smith', '582', 'actor-admin', '340 Centre', '1983-11-13', 'Male');



/* Data for table acts */
INSERT INTO acts (act_id, act_description) VALUES (1, 'Act I');
INSERT INTO acts (act_id, act_description) VALUES (2, 'Act II');
INSERT INTO acts (act_id, act_description) VALUES (3, 'Act III');
INSERT INTO acts (act_id, act_description) VALUES (4, 'Act IV');
INSERT INTO acts (act_id, act_description) VALUES (5, 'Act V');
INSERT INTO acts (act_id, act_description) VALUES (6, 'Act VI');
INSERT INTO acts (act_id, act_description) VALUES (7, 'Act VII');

/* Data for table participation */
INSERT INTO participation (actor_id, scene_id, role_id, stage_set_id, play_id, act_id) VALUES (2, 1, 2, 1, 1, 1);
INSERT INTO participation (actor_id, scene_id, role_id, stage_set_id, play_id, act_id) VALUES (1, 1, 1, 2, 1, 1);

/* Data for table play-act-fact */
INSERT INTO play_act_fact (play_id, act_id) VALUES (1, 1);
INSERT INTO play_act_fact (play_id, act_id) VALUES (1, 2);
INSERT INTO play_act_fact (play_id, act_id) VALUES (2, 1);
INSERT INTO play_act_fact (play_id, act_id) VALUES (2, 2);

/* Data for table plays */
INSERT INTO plays (play_id, play_description) VALUES (1, 'Love for the Fallen City');
INSERT INTO plays (play_id, play_description) VALUES (2, 'Chess King');

/* Data for table roles */
INSERT INTO roles (role_id, name, description) VALUES (1, 'snakeman', 'snakeman');
INSERT INTO roles (role_id, name, description) VALUES (2, 'hero', 'hero');

/* Data for table scenes */
INSERT INTO scenes (scene_id, scene_description) VALUES (1, 'Act III - Scene I');
INSERT INTO scenes (scene_id, scene_description) VALUES (2, 'Act III - Scene II');
INSERT INTO scenes (scene_id, scene_description) VALUES (3, 'Act IV - Scene 1');
INSERT INTO scenes (scene_id, scene_description) VALUES (4, 'Act IV - Scene 2');
INSERT INTO scenes (scene_id, scene_description) VALUES (5, 'Acti IV - Scene 3');

/* Data for table stage-objects */
INSERT INTO stage_objects (stage_object_id, name, description, location) VALUES (1, 'Ball', 'Ball', 'front');
INSERT INTO stage_objects (stage_object_id, name, description, location) VALUES (2, 'Pen', 'Pen', 'rear');
INSERT INTO stage_objects (stage_object_id, name, description, location) VALUES (3, 'Stick', 'Stick', 'left');
INSERT INTO stage_objects (stage_object_id, name, description, location) VALUES (4, 'Dog', 'Dog', 'Right');



/* Data for table stage-set-stage-object-fact */
INSERT INTO stage_set_stage_object_fact (stage_set_id, stage_object_id) VALUES (1, 1);
INSERT INTO stage_set_stage_object_fact (stage_set_id, stage_object_id) VALUES (1, 2);
INSERT INTO stage_set_stage_object_fact (stage_set_id, stage_object_id) VALUES (2, 3);
INSERT INTO stage_set_stage_object_fact (stage_set_id, stage_object_id) VALUES (2, 4);



/* Data for table stage-sets */
INSERT INTO stage_sets (stage_set_id, dscription) VALUES (1, 'Set 1');
INSERT INTO stage_sets (stage_set_id, dscription) VALUES (2, 'Set 2');
/*COMMIT;*/

/* Mysql syntax : enable foreign key*/
SET FOREIGN_KEY_CHECKS=1;