/*
 * Create the database schema of the AKCRS system
 * for the PostgreSQL DBMS
 */

DROP TABLE reports;
DROP TABLE bugs;
DROP TABLE users;
DROP TYPE bugstate;


CREATE TYPE bugstate AS ENUM ('Open', 'Analyzed', 'Feedback', 'Closed', 'Suspended');


CREATE TABLE users
(
    id serial NOT NULL,
    email varchar(254) NOT NULL,
    password char(64) NOT NULL,
    is_developer boolean DEFAULT false,
    
    CONSTRAINT users_pkey PRIMARY KEY (id)
);


CREATE TABLE bugs
(
    id serial NOT NULL,
    state bugstate NOT NULL,
    reported integer NOT NULL,
    
    CONSTRAINT bugs_pkey PRIMARY KEY (id)
);


CREATE TABLE reports
(
    id serial NOT NULL,
    bug_id integer,
    user_id integer NOT NULL,
    received_datetime timestamp DEFAULT CURRENT_TIMESTAMP,
    --received_date date DEFAULT CURRENT_DATE,
    --received_time time DEFAULT LOCALTIME,
    confirmation_code char(16) NOT NULL,
    confirmed boolean DEFAULT false,
    bugs_id integer[],
    crashtype text,
    crashdate text,
    hostname text,
    ostype text,
    osrelease text,
    version text,
    machine text,
    panic text,
    backtrace text,
    top_significant_func text,
    rem_significant_funcs text[],
    ps_axl text,
    vmstat_s text,
    vmstat_m text,
    vmstat_z text,
    vmstat_i text,
    pstat_T text,
    pstat_s text,
    iostat text,
    ipcs_a text,
    ipcs_T text,
    nfsstat text,
    netstat_s text,
    netstat_m text,
    netstat_id text,
    netstat_anr text,
    netstat_anA text,
    netstat_aL text,
    fstat text,
    dmesg text,
    kernelconfig text,
    ddbcapturebuffer text,
    
    CONSTRAINT reports_pkey PRIMARY KEY (id),
    CONSTRAINT reports_bug_id_fkey FOREIGN KEY (bug_id) REFERENCES bugs (id),
    CONSTRAINT reports_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id)
);