BEGIN;

DROP TABLE IF EXISTS subscribe CASCADE;
DROP TABLE IF EXISTS api CASCADE;
DROP TABLE IF EXISTS localuser CASCADE;
DROP TABLE IF EXISTS utcstudent;

CREATE TABLE localuser (
  utclogin TEXT PRIMARY KEY,
  email TEXT,
  surname TEXT,
  firstname TEXT  
);

CREATE TABLE api (
  id UUID PRIMARY KEY,
  dbegin DATE NOT NULL,
  code INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  duration INTEGER,
  size INTEGER,
  ects INTEGER NOT NULL,
  resplogin TEXT NOT NULL,
  CHECK (code>0),
  CHECK (duration BETWEEN 1 AND 5),
  CHECK (size>0),
  CHECK (ects BETWEEN 1 AND 3),
  FOREIGN KEY (resplogin) REFERENCES localuser (utclogin),
  UNIQUE (dbegin, code),
  UNIQUE (dbegin, name),
  UNIQUE (dbegin, description)
);

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL,
  api UUID NOT NULL,
  subdate DATE NOT NULL,
  validation BOOLEAN,
  validationdate DATE,
  FOREIGN KEY (utclogin) REFERENCES localuser(utclogin),
  FOREIGN KEY (api) REFERENCES api(id),
  PRIMARY KEY (utclogin,api)
);

COMMIT;