BEGIN;

CREATE SCHEMA IF NOT EXISTS training;

CREATE TABLE IF NOT EXISTS training.trainings (
  id          UUID PRIMARY KEY,
  name        varchar(255) NOT NULL,
  description text NOT NULL,
  days        smallint NOT NULL,
  price       decimal NOT NULL
);

CREATE TABLE IF NOT EXISTS training.prices (
  training_id UUID REFERENCES training.trainings(id),
  amount      NUMERIC(6, 4) NOT NULL,
  currency    VARCHAR(3) NOT NULL,
  CHECK       (currency IN ('USD', 'EUR', 'CZK'))
  type        VARCHAR(10) NOT NULL,
  CHECK       (type IN ('OPEN', 'CORPORATE', 'STUDENT', 'GOVERNMENT'))
  PRIMARY KEY (training_id, currency, type) -- composite primary key
)

CREATE TABLE IF NOT EXISTS training.dates (
  id          UUID PRIMARY KEY,
  training_id UUID NOT NULL,
  date        DATE NOT NULL,
  start_time  TIME NOT NULL,
  days        SMALLINT NOT NULL,
  is_online   BOOLEAN NOT NULL,
  location    VARCHAR(255) NOT NULL,
  address     VARCHAR(255) NOT NULL,
  capacity    SMALLINT NOT NULL,
  amount      NUMERIC(6, 4) NOT NULL,
  currency    VARCHAR(3) NOT NULL,
  CHECK       (currency IN ('USD', 'EUR', 'CZK'))
  FOREIGN KEY (training_id) REFERENCES training.trainings(id)
);

CREATE TABLE IF NOT EXISTS training.attendees (
  id            UUID PRIMARY KEY,
  date_id       UUID NOT NULL,
  name          VARCHAR(255) NOT NULL,
  email         VARCHAR(255) NOT NULL,
  company       VARCHAR(255) NOT NULL,
  role          VARCHAR(255) NOT NULL,
  is_student    BOOLEAN NOT NULL,
  has_attended  BOOLEAN NOT NULL,
  has_paid      BOOLEAN NOT NULL,
  FOREIGN KEY (date_id) REFERENCES training.dates(id)
);

CREATE TABLE IF NOT EXISTS training.feedback (
  id                  UUID PRIMARY KEY,
  attendee_id         UUID NOT NULL,
  rating              SMALLINT NOT NULL,
  comment             TEXT NOT NULL,
  is_anonymous        BOOLEAN NOT NULL,
  is_sharing_allowed  BOOLEAN NOT NULL,
  FOREIGN KEY (attendee_id) REFERENCES training.attendees(id)
);

COMMIT;
