/* 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 codeq_user_id_seq to codeq;
   grant all privileges on solution_id_seq to codeq;
 */

create sequence codeq_user_id_seq;
create sequence solution_id_seq;

create table codeq_user (
  id integer not null default nextval('codeq_user_id_seq'),
  username varchar(30) not null,
  password varchar(128) not null,
  first_name varchar(30) not null,
  last_name varchar(30) not null,
  email varchar(254) not null,
  is_superuser bool not null,
  is_staff bool not null,
  is_active bool not null,
  date_joined timestamp not null,
  last_login timestamp not null,
  constraint codeq_user_pk primary key (id),
  constraint codeq_user_uq1 unique (username)
);

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
);