-- Creating domains

CREATE DOMAIN HASH AS VARCHAR(32);
CREATE DOMAIN CHECKSUM32 AS INTEGER;
CREATE DOMAIN FILE_NAME AS VARCHAR(250);
CREATE DOMAIN PATH_NAME AS VARCHAR(300);
CREATE DOMAIN COMMENT AS VARCHAR(1000);
CREATE DOMAIN BOOLEAN_CHAR AS BOOLEAN DEFAULT FALSE;
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN SIZE AS BIGINT;
CREATE DOMAIN DURATION AS INTEGER;
CREATE DOMAIN TIME_STAMP AS TIMESTAMP;
CREATE DOMAIN CONTENT_TYPE AS VARCHAR(120);
CREATE DOMAIN USER_CODE AS VARCHAR(50);
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);

-- Creating tables

CREATE TABLE FILES  (
  ID TECH_ID NOT NULL,
  NAME FILE_NAME NOT NULL,
  PATH PATH_NAME NOT NULL,
  COMMENT COMMENT,
  REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  REGISTRATOR_CODE USER_CODE NOT NULL,
  EXPIRATION_TIMESTAMP TIME_STAMP NOT NULL,
  USER_ID TECH_ID NOT NULL,
  CONTENT_TYPE CONTENT_TYPE, 
  SIZE SIZE, 
  CRC32_CHECKSUM CHECKSUM32, 
  COMPLETE_SIZE SIZE NOT NULL
);

CREATE TABLE USERS  (
  ID TECH_ID NOT NULL,
  USER_CODE USER_CODE NOT NULL, 
  EMAIL OBJECT_NAME NOT NULL,
  FULL_NAME OBJECT_NAME,
  PASSWORD_HASH HASH,
  IS_EXTERNALLY_AUTHENTICATED BOOLEAN_CHAR DEFAULT 'FALSE',
  IS_ADMIN BOOLEAN_CHAR NOT NULL DEFAULT 'FALSE',
  IS_ACTIVE BOOLEAN_CHAR NOT NULL DEFAULT 'TRUE',
  USER_ID_REGISTRATOR TECH_ID,
  QUOTA_GROUP_ID TECH_ID,
  REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  EXPIRATION_TIMESTAMP TIME_STAMP
);

CREATE TABLE FILE_SHARES  (
  ID TECH_ID NOT NULL,
  FILE_ID TECH_ID NOT NULL,
  USER_ID TECH_ID NOT NULL
);

-- New table QUOTA_GROUPS

CREATE TABLE QUOTA_GROUPS (
  ID TECH_ID NOT NULL,
  FILE_COUNT INTEGER NOT NULL DEFAULT 0,
  FILE_SIZE SIZE NOT NULL DEFAULT 0,
  QUOTA_FILE_COUNT INTEGER,
  QUOTA_FILE_SIZE SIZE,
  FILE_RETENTION DURATION,
  USER_RETENTION DURATION
);

-- Creating primary key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_PK PRIMARY KEY(ID);
ALTER TABLE USERS ADD CONSTRAINT USER_PK PRIMARY KEY(ID);
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_PK PRIMARY KEY(ID);
ALTER TABLE QUOTA_GROUPS ADD CONSTRAINT QUOTA_GROUP_PK PRIMARY KEY(ID);

-- Creating unique key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_BK_UK UNIQUE(PATH);
ALTER TABLE USERS ADD CONSTRAINT USER_BK_UK UNIQUE(USER_CODE);
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_BK_UK UNIQUE(USER_ID,FILE_ID);

-- Creating foreign key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS(ID);
ALTER TABLE USERS ADD CONSTRAINT USER_REGISTRATOR_FK FOREIGN KEY (USER_ID_REGISTRATOR) REFERENCES USERS(ID);
ALTER TABLE USERS ADD CONSTRAINT USER_QUOTA_GROUP_FK FOREIGN KEY (QUOTA_GROUP_ID) REFERENCES QUOTA_GROUPS(ID);
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE;
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_FILE_FK FOREIGN KEY (FILE_ID) REFERENCES FILES(ID) ON DELETE CASCADE;

-- Creating sequences

CREATE SEQUENCE FILE_ID_SEQ;
CREATE SEQUENCE FILE_SHARE_ID_SEQ;
CREATE SEQUENCE USER_ID_SEQ;
CREATE SEQUENCE QUOTA_GROUP_ID_SEQ;

-- Creating indexes

CREATE INDEX FILE_USER_FK_I ON FILES (USER_ID);
CREATE INDEX FILE_USER_NAME_SIZE_I ON FILES (USER_ID, NAME, COMPLETE_SIZE);
CREATE INDEX FISH_FILE_FK_I ON FILE_SHARES (FILE_ID);
CREATE INDEX FISH_USER_FK_I ON FILE_SHARES (USER_ID);
CREATE INDEX USER_QUOTA_GROUP_FK_I ON USERS (QUOTA_GROUP_ID);
CREATE INDEX USER_EMAIL_I ON USERS (EMAIL);