CREATE DATABASE IF NOT EXISTS volunteermatch;
USE volunteermatch;

CREATE TABLE contact(
contact_id INT AUTO_INCREMENT,
contact_email VARCHAR(45),
contact_first_name VARCHAR(20),
contact_last_name VARCHAR(40),
contact_phone INT(8),
CONSTRAINT PRIMARY KEY (contact_id)
);

INSERT INTO contact VALUES
(NULL, 'jules@gmail.com', 'Julie', 'Gundersen', 12345678),
(NULL, 'elias@gmail.com', 'Elias', 'Brynestad', 23456789);

CREATE TABLE location(
loc_id INT NOT NULL AUTO_INCREMENT,
loc_country VARCHAR(20) NOT NULL,
loc_zip INT(8) NOT NULL,
loc_street_1 VARCHAR(45),
loc_street_2 VARCHAR(10),
loc_city VARCHAR(30),
loc_region VARCHAR(30),
CONSTRAINT PRIMARY KEY (loc_id)
);

INSERT INTO location VALUES
(NULL, 'Norway', 1234, 'Gateveien 32', 'Melkeveien 3', 'Kristiansand', 'Vest-Agder'),
(NULL, 'USA', 4630, 'Trump street 13', 'Obama 1', 'Los Angeles', 'California');

CREATE TABLE account(
acc_id INT AUTO_INCREMENT,
acc_email VARCHAR(20) NOT NULL,
acc_first_name VARCHAR(30) NOT NULL,
acc_last_name VARCHAR(40) NOT NULL,
acc_password VARCHAR(25),
acc_phone INT,
loc_id INT NOT NULL,
cau_id INT NOT NULL,
CONSTRAINT PRIMARY KEY (acc_id),
CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
CONSTRAINT FOREIGN KEY (cau_id) REFERENCES account_cause (cau_id)
);

INSERT INTO account VALUES
(NULL, 'kris@hotmail.com', 'Kristoffer', 'Slettebakken', 'ilovemusic<3', 84930284, 1, 1),
(NULL, 'pingpong@gmail.com', 'Phuong', 'Pham', 'noodles4life', 66748392, 2, 1);

CREATE TABLE organization(
org_id INT NOT NULL AUTO_INCREMENT,
org_name VARCHAR(30) NOT NULL,
org_mission TEXT,
org_description TEXT,
org_phone INT,
org_type VARCHAR(30),
org_registered_date DATE,
org_website_URL TINYTEXT,
org_linkedin_URL TINYTEXT,
org_facebook_URL TINYTEXT,
org_twitter_URL TINYTEXT,
org_image_URL TINYTEXT,
contact_id INT,
loc_id INT NOT NULL,
CONSTRAINT PRIMARY KEY (org_id),
CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
CONSTRAINT FOREIGN KEY (cau_id) REFERENCES organization_cause (cau_id)
);

INSERT INTO organization VALUES
(NULL, 'Wood Worm Workers', 'Organization with a focus surrounding the population of worms in mahogany trees in Malaysia.', 'Organization with 4 employees based in Zimbabwe.', '87655578', 'Private org.', '01012018', 'wwww.www.com', NULL, NULL, NULL, NULL, 1, 1),
(NULL, 'Grey Goose Shelters', 'We are making a stand against the oppression against grey gooses all around the world.', '8400 employees. Organization based in Marokko', '98555325', 'US GOV. org.', '02032018', 'wwww.ggs.com', NULL, NULL, NULL, NULL, 2, 2
);

CREATE TABLE opportunity(
opp_id INT AUTO_INCREMENT,
opp_title VARCHAR(45),
opp_description TEXT,
opp_creation_date DATE,
opp_max_volunteer INT,
opp_min_age INT,
opp_start_datetime DATETIME,
opp_end_datetime DATETIME,
opp_status VARCHAR(30),
opp_great_for VARCHAR(50),
loc_id INT,
contact_id INT,
CONSTRAINT PRIMARY KEY (opp_id),
CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
CONSTRAINT FOREIGN KEY (contact_id) REFERENCES contact (contact_id)
);

