CREATE TABLE company (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    street VARCHAR,
    house_number VARCHAR,
    zip_code VARCHAR,
    city VARCHAR
);

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    login VARCHAR NOT NULL,
    password VARCHAR NOT NULL,
    full_name VARCHAR,
    email VARCHAR,
    admin bool NOT NULL default false,
    get_emails bool NOT NULL default false,
    active bool NOT NULL DEFAULT true
);

CREATE TYPE slot_type AS ENUM ('Quarter', 'Half', 'Hour', 'Day');

CREATE TABLE property (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    description VARCHAR,
    price NUMERIC(9, 2) NOT NULL,
    slot slot_type NOT NULL default 'Hour',
    allow_multi BOOLEAN NOT NULL default true,
    active BOOLEAN NOT NULL default true
);

CREATE TYPE message_type AS ENUM ('NewReservation', 'NewReservationCust', 'ReservationApp', 'ReservationCanceled');

CREATE TABLE message (
    id SERIAL PRIMARY KEY,
    msg_type message_type NOT NULL ,
    subject VARCHAR NOT NULL ,
    "text" TEXT NOT NULL
);

CREATE TABLE opening_hour (
    id SERIAL PRIMARY KEY,
    day INTEGER NOT NULL ,
    "from" TIME NOT NULL ,
    "to" TIME NOT NULL,
    discount INTEGER
);

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR NOT NULL,
    discount INTEGER NOT NULL DEFAULT 0
);

CREATE TYPE reservation_state AS ENUM ('New', 'Approved', 'Canceled');

CREATE TABLE reservation_sum (
    id SERIAL PRIMARY KEY,
    uuid uuid NOT NULL,
    date DATE NOT NULL,
    customer INTEGER REFERENCES customer(id) NOT NULL,
    price NUMERIC(9, 2) NOT NULL,
    state reservation_state NOT NULL DEFAULT 'New',
    date_create DATE NOT NULL,
    edited_by INTEGER REFERENCES "user"(id) ON DELETE SET NULL,
    note VARCHAR
);


CREATE TABLE reservation (
    id SERIAL PRIMARY KEY,
    "from" TIME NOT NULL,
    "to" TIME NOT NULL,
    property INTEGER REFERENCES property(id) NOT NULL,
    summary INTEGER REFERENCES reservation_sum(id) NOT NULL
);
