/*
    sample project goal
    ----------------------

    one school per schema

    every school has students
    every school has assignments

    You can assign assignments to students. Those assignments can be from every school.
*/

/* reset */

DROP SCHEMA school1 CASCADE;
DROP SCHEMA school2 CASCADE;

/* create schemas */

CREATE SCHEMA school1;

CREATE SCHEMA school2;

/* create student tables */
CREATE TABLE school1.students(
    id serial primary key NOT NULL,
    name varchar NOT NULL,
    unique(name)
);

CREATE TABLE school2.students(
    id serial primary key NOT NULL,
    name varchar NOT NULL,
    unique(name)
);

/* fill student tables with sample data */
INSERT INTO school1.students ("name")
VALUES ('Max');
INSERT INTO school1.students ("name")
VALUES ('Sarah');
INSERT INTO school1.students ("name")
VALUES ('Jane');

INSERT INTO school2.students ("name")
VALUES ('David');
INSERT INTO school2.students ("name")
VALUES ('Lisa');
INSERT INTO school2.students ("name")
VALUES ('James');


/* create assignments tables */
CREATE TABLE school1.assignments(
    id serial primary key NOT NULL,
    title varchar NOT NULL,
    unique(title)
);

CREATE TABLE school2.assignments(
    id serial primary key NOT NULL,
    title varchar NOT NULL,
    unique(title)
);

/* fill assignment tables with sample data */
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 01');
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 02');
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 03');

INSERT INTO school2.assignments ("title")
VALUES ('Assignment 04');
INSERT INTO school2.assignments ("title")
VALUES ('Assignment 05');
INSERT INTO school2.assignments ("title")
VALUES ('Assignment 06');


/* create assignments_students tables */
CREATE TABLE school1.assignments_students(
    student_id int REFERENCES school1.students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    assignment_id int REFERENCES school1.assignments (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT assignments_products_pkey PRIMARY KEY (assignment_id, student_id)
);

CREATE TABLE school2.assignments_students(
    student_id int REFERENCES school2.students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    assignment_id int REFERENCES school2.assignments (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT assignments_products_pkey PRIMARY KEY (assignment_id, student_id)
);