0_init.sql

CREATE TABLE IF NOT EXISTS config (
	"id" SERIAL PRIMARY KEY,
	"current_semester" TEXT NOT NULL,
	"fresher_week_start" TEXT NOT NULL,
	"fresher_week_end" TEXT NOT NULL,
	"tutor_registration_open" BOOLEAN NOT NULL DEFAULT FALSE,
	"shirt_sizes" TEXT[] NOT NULL DEFAULT '{}',
	"trainings_start" TEXT NOT NULL,
	"trainings_end" TEXT NOT NULL,
	"rally_registration_open" BOOLEAN NOT NULL DEFAULT FALSE,
	"rally_date" TEXT NOT NULL,
	"rally_briefing_date" TEXT NOT NULL,
	"rally_briefing_time" TIME DEFAULT NULL,
	"eswe_link" TEXT NOT NULL DEFAULT '',
	"eswe_registration_start" TIMESTAMP,
	"eswe_start" TEXT,
	"eswe_end" TEXT,
	"eswe_price" INT NOT NULL DEFAULT 0,
	"default_path" TEXT NOT NULL DEFAULT '/information',
	"header_links" TEXT[] NOT NULL DEFAULT '{}',
	"default_permissions" INT NOT NULL DEFAULT 0,
	"schedule_config" JSONB NOT NULL DEFAULT '{}',
	"mail_templates" JSONB NOT NULL DEFAULT '{}',
	"training_mail_reminder_days" INT NOT NULL DEFAULT 7
);

CREATE TABLE IF NOT EXISTS study_programs (
	"id" SERIAL PRIMARY KEY,
	"name" JSONB NOT NULL,
	"tutors_wanted" INT NOT NULL DEFAULT 0,
	"tutorial_names" TEXT[] NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS users (
	"id" TEXT PRIMARY KEY,
	"username" TEXT NOT NULL,
	"permissions" INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS tutorials (
	"id" SERIAL PRIMARY KEY,
	"name" TEXT NOT NULL,
	"study_program" INT NOT NULL REFERENCES study_programs(id),
	"rally_questionnaire_points" INT
);

CREATE TABLE IF NOT EXISTS tutor_trainings (
	"id" SERIAL PRIMARY KEY,
	"date" TEXT NOT NULL,
	"location" TEXT NOT NULL,
	"language" TEXT NOT NULL,
	"max_participants" INT NOT NULL DEFAULT 0,
	"internal" BOOLEAN NOT NULL DEFAULT FALSE,
	"notes" TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS tutors (
	"id" SERIAL PRIMARY KEY,
	"firstname" TEXT NOT NULL,
	"lastname" TEXT NOT NULL,
	"nickname" TEXT,
	"birthday" DATE NOT NULL,
	"email" TEXT NOT NULL,
	"phone" TEXT NOT NULL,
	"address" TEXT NOT NULL,
	"degree" TEXT NOT NULL,
	"gender" TEXT NOT NULL,
	"shirt_size" TEXT NOT NULL,
	"dietary_restriction" TEXT NOT NULL DEFAULT '',
	"study_program" INT NOT NULL REFERENCES study_programs(id),
	"training" INT DEFAULT NULL REFERENCES tutor_trainings(id),
	"sent_training_mail" BOOLEAN NOT NULL DEFAULT FALSE,
	"trained" BOOLEAN NOT NULL DEFAULT FALSE,
	"co_tutor_wish" TEXT NOT NULL DEFAULT '',
	"mentor" BOOLEAN NOT NULL DEFAULT FALSE,
	"tutorial" INT DEFAULT NULL REFERENCES tutorials(id),
	"number" TEXT,
	"notes" TEXT NOT NULL DEFAULT '',
	"password" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS former_tutors (
	"id" SERIAL PRIMARY KEY,
	"email" TEXT NOT NULL UNIQUE,
	"firstname" TEXT NOT NULL,
	"lastname" TEXT NOT NULL,
	"trained_date" TEXT NOT NULL DEFAULT '',
	"years" INT NOT NULL,
	"last_year" TEXT NOT NULL,
	"notes" TEXT,
	"lists" TEXT[] NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS rally_station_supervisors (
	"id" SERIAL PRIMARY KEY,
	"firstname" TEXT NOT NULL,
	"lastname" TEXT NOT NULL,
	"nickname" TEXT,
	"birthday" DATE NOT NULL,
	"email" TEXT NOT NULL,
	"phone" TEXT NOT NULL,
	"morning" BOOLEAN NOT NULL DEFAULT FALSE,
	"afternoon" BOOLEAN NOT NULL DEFAULT FALSE,
	"co_supervisor_wish" TEXT NOT NULL DEFAULT '',
	"notes" TEXT NOT NULL DEFAULT '',
	"password" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS rally_stations (
	"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	"name" TEXT NOT NULL,
	"description" TEXT NOT NULL,
	"points_description" TEXT NOT NULL,
	"should_maximize" BOOLEAN NOT NULL
);

CREATE TABLE IF NOT EXISTS rally_station_assignments (
	"station" UUID NOT NULL REFERENCES rally_stations(id),
	"supervisor" INT NOT NULL REFERENCES rally_station_supervisors(id),
	PRIMARY KEY (station, supervisor)
);

CREATE TABLE IF NOT EXISTS rally_points (
	"rally_station" UUID NOT NULL REFERENCES rally_stations(id),
	"tutorial" INT NOT NULL REFERENCES tutorials(id),
	"points" INT NOT NULL,
	"bribe" INT NOT NULL,
	PRIMARY KEY (rally_station, tutorial)
);

CREATE TABLE IF NOT EXISTS discounts (
	"id" SERIAL PRIMARY KEY,
	"title" TEXT NOT NULL,
	"description" JSONB NOT NULL,
	"address" TEXT NOT NULL,
	"location" FLOAT[] NOT NULL,
	"opening_hours" JSONB NOT NULL,
	"start_date" DATE NOT NULL,
	"end_date" DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS tags (
	"id" SERIAL PRIMARY KEY,
	"name" JSONB NOT NULL
);

CREATE TABLE IF NOT EXISTS discount_tags (
	"discount" INT NOT NULL REFERENCES discounts(id),
	"tag" INT NOT NULL REFERENCES tags(id),
	PRIMARY KEY (discount, tag)
);

CREATE TABLE IF NOT EXISTS schedules (
	"id" SERIAL PRIMARY KEY,
	"title" JSONB NOT NULL,
	"study_program" INT NOT NULL UNIQUE REFERENCES study_programs(id),
	"updated" TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS schedule_entries (
	"id" SERIAL PRIMARY KEY,
	"schedule" INT NOT NULL REFERENCES schedules(id),
	"title" JSONB NOT NULL,
	"location" JSONB,
	"start_uncertainty" INT NOT NULL DEFAULT 0,
	"start" INT NOT NULL,
	"end" INT NOT NULL,
	"end_uncertainty" INT NOT NULL DEFAULT 0,
	"date" DATE NOT NULL
);

CREATE TABLE  IF NOT EXISTS master_freshers (
	"id" SERIAL PRIMARY KEY,
	"firstname" TEXT NOT NULL,
	"lastname" TEXT NOT NULL,
	"email" TEXT NOT NULL,
	"study_program" TEXT NOT NULL,
	"aachen_experience" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS mrx (
	"id" SERIAL PRIMARY KEY,
	"name" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS mrx_entries (
	"id" SERIAL PRIMARY KEY,
	"mrx" INT NOT NULL REFERENCES mrx(id),
	"time" TIMESTAMP NOT NULL,
	"text" TEXT,
	"image" TEXT
);