use kesa;

CREATE TABLE IF NOT EXISTS users (
    id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uname char(255) default NULL UNIQUE KEY,
    pwd char(255) default NULL,
    roles char(6) default "000000",
    created TIMESTAMP DEFAULT NOW(),
    org BOOLEAN,
    footprint DOUBLE default 0,
    registred BOOLEAN
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS userinfo (
    id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userid int(5) default NULL UNIQUE KEY,
    firstname char(255) DEFAULT "",
    lastname char(255) DEFAULT "",
    organization char(255) DEFAULT "",
    email char(255) DEFAULT "",
    language char(3) default 'EST',
    FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS answers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userid INT NOT NULL,
    choice_2_0 INT DEFAULT 0,
    choice_3_0 INT DEFAULT 0,
    choice_3_1 INT DEFAULT 0,
    choice_4_0 INT DEFAULT 0,
    choice_4_1 INT DEFAULT 0,
    choice_4_2 INT DEFAULT 0,
    choice_4_3 INT DEFAULT 0,
    choice_4_4 INT DEFAULT 0,
    choice_4_5 INT DEFAULT 0,
    choice_4_6 INT DEFAULT 0,
    choice_4_7 INT DEFAULT 0,
    choice_4_8 INT DEFAULT 0,
    choice_5_0 INT DEFAULT 0,
    choice_6_0 INT DEFAULT 0,
    choice_6_1 INT DEFAULT 0,
    choice_6_2 INT DEFAULT 0,
    choice_6_3 INT DEFAULT 0,
    choice_6_4 INT DEFAULT 0,
    choice_6_5 INT DEFAULT 0,
    choice_7_0 INT DEFAULT 0,
    choice_7_1 INT DEFAULT 0,
    choice_7_2 INT DEFAULT 0,
    choice_7_3 INT DEFAULT 0,
    choice_7_4 INT DEFAULT 0,
    choice_7_5 INT DEFAULT 0,
    choice_8_0 INT DEFAULT 0,
    choice_8_1 INT DEFAULT 0,
    choice_8_2 INT DEFAULT 0,
    choice_8_3 INT DEFAULT 0,
    period_start_month INT DEFAULT 0,
    period_start_year INT DEFAULT 0,
    period_end_month INT DEFAULT 0,
    period_end_year INT DEFAULT 0,
    footprint DOUBLE default 0,
    footprint_pp DOUBLE default 0,
    calculation TEXT,
    created TIMESTAMP DEFAULT NOW(),
    shared BOOLEAN,
    comment TEXT,
    FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;