drop trigger trg_novels on copper.novels;
drop trigger trg_authors on copper.authors;
drop trigger trg_actors on copper.actors;

ALTER TABLE copper.novels
	DROP CONSTRAINT nvl_FK_thrs;
ALTER TABLE copper.novels
	DROP CONSTRAINT nvl_FK_ctrs;
ALTER TABLE copper.authors
	DROP CONSTRAINT thrs_FK_ctrs;

drop table copper.novels;
drop table copper.authors;
drop table copper.actors;

drop sequence copper.novels_star_seq;
drop sequence copper.authors_star_seq;
drop sequence copper.actors_star_seq;

CREATE SEQUENCE copper.novels_star_seq;
CREATE SEQUENCE copper.authors_star_seq;
CREATE SEQUENCE copper.actors_star_seq;

CREATE TABLE copper.novels (
    star INTEGER DEFAULT nextval('copper.novels_star_seq') NOT NULL PRIMARY KEY,
    title VARCHAR(1023) NOT NULL,
    language VARCHAR(255) NOT NULL,
    author integer NOT NULL,
    modifier integer NOT NULL,
    creationDate timestamp NOT NULL DEFAULT now(),
    updateDate timestamp
) tablespace waxts;

CREATE TABLE copper.authors (
    star INTEGER DEFAULT nextval('copper.authors_star_seq') NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    surname VARCHAR(255) NOT NULL,
    modifier integer NOT NULL,
    creationDate timestamp NOT NULL DEFAULT now(),
    updateDate timestamp
) tablespace waxts;

CREATE TABLE copper.actors (
    star INTEGER DEFAULT nextval('copper.actors_star_seq') NOT NULL PRIMARY KEY,
    userid VARCHAR(255),
    password VARCHAR(255),
    lastUpdatePassword timestamp,
    firstLogin timestamp,
    lastLogin timestamp,
    penalities INTEGER,
    firstname VARCHAR(255) NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    rating INTEGER NOT NULL DEFAULT 0,
    creationDate timestamp NOT NULL DEFAULT now(),
    updateDate timestamp
) tablespace waxts;

create trigger trg_novels before update
on copper.novels for each row
execute procedure upd_dt();
create trigger trg_authors before update
on copper.authors for each row
execute procedure upd_dt();
create trigger trg_actors before update
on copper.actors for each row
execute procedure upd_dt();

ALTER TABLE copper.novels
	ADD CONSTRAINT nvl_FK_thrs
	FOREIGN KEY(author)
	REFERENCES copper.authors(star)
	ON DELETE CASCADE 
	ON UPDATE CASCADE;
ALTER TABLE copper.novels
	ADD CONSTRAINT nvl_FK_ctrs
	FOREIGN KEY(modifier)
	REFERENCES copper.actors(star)
	ON DELETE CASCADE 
	ON UPDATE CASCADE;
ALTER TABLE copper.authors
	ADD CONSTRAINT thrs_FK_ctrs
	FOREIGN KEY(modifier)
	REFERENCES copper.actors(star)
	ON DELETE CASCADE 
	ON UPDATE CASCADE;