-- SERIAL will automatically increment an integer starting from 1


DROP SEQUENCE author_author_id_seq;
DROP SEQUENCE color_color_id_seq;
DROP SEQUENCE comments_comment_id_seq;
DROP SEQUENCE form_form_id_seq;
DROP SEQUENCE school_school_id_seq;
DROP SEQUENCE timeline_timeline_id_seq;
DROP SEQUENCE type_type_id_seq;

DROP TABLE color;
CREATE TABLE color (
color_id       SERIAL PRIMARY KEY,
color          CHAR(20)
);

DROP TABLE type;
CREATE TABLE type (
type_id        SERIAL PRIMARY KEY,
type           CHAR(20)
);

DROP TABLE timeline;
CREATE TABLE timeline (
timeline_id    SERIAL PRIMARY KEY,
period         CHAR(20)
);

DROP TABLE school;
CREATE TABLE school (
school_id      SERIAL PRIMARY KEY,
school         CHAR(20) DEFAULT NULL
);

DROP TABLE form;
CREATE TABLE form (
form_id        SERIAL PRIMARY KEY,
form           CHAR(20)
);

DROP TABLE author;
CREATE TABLE author ( 
author_id      SERIAL PRIMARY KEY,
name           CHAR(60), 
birth_year     INTEGER DEFAULT NULL,
death_year     INTEGER DEFAULT NULL,
birth_location CHAR(20) DEFAULT NULL,
death_location CHAR(20) DEFAULT NULL,
school_id      INTEGER REFERENCES school ON UPDATE CASCADE ON DELETE SET NULL,
time_id        INTEGER REFERENCES timeline ON UPDATE CASCADE ON DELETE SET NULL
);

DROP TABLE picture;
CREATE TABLE picture (
picture_id     INTEGER PRIMARY KEY,
author_id      INTEGER REFERENCES author ON UPDATE CASCADE ON DELETE SET NULL,
form_id        INTEGER REFERENCES form ON UPDATE CASCADE ON DELETE SET NULL,
type_id        INTEGER REFERENCES type ON UPDATE CASCADE ON DELETE SET NULL,
color_id       INTEGER REFERENCES color ON UPDATE CASCADE ON DELETE SET NULL,
technique      CHAR(20)DEFAULT NULL,
height         INTEGER DEFAULT NULL,
width          INTEGER DEFAULT NULL,
location       CHAR(40)DEFAULT NULL,
date           INTEGER DEFAULT NULL,
title          CHAR(60)DEFAULT NULL,
kb             INTEGER DEFAULT NULL,
y_index        FLOAT DEFAULT NULL,
i_index        FLOAT DEFAULT NULL,
q_index        FLOAT DEFAULT NULL
);

DROP TABLE info;
CREATE TABLE info (
info_id        INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
info           VARCHAR DEFAULT NULL
);

DROP TABLE bio;
CREATE TABLE bio (
bio_id         INTEGER REFERENCES author ON UPDATE CASCADE ON DELETE CASCADE,
bio            VARCHAR DEFAULT NULL
);

DROP TABLE ratings;
CREATE TABLE ratings (
ratings_id     INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
total_ratings  FLOAT DEFAULT NULL,
num_ratings    FLOAT DEFAULT NULL
);

DROP TABLE comments;
CREATE TABLE comments (
comment_id     SERIAL PRIMARY KEY,
picture_id     INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
comments       VARCHAR DEFAULT NULL,
by	       VARCHAR DEFAULT NULL,
datetime       TIMESTAMP DEFAULT NULL

);

DROP TABLE y;
CREATE TABLE y (
y_id           INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
x              INTEGER,
y              INTEGER,
value          FLOAT
);

DROP TABLE i;
CREATE TABLE i (
i_id           INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
x              INTEGER,
y              INTEGER,
value          FLOAT
);

DROP TABLE q;
CREATE TABLE q (
q_id           INTEGER REFERENCES picture ON UPDATE CASCADE ON DELETE CASCADE,
x              INTEGER,
y              INTEGER,
value          FLOAT
);

GRANT select ON author TO public;
GRANT select ON info TO public;
GRANT select ON picture TO public;
GRANT select ON color TO public;
GRANT select ON type TO public;
GRANT select ON school TO public;
GRANT select ON timeline TO public;
GRANT select ON y TO public;
GRANT select ON i TO public;
GRANT select ON q TO public;
GRANT select ON bio TO public;
GRANT select ON form TO public;
GRANT select ON comments TO public;
GRANT select ON ratings TO public;
GRANT insert ON comments TO public;
GRANT insert ON ratings TO public;
GRANT update ON ratings TO public;
GRANT ALL ON comments_comment_id_seq TO PUBLIC;