data.sql

DROP TABLE IF EXISTS items_by_user;

DROP TABLE IF EXISTS items;
CREATE TABLE IF NOT EXISTS items (item_id SERIAL PRIMARY KEY, listType VARCHAR(55), item VARCHAR (255), amount INTEGER, category VARCHAR (55));

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (user_id SERIAL PRIMARY KEY, name VARCHAR(55), household INTEGER, base BOOLEAN, heat BOOLEAN, cold BOOLEAN, infant BOOLEAN, child BOOLEAN, meds BOOLEAN, pets BOOLEAN, numberdays INTEGER, userState VARCHAR(2), userCity VARCHAR(50));

CREATE TABLE IF NOT EXISTS items_by_user (user_id INTEGER REFERENCES users, item_id INTEGER REFERENCES items, added_on DATE);

INSERT INTO items (listType, item, amount, category) VALUES ('base', 'flashlight', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'calories of food', 2000, 'per person per day');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'gallon of water', 1, 'per person per day');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'variety of batteries', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'hand crank radio', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'first aid kit', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'set of personal hygiene items for all family members', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'multi tool', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'set of chargers for phones and other devices', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'copies of personal documents for all family members', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'cash', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'emergency blanket', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'insect repellent', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'emergency whistle', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'matches and/or lighters', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'propane stove', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', '4-5 canisters of propane', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'thermometer', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'vaccination records for family', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'a few bottles of pedialite', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'can opener', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'selection of books', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'deck of playing cards', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'paper and pencils', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'set of local maps', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'bags for human waste', 2, 'per person per day');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'rolls of toilet paper', 2, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('base', 'trash can with lid for human waste bags', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('heat', 'sets of light, loose clothing', 2, 'per-person');
INSERT INTO items (listType, item, amount, category) VALUES ('heat', 'extra water rations', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('heat', 'sun hat', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('cold', 'shovel', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('cold', 'pair of gloves', 1, 'per-person');
INSERT INTO items (listType, item, amount, category) VALUES ('cold', 'set of thermal underwear', 1, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('cold', 'sets of warm layers', 2, 'per person');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'pet food', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'gallons water', 3, 'per pet');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'a few rolls of poop bags', 1, 'per pet');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'extra collar', 1, 'per pet');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'extra leash', 1, 'per pet');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'extra crate or carrier for pet', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('pets', 'set of supplies specific to your pet', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', '3 or 4 cans of formula', 1, 'per infant');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', 'large pack diapers', 1, 'per infant');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', 'baby wipes', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', 'set of baby blankets', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', 'diaper rash cream', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('infant', 'baby sling or carrier', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('child', 'collection of small games', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('child', 'set of hygiene items for your child', 1, ' ');
INSERT INTO items (listType, item, amount, category) VALUES ('meds', 'store of all current medications', 1, ' ');