INSERT INTO opportunity VALUES
(NULL, 'Counter', 'Count the amount of wood worms in Malaysia', 2019-01-01, 10, 15, '2019-02-01 08:00:00', '2019-03-01 14:00:00', 'Active', 'Groups', 1, 1),
(NULL, 'Lifesaver', 'CPR to the gooses nearby', 2018-07-25, 5, 18, '2018-08-01 08:00:00', '2018-08-31 19:00:00', 'Inactive', 'Kids', 2, 2);

CREATE TABLE interest(
acc_id INT,
opp_id INT,
interest_datetime DATETIME,
interest_status VARCHAR(15),
interest_commitment_start DATETIME,
interest_commitment_end DATETIME,
CONSTRAINT PRIMARY KEY (acc_id, opp_id),
CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity (opp_id)
);

INSERT INTO interest VALUES
(1, 1, '2018-12-30 12:09:10', 'WILL ATTEND', '2019-01-14 10:00:00', '2019-01-06 13:00:00'),
(2, 2, '2018-07-31 14:51:13', 'INQUIRY', '2018-08-01 08:00:00', '2019-01-06 18:30:00');

CREATE TABLE timesheet(
acc_id INT,
opp_id INT,
volunteer_date DATETIME,
timesheet_hours INT (3),
timesheet_entry_date DATETIME,
timesheet_member_approval INT,
timesheet_status VARCHAR(20),
CONSTRAINT PRIMARY KEY (acc_id, opp_id),
CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity (opp_id),
CONSTRAINT FOREIGN KEY (timesheet_member_approval) REFERENCES account (acc_id)
);

INSERT INTO timesheet VALUES
(1, 2, '2019-02-01 09:13:10', 4, '2019-02-01 13:13:10', 'PENDING'),
(2, 1, '2019-02-07 12:45:30', 7, '2018-02-07 19:45:30', 'APPROVED');

CREATE TABLE skill_category(
skill_cat_id INT NOT NULL AUTO_INCREMENT,
skill_cat_name VARCHAR(45),
CONSTRAINT PRIMARY KEY (skill_cat_id)
);

INSERT INTO skill_category VALUES
(NULL, 'Animals and Environment'),
(NULL, 'Language');

CREATE TABLE skill(
skill_id INT AUTO_INCREMENT,
skill_name VARCHAR(45),
skill_cat_id INT,
CONSTRAINT PRIMARY KEY (skill_id),
CONSTRAINT FOREIGN KEY (skill_cat_id) REFERENCES skill_category(skill_cat_id)
);

INSERT INTO skill VALUES
(NULL, 'Animal CPR', 1),
(NULL, 'French', 2);

CREATE TABLE account_skill(
acc_id INT,
skill_id INT,
CONSTRAINT PRIMARY KEY (acc_id, skill_id),
CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
CONSTRAINT FOREIGN KEY (skill_id) REFERENCES skill (skill_id)
);

INSERT INTO account_skill VALUES
(1, 1),
(2, 2);

CREATE TABLE opportunity_skill(
opp_id INT,
skill_id INT,
CONSTRAINT PRIMARY KEY (opp_id, skill_id),
CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity(opp_id),
CONSTRAINT FOREIGN KEY (skill_id) REFERENCES skill(skill_id)
);

INSERT INTO opportunity_skill VALUES
(1, 1),
(2, 2);

CREATE TABLE cause(
cau_id INT NOT NULL AUTO_INCREMENT,
cau_name VARCHAR(30),
CONSTRAINT PRIMARY KEY (cau_id)
);

INSERT INTO cause VALUES
(NULL, 'Environment'),
(NULL, 'International');

CREATE TABLE organization_cause(
cau_id INT,
org_id INT,
CONSTRAINT PRIMARY KEY (cau_id, org_id),
CONSTRAINT FOREIGN KEY (cau_id) REFERENCES cause (cau_id),
CONSTRAINT FOREIGN KEY (org_id) REFERENCES organization (org_id)
);

INSERT INTO organization_cause VALUES
(1, 1),
(2, 2);

CREATE TABLE account_cause(
cau_id INT,
acc_id INT,
CONSTRAINT PRIMARY KEY (cau_id, acc_id),
CONSTRAINT FOREIGN KEY (cau_id) REFERENCES cause (cau_id),
CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id)
);

INSERT INTO account_cause VALUES
(1, 1),
(2, 2);