DROP TABLE IF EXISTS people;
CREATE TABLE people (
    id INT NOT NULL auto_increment,
    name VARCHAR(255) NULL DEFAULT NULL, 
    phone_number VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS questionaires;
CREATE TABLE questionaires (
    id INT NOT NULL auto_increment,
    code VARCHAR(100) NOT NULL,  -- always a letter
    description VARCHAR(255) NOT NULL,  
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS questions;
CREATE TABLE questions (
    id INT NOT NULL auto_increment,
    questionaire_id INT NOT NULL,
    code VARCHAR(100) NOT NULL , -- always a number
    longdesc VARCHAR(255) NULL DEFAULT NULL,
    shortdesc VARCHAR(20) NULL DEFAULT NULL,
    datatype VARCHAR(100) NOT NULL,  -- number, char, bool
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS answers;
CREATE TABLE answers (
    id INT NOT NULL auto_increment,
    question_id INT NOT NULL,
    person_id INT NOT NULL,
    answer VARCHAR(255) NOT NULL,
    recieved TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

-- add group table in the future
