/* CodeQ: an online programming tutor.
   Copyright (C) 2015 UL FRI

This program is free software: you can redistribute it and/or modify it under
the terms of the GNU Affero General Public License as published by the Free
Software Foundation, either version 3 of the License, or (at your option) any
later version.

This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
details.

You should have received a copy of the GNU Affero General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>. */

/* initial (server preparatory) statements:
   create user codeq with password 'c0d3q';
   create user vladar with password 'vl4d4r' superuser createdb createrole;
 * to create the database (logged in as vladar):
   create database codeq with encoding 'UTF8' template template0;
   \c codeq
   \i create.sql
   grant all privileges on all tables in schema public to codeq;
   grant all privileges on all sequences in schema public to codeq;
 */

create sequence user_group_id_seq;
create sequence codeq_user_id_seq;
create sequence language_id_seq;
create sequence problem_group_id_seq;
create sequence problem_id_seq;
create sequence solution_id_seq;

create table user_group (
  id integer not null default nextval('user_group_id_seq'),
  name varchar(80) not null,
  constraint user_group_pk primary key (id),
  constraint user_group_uq1 unique (name)
);

create table codeq_user (
  id integer not null default nextval('codeq_user_id_seq'),
  username varchar(30) not null,
  password varchar(128) not null,
  name varchar(200),
  email varchar(254) not null,
  is_admin bool not null,
  is_active bool not null,
  date_joined timestamp not null,
  last_login timestamp not null,
  gui_lang varchar(2),
  robot_address varchar(30),
  gui_layout varchar(30),
  experiments jsonb,
  constraint codeq_user_pk primary key (id),
  constraint codeq_user_uq1 unique (username)
);

create table user_in_group (
  user_group_id integer not null,
  codeq_user_id integer not null,
  constraint user_in_group_pk primary key (user_group_id, codeq_user_id)
);

create table language (
  id integer not null default nextval('language_id_seq'),
  identifier varchar(30) not null,
  constraint language_pk primary key (id),
  constraint language_uq2 unique (identifier)
);

create table problem_group (
  id integer not null default nextval('problem_group_id_seq'),
  identifier varchar(100) not null,
  constraint group_pk primary key (id),
  constraint group_uq2 unique (identifier)
);

create table problem (
  id integer not null default nextval('problem_id_seq'),
  language_id integer not null,
  problem_group_id integer not null,
  identifier varchar(100) not null,
  constraint problem_pk primary key (id),
  constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade,
  constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade,
  constraint problem_uq2 unique (language_id, problem_group_id, identifier)
);

create table solution (
  id integer not null default nextval('solution_id_seq'),
  done bool not null,
  content text not null,
  problem_id integer not null,
  codeq_user_id integer not null,
  trace jsonb not null default '[]'::jsonb,
  constraint solution_pk primary key (id),
  constraint solution_fk1 foreign key (codeq_user_id) references codeq_user (id) on delete cascade on update cascade
);