-- Open Social Network schema specification		0_01
-- 
-- Copyright (c) 2006, Jonathan Vanasco
-- All rights reserved.
-- Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
-- 	• Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
-- 	• Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
-- 	• Neither the name of the organization nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission
-- 
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


CREATE TABLE open_sn__gender (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__gender VALUES ( 10 , 'female' );
INSERT INTO open_sn__gender VALUES ( 11 , 'male' );

CREATE TABLE open_sn__ethnicity (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__ethnicity VALUES ( 11 , 'African Descent' );
INSERT INTO open_sn__ethnicity VALUES ( 17 , 'Caucasian' );
INSERT INTO open_sn__ethnicity VALUES ( 10 , 'Asian' );
INSERT INTO open_sn__ethnicity VALUES ( 12 , 'East Indian' );
INSERT INTO open_sn__ethnicity VALUES ( 13 , 'Hispanic / Latino' );
INSERT INTO open_sn__ethnicity VALUES ( 14 , 'Middle Eastern' );
INSERT INTO open_sn__ethnicity VALUES ( 15 , 'Native American' );
INSERT INTO open_sn__ethnicity VALUES ( 16 , 'Pacific Islander' );


CREATE TABLE open_sn__hair (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__hair VALUES ( 11 , 'Brunette' );
INSERT INTO open_sn__hair VALUES ( 12 , 'Blonde' );
INSERT INTO open_sn__hair VALUES ( 13 , 'Red' );
INSERT INTO open_sn__hair VALUES ( 14 , 'Grey' );

CREATE TABLE open_sn__eyes (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__eyes VALUES ( 11 , 'Brown' );
INSERT INTO open_sn__eyes VALUES ( 12 , 'Blue' );
INSERT INTO open_sn__eyes VALUES ( 13 , 'Green' );

CREATE TABLE open_sn__height_units (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__height_units VALUES ( 11 , 'metric' );
INSERT INTO open_sn__height_units VALUES ( 12 , 'english traditional' );

CREATE TABLE open_sn__weight_units (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__weight_units VALUES ( 11 , 'metric' );
INSERT INTO open_sn__weight_units VALUES ( 12 , 'english traditional' );

CREATE TABLE open_sn__zodiac (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__zodiac VALUES ( 11 , 'Aries' );
INSERT INTO open_sn__zodiac VALUES ( 12 , 'Taurus' );
INSERT INTO open_sn__zodiac VALUES ( 13 , 'Gemini' );
INSERT INTO open_sn__zodiac VALUES ( 14 , 'Cancer' );
INSERT INTO open_sn__zodiac VALUES ( 15 , 'Leo' );
INSERT INTO open_sn__zodiac VALUES ( 16 , 'Virgo' );
INSERT INTO open_sn__zodiac VALUES ( 17 , 'Libra' );
INSERT INTO open_sn__zodiac VALUES ( 18 , 'Scorpio' );
INSERT INTO open_sn__zodiac VALUES ( 19 , 'Saggitarius' );
INSERT INTO open_sn__zodiac VALUES ( 20 , 'Capricorn' );
INSERT INTO open_sn__zodiac VALUES ( 21 , 'Aquarius' );
INSERT INTO open_sn__zodiac VALUES ( 22 , 'Pisces' );

CREATE TABLE open_sn__relationship_status (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__relationship_status VALUES ( 10 , 'Single' );
INSERT INTO open_sn__relationship_status VALUES ( 11 , 'In a Relationship' );
INSERT INTO open_sn__relationship_status VALUES ( 12 , 'In a Domestic Partnership' );
INSERT INTO open_sn__relationship_status VALUES ( 13 , 'Married' );
INSERT INTO open_sn__relationship_status VALUES ( 14 , 'It\s Complicated' );
INSERT INTO open_sn__relationship_status VALUES ( 15 , 'Divorced' );

CREATE TABLE open_sn__sexual_orientation (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__sexual_orientation VALUES ( 10 , 'heterosexual' );
INSERT INTO open_sn__sexual_orientation VALUES ( 11 , 'homosexual' );
INSERT INTO open_sn__sexual_orientation VALUES ( 12 , 'bisexual' );

CREATE TABLE open_sn__religion(
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__religion VALUES ( 11 , 'Agnostic' );
INSERT INTO open_sn__religion VALUES ( 12 , 'Atheist' );
INSERT INTO open_sn__religion VALUES ( 13 , 'Buddhist' );
INSERT INTO open_sn__religion VALUES ( 14 , 'Catholic' );
INSERT INTO open_sn__religion VALUES ( 15 , 'Christian' );
INSERT INTO open_sn__religion VALUES ( 16 , 'Christian Scientist' );
INSERT INTO open_sn__religion VALUES ( 17 , 'Hindu' );
INSERT INTO open_sn__religion VALUES ( 18 , 'Jewish' );
INSERT INTO open_sn__religion VALUES ( 19 , 'Mormon' );
INSERT INTO open_sn__religion VALUES ( 20 , 'Muslim' );
INSERT INTO open_sn__religion VALUES ( 21 , 'None' );
INSERT INTO open_sn__religion VALUES ( 22 , 'Pagan' );
INSERT INTO open_sn__religion VALUES ( 23 , 'Protestant' );
INSERT INTO open_sn__religion VALUES ( 24 , 'Scientologist' );
INSERT INTO open_sn__religion VALUES ( 25 , 'Taoist' );
INSERT INTO open_sn__religion VALUES ( 26 , 'Unitarian' );
INSERT INTO open_sn__religion VALUES ( 27 , 'Quaker' );
INSERT INTO open_sn__religion VALUES ( 28 , 'Wiccan' );
	
CREATE TABLE open_sn__alcohol (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__alcohol VALUES ( 10 , 'Never' );
INSERT INTO open_sn__alcohol VALUES ( 11 , 'Sometimes' );
INSERT INTO open_sn__alcohol VALUES ( 12 , 'Regularly' );

CREATE TABLE open_sn__tobacco (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__tobacco VALUES ( 10 , 'Never' );
INSERT INTO open_sn__tobacco VALUES ( 11 , 'Sometimes' );
INSERT INTO open_sn__tobacco VALUES ( 12 , 'Regularly' );
	
CREATE TABLE open_sn__drugs (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__drugs VALUES ( 10 , 'Never' );
INSERT INTO open_sn__drugs VALUES ( 11 , 'Sometimes' );
INSERT INTO open_sn__drugs VALUES ( 12 , 'Regularly' );


CREATE TABLE open_sn__diet(
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__diet VALUES ( 11 , 'Omnivore' );
INSERT INTO open_sn__diet VALUES ( 12 , 'Carnivore' );
INSERT INTO open_sn__diet VALUES ( 13 , 'Pesco/Pollo-Vegetarian' );
INSERT INTO open_sn__diet VALUES ( 14 , 'Pescetarian' );
INSERT INTO open_sn__diet VALUES ( 15 , 'Pollo-Vegetarian' );
INSERT INTO open_sn__diet VALUES ( 16 , 'Vegetarian' );
INSERT INTO open_sn__diet VALUES ( 17 , 'Vegan' );
INSERT INTO open_sn__diet VALUES ( 18 , 'Kosher' );

CREATE TABLE open_sn__children (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__children VALUES ( 10 , 'Decided- None' );
INSERT INTO open_sn__children VALUES ( 11 , 'Decided- Someday' );
INSERT INTO open_sn__children VALUES ( 12 , 'Undecided' );
INSERT INTO open_sn__children VALUES ( 14 , 'Proud parent' );
	
	
CREATE TABLE open_sn__country_iso (
	iso_abbv CHAR(2) NOT NULL PRIMARY KEY ,
	name VARCHAR(64) NOT NULL UNIQUE
);
INSERT INTO open_sn__country_iso VALUES ('us','United States');
INSERT INTO open_sn__country_iso VALUES ('ca','Canada');
INSERT INTO open_sn__country_iso VALUES ('gb','United Kingdom');
INSERT INTO open_sn__country_iso VALUES ('ie','Ireland');
INSERT INTO open_sn__country_iso VALUES ('nl','Netherlands');
INSERT INTO open_sn__country_iso VALUES ('fr','France');
INSERT INTO open_sn__country_iso VALUES ('de','Germany');
INSERT INTO open_sn__country_iso VALUES ('it','Italy');
INSERT INTO open_sn__country_iso VALUES ('es','Spain');
INSERT INTO open_sn__country_iso VALUES ('ch','Switzerland');
INSERT INTO open_sn__country_iso VALUES ('at','Austria');
INSERT INTO open_sn__country_iso VALUES ('no' , 'Norway');
INSERT INTO open_sn__country_iso VALUES ('se' , 'Sweden');
INSERT INTO open_sn__country_iso VALUES ('au','Australia');
INSERT INTO open_sn__country_iso VALUES ('nz','New Zealand');
INSERT INTO open_sn__country_iso VALUES ('jp','Japan');
INSERT INTO open_sn__country_iso VALUES ('be','Belgium');
INSERT INTO open_sn__country_iso VALUES ('dk','Denmark');
INSERT INTO open_sn__country_iso VALUES ('hr' , 'Croatia');
INSERT INTO open_sn__country_iso VALUES ('gr' , 'Greece');
INSERT INTO open_sn__country_iso VALUES ('tw' , 'Taiwan');
INSERT INTO open_sn__country_iso VALUES ('af','Afghanistan');
INSERT INTO open_sn__country_iso VALUES ('ax','Aland Islands');
INSERT INTO open_sn__country_iso VALUES ('al','Albania');
INSERT INTO open_sn__country_iso VALUES ('dz','Algeria');
INSERT INTO open_sn__country_iso VALUES ('as','American Samoa');
INSERT INTO open_sn__country_iso VALUES ('ad','Andorra');
INSERT INTO open_sn__country_iso VALUES ('ao','Angola');
INSERT INTO open_sn__country_iso VALUES ('ai','Anguilla');
INSERT INTO open_sn__country_iso VALUES ('aq','Antarctica');
INSERT INTO open_sn__country_iso VALUES ('ag','Antigua and Barbuda');
INSERT INTO open_sn__country_iso VALUES ('ar','Argentina');
INSERT INTO open_sn__country_iso VALUES ('am','Armenia');
INSERT INTO open_sn__country_iso VALUES ('aw','Aruba');
INSERT INTO open_sn__country_iso VALUES ('az','Azerbaijan');
INSERT INTO open_sn__country_iso VALUES ('bs','Bahamas');
INSERT INTO open_sn__country_iso VALUES ('bh','Bahrain');
INSERT INTO open_sn__country_iso VALUES ('bd','Bangladesh');
INSERT INTO open_sn__country_iso VALUES ('bb','Barbados');
INSERT INTO open_sn__country_iso VALUES ('by','Belarus');
INSERT INTO open_sn__country_iso VALUES ('bz','Belize');
INSERT INTO open_sn__country_iso VALUES ('bj','Benin');
INSERT INTO open_sn__country_iso VALUES ('bm','Bermuda');
INSERT INTO open_sn__country_iso VALUES ('bt','Bhutan');
INSERT INTO open_sn__country_iso VALUES ('bo','Bolivia');
INSERT INTO open_sn__country_iso VALUES ('ba','Bosnia and Herzegovina');
INSERT INTO open_sn__country_iso VALUES ('bw','Botswana');
INSERT INTO open_sn__country_iso VALUES ('bv','Bouvet Island');
INSERT INTO open_sn__country_iso VALUES ('br','Brazil');
INSERT INTO open_sn__country_iso VALUES ('io','British Indian Ocean Territory');
INSERT INTO open_sn__country_iso VALUES ('bn','Brunei Darussalam');
INSERT INTO open_sn__country_iso VALUES ('bg','Bulgaria');
INSERT INTO open_sn__country_iso VALUES ('bf','Burkina Faso');
INSERT INTO open_sn__country_iso VALUES ('bi','Burundi');
INSERT INTO open_sn__country_iso VALUES ('kh','Cambodia');
INSERT INTO open_sn__country_iso VALUES ('cm','Cameroon');
INSERT INTO open_sn__country_iso VALUES ('cv','Cape Verde');
INSERT INTO open_sn__country_iso VALUES ('ky','Cayman Islands');
INSERT INTO open_sn__country_iso VALUES ('cf','Central African Republic');
INSERT INTO open_sn__country_iso VALUES ('td','Chad');
INSERT INTO open_sn__country_iso VALUES ('cl','Chile');
INSERT INTO open_sn__country_iso VALUES ('cn','China');
INSERT INTO open_sn__country_iso VALUES ('cx','Christmas Island');
INSERT INTO open_sn__country_iso VALUES ('cc','Cocos (Keeling) Islands');
INSERT INTO open_sn__country_iso VALUES ('co','Colombia');
INSERT INTO open_sn__country_iso VALUES ('km','Comoros');
INSERT INTO open_sn__country_iso VALUES ('cg','Congo');
INSERT INTO open_sn__country_iso VALUES ('cd','Congo, The Democratic Republic Of The');
INSERT INTO open_sn__country_iso VALUES ('ck','Cook Islands');
INSERT INTO open_sn__country_iso VALUES ('cr','Costa Rica');
INSERT INTO open_sn__country_iso VALUES ('ci','Cote D\'ivoire');
INSERT INTO open_sn__country_iso VALUES ('cu','Cuba');
INSERT INTO open_sn__country_iso VALUES ('cy','Cyprus');
INSERT INTO open_sn__country_iso VALUES ('cz','Czech Republic');
INSERT INTO open_sn__country_iso VALUES ('dj','Djibouti');
INSERT INTO open_sn__country_iso VALUES ('dm','Dominica');
INSERT INTO open_sn__country_iso VALUES ('do','Dominican Republic');
INSERT INTO open_sn__country_iso VALUES ('ec','Ecuador');
INSERT INTO open_sn__country_iso VALUES ('eg','Egypt');
INSERT INTO open_sn__country_iso VALUES ('sv','El Salvador');
INSERT INTO open_sn__country_iso VALUES ('gq','Equatorial Guinea');
INSERT INTO open_sn__country_iso VALUES ('er','Eritrea');
INSERT INTO open_sn__country_iso VALUES ('ee','Estonia');
INSERT INTO open_sn__country_iso VALUES ('et','Ethiopia');
INSERT INTO open_sn__country_iso VALUES ('fk','Falkland Islands (Malvinas)');
INSERT INTO open_sn__country_iso VALUES ('fo','Faroe Islands');
INSERT INTO open_sn__country_iso VALUES ('fj','Fiji');
INSERT INTO open_sn__country_iso VALUES ('fi','Finland');
INSERT INTO open_sn__country_iso VALUES ('gf','French Guiana');
INSERT INTO open_sn__country_iso VALUES ('pf','French Polynesia');
INSERT INTO open_sn__country_iso VALUES ('tf','French Southern Territories');
INSERT INTO open_sn__country_iso VALUES ('ga','Gabon');
INSERT INTO open_sn__country_iso VALUES ('gm','Gambia');
INSERT INTO open_sn__country_iso VALUES ('ge','Georgia');
INSERT INTO open_sn__country_iso VALUES ('gh','Ghana');
INSERT INTO open_sn__country_iso VALUES ('gi','Gibraltar');
INSERT INTO open_sn__country_iso VALUES ('gl','Greenland');
INSERT INTO open_sn__country_iso VALUES ('gd','Grenada');
INSERT INTO open_sn__country_iso VALUES ('gp','Guadeloupe');
INSERT INTO open_sn__country_iso VALUES ('gu','Guam');
INSERT INTO open_sn__country_iso VALUES ('gt','Guatemala');
INSERT INTO open_sn__country_iso VALUES ('gn','Guinea');
INSERT INTO open_sn__country_iso VALUES ('gw','Guinea-Bissau');
INSERT INTO open_sn__country_iso VALUES ('gy','Guyana');
INSERT INTO open_sn__country_iso VALUES ('ht','Haiti');
INSERT INTO open_sn__country_iso VALUES ('hm','Heard Island and Mcdonald Islands');
INSERT INTO open_sn__country_iso VALUES ('va','Holy See (Vatican City State)');
INSERT INTO open_sn__country_iso VALUES ('hn','Honduras');
INSERT INTO open_sn__country_iso VALUES ('hk','Hong Kong');
INSERT INTO open_sn__country_iso VALUES ('hu','Hungary');
INSERT INTO open_sn__country_iso VALUES ('is','Iceland');
INSERT INTO open_sn__country_iso VALUES ('in','India');
INSERT INTO open_sn__country_iso VALUES ('id','Indonesia');
INSERT INTO open_sn__country_iso VALUES ('ir','Iran, Islamic Republic Of');
INSERT INTO open_sn__country_iso VALUES ('iq','Iraq');
INSERT INTO open_sn__country_iso VALUES ('il','Israel');
INSERT INTO open_sn__country_iso VALUES ('jm','Jamaica');
INSERT INTO open_sn__country_iso VALUES ('jo','Jordan');
INSERT INTO open_sn__country_iso VALUES ('kz','Kazakhstan');
INSERT INTO open_sn__country_iso VALUES ('ke','Kenya');
INSERT INTO open_sn__country_iso VALUES ('ki','Kiribati');
INSERT INTO open_sn__country_iso VALUES ('kp','Korea, Democratic People\'s Republic Of');
INSERT INTO open_sn__country_iso VALUES ('kr','Korea, Republic Of');
INSERT INTO open_sn__country_iso VALUES ('kw','Kuwait');
INSERT INTO open_sn__country_iso VALUES ('kg','Kyrgyzstan');
INSERT INTO open_sn__country_iso VALUES ('la','Lao People\'s Democratic Republic');
INSERT INTO open_sn__country_iso VALUES ('lv','Latvia');
INSERT INTO open_sn__country_iso VALUES ('lb','Lebanon');
INSERT INTO open_sn__country_iso VALUES ('ls','Lesotho');
INSERT INTO open_sn__country_iso VALUES ('lr','Liberia');
INSERT INTO open_sn__country_iso VALUES ('ly','Libyan Arab Jamahiriya');
INSERT INTO open_sn__country_iso VALUES ('li','Liechtenstein');
INSERT INTO open_sn__country_iso VALUES ('lt','Lithuania');
INSERT INTO open_sn__country_iso VALUES ('lu','Luxembourg');
INSERT INTO open_sn__country_iso VALUES ('mo','Macao');
INSERT INTO open_sn__country_iso VALUES ('mk','Macedonia, The Former Yugoslav Republic Of');
INSERT INTO open_sn__country_iso VALUES ('mg','Madagascar');
INSERT INTO open_sn__country_iso VALUES ('mw','Malawi');
INSERT INTO open_sn__country_iso VALUES ('my','Malaysia');
INSERT INTO open_sn__country_iso VALUES ('mv','Maldives');
INSERT INTO open_sn__country_iso VALUES ('ml','Mali');
INSERT INTO open_sn__country_iso VALUES ('mt','Malta');
INSERT INTO open_sn__country_iso VALUES ('mh','Marshall Islands');
INSERT INTO open_sn__country_iso VALUES ('mq','Martinique');
INSERT INTO open_sn__country_iso VALUES ('mr','Mauritania');
INSERT INTO open_sn__country_iso VALUES ('mu','Mauritius');
INSERT INTO open_sn__country_iso VALUES ('yt','Mayotte');
INSERT INTO open_sn__country_iso VALUES ('mx','Mexico');
INSERT INTO open_sn__country_iso VALUES ('fm','Micronesia, Federated States Of');
INSERT INTO open_sn__country_iso VALUES ('md','Moldova, Republic Of');
INSERT INTO open_sn__country_iso VALUES ('mc','Monaco');
INSERT INTO open_sn__country_iso VALUES ('mn','Mongolia');
INSERT INTO open_sn__country_iso VALUES ('ms','Montserrat');
INSERT INTO open_sn__country_iso VALUES ('ma','Morocco');
INSERT INTO open_sn__country_iso VALUES ('mz','Mozambique');
INSERT INTO open_sn__country_iso VALUES ('mm','Myanmar');
INSERT INTO open_sn__country_iso VALUES ('na','Namibia');
INSERT INTO open_sn__country_iso VALUES ('nr','Nauru');
INSERT INTO open_sn__country_iso VALUES ('np','Nepal');
INSERT INTO open_sn__country_iso VALUES ('an','Netherlands Antilles');
INSERT INTO open_sn__country_iso VALUES ('nc','New Caledonia');
INSERT INTO open_sn__country_iso VALUES ('ni','Nicaragua');
INSERT INTO open_sn__country_iso VALUES ('ne','Niger');
INSERT INTO open_sn__country_iso VALUES ('ng','Nigeria');
INSERT INTO open_sn__country_iso VALUES ('nu','Niue');
INSERT INTO open_sn__country_iso VALUES ('nf','Norfolk Island');
INSERT INTO open_sn__country_iso VALUES ('mp','Northern Mariana Islands');
INSERT INTO open_sn__country_iso VALUES ('om','Oman');
INSERT INTO open_sn__country_iso VALUES ('pk','Pakistan');
INSERT INTO open_sn__country_iso VALUES ('pw','Palau');
INSERT INTO open_sn__country_iso VALUES ('ps','Palestinian Territory, Occupied');
INSERT INTO open_sn__country_iso VALUES ('pa','Panama');
INSERT INTO open_sn__country_iso VALUES ('pg','Papua New Guinea');
INSERT INTO open_sn__country_iso VALUES ('py','Paraguay');
INSERT INTO open_sn__country_iso VALUES ('pe','Peru');
INSERT INTO open_sn__country_iso VALUES ('ph','Philippines');
INSERT INTO open_sn__country_iso VALUES ('pn','Pitcairn');
INSERT INTO open_sn__country_iso VALUES ('pl','Poland');
INSERT INTO open_sn__country_iso VALUES ('pt','Portugal');
INSERT INTO open_sn__country_iso VALUES ('pr','Puerto Rico');
INSERT INTO open_sn__country_iso VALUES ('qa','Qatar');
INSERT INTO open_sn__country_iso VALUES ('re','Reunion');
INSERT INTO open_sn__country_iso VALUES ('ro','Romania');
INSERT INTO open_sn__country_iso VALUES ('ru','Russian Federation');
INSERT INTO open_sn__country_iso VALUES ('rw','Rwanda');
INSERT INTO open_sn__country_iso VALUES ('sh','Saint Helena');
INSERT INTO open_sn__country_iso VALUES ('kn','Saint Kitts and Nevis');
INSERT INTO open_sn__country_iso VALUES ('lc','Saint Lucia');
INSERT INTO open_sn__country_iso VALUES ('pm','Saint Pierre and Miquelon');
INSERT INTO open_sn__country_iso VALUES ('vc','Saint Vincent and The Grenadines');
INSERT INTO open_sn__country_iso VALUES ('ws','Samoa');
INSERT INTO open_sn__country_iso VALUES ('sm','San Marino');
INSERT INTO open_sn__country_iso VALUES ('st','Sao Tome and Principe');
INSERT INTO open_sn__country_iso VALUES ('sa','Saudi Arabia');
INSERT INTO open_sn__country_iso VALUES ('sn','Senegal');
INSERT INTO open_sn__country_iso VALUES ('cs','Serbia and Montenegro');
INSERT INTO open_sn__country_iso VALUES ('sc','Seychelles');
INSERT INTO open_sn__country_iso VALUES ('sl','Sierra Leone');
INSERT INTO open_sn__country_iso VALUES ('sg','Singapore');
INSERT INTO open_sn__country_iso VALUES ('sk','Slovakia');
INSERT INTO open_sn__country_iso VALUES ('si','Slovenia');
INSERT INTO open_sn__country_iso VALUES ('sb','Solomon Islands');
INSERT INTO open_sn__country_iso VALUES ('so','Somalia');
INSERT INTO open_sn__country_iso VALUES ('za','South Africa');
INSERT INTO open_sn__country_iso VALUES ('gs','South Georgia and The South Sandwich Islands');
INSERT INTO open_sn__country_iso VALUES ('lk','Sri Lanka');
INSERT INTO open_sn__country_iso VALUES ('sd','Sudan');
INSERT INTO open_sn__country_iso VALUES ('sr','Suriname');
INSERT INTO open_sn__country_iso VALUES ('sj','Svalbard and Jan Mayen');
INSERT INTO open_sn__country_iso VALUES ('sz','Swaziland');
INSERT INTO open_sn__country_iso VALUES ('sy','Syrian Arab Republic');
INSERT INTO open_sn__country_iso VALUES ('tj','Tajikistan');
INSERT INTO open_sn__country_iso VALUES ('tz','Tanzania, United Republic Of');
INSERT INTO open_sn__country_iso VALUES ('th','Thailand');
INSERT INTO open_sn__country_iso VALUES ('tl','Timor-Leste');
INSERT INTO open_sn__country_iso VALUES ('tg','Togo');
INSERT INTO open_sn__country_iso VALUES ('tk','Tokelau');
INSERT INTO open_sn__country_iso VALUES ('to','Tonga');
INSERT INTO open_sn__country_iso VALUES ('tt','Trinidad and Tobago');
INSERT INTO open_sn__country_iso VALUES ('tn','Tunisia');
INSERT INTO open_sn__country_iso VALUES ('tr','Turkey');
INSERT INTO open_sn__country_iso VALUES ('tm','Turkmenistan');
INSERT INTO open_sn__country_iso VALUES ('tc','Turks and Caicos Islands');
INSERT INTO open_sn__country_iso VALUES ('tv','Tuvalu');
INSERT INTO open_sn__country_iso VALUES ('ug','Uganda');
INSERT INTO open_sn__country_iso VALUES ('ua','Ukraine');
INSERT INTO open_sn__country_iso VALUES ('ae','United Arab Emirates');
INSERT INTO open_sn__country_iso VALUES ('um','United States Minor Outlying Islands');
INSERT INTO open_sn__country_iso VALUES ('uy','Uruguay');
INSERT INTO open_sn__country_iso VALUES ('uz','Uzbekistan');
INSERT INTO open_sn__country_iso VALUES ('vu','Vanuatu');
INSERT INTO open_sn__country_iso VALUES ('ve','Venezuela');
INSERT INTO open_sn__country_iso VALUES ('vn','Viet Nam');
INSERT INTO open_sn__country_iso VALUES ('vg','Virgin Islands, British');
INSERT INTO open_sn__country_iso VALUES ('vi','Virgin Islands, U.S.');
INSERT INTO open_sn__country_iso VALUES ('wf','Wallis and Futuna');
INSERT INTO open_sn__country_iso VALUES ('eh','Western Sahara');
INSERT INTO open_sn__country_iso VALUES ('ye','Yemen');
INSERT INTO open_sn__country_iso VALUES ('zm','Zambia');
INSERT INTO open_sn__country_iso VALUES ('zw','Zimbabwe');

CREATE TABLE open_sn__state_province (
	id INTEGER NOT NULL PRIMARY KEY ,
	iso_abbv CHAR(2) NOT NULL REFERENCES open_sn__country_iso(iso_abbv) ,
	name VARCHAR(32) NOT NULL UNIQUE
);
CREATE TABLE open_sn__city (
	id INTEGER NOT NULL PRIMARY KEY ,
	state_province_id INTEGER NOT NULL REFERENCES open_sn__state_province(id),
	name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE open_sn__education_level (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(64) NOT NULL UNIQUE
);
INSERT INTO open_sn__education_level VALUES ( 10 , 'High School' );
INSERT INTO open_sn__education_level VALUES ( 11 , 'Some College' );
INSERT INTO open_sn__education_level VALUES ( 12 , 'In College- BA' );
INSERT INTO open_sn__education_level VALUES ( 13 , 'College Graduate- BA' );
INSERT INTO open_sn__education_level VALUES ( 14 , 'In Graduate / Professional School' );
INSERT INTO open_sn__education_level VALUES ( 15 , 'Graduate / Professional School- Completed' );
INSERT INTO open_sn__education_level VALUES ( 16 , 'Post Graduate Work' );


CREATE TABLE open_sn__education_degree_type (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__education_degree_type VALUES ( 10 , 'Diploma' );
INSERT INTO open_sn__education_degree_type VALUES ( 11 , 'BA' );
INSERT INTO open_sn__education_degree_type VALUES ( 12 , 'BS' );
INSERT INTO open_sn__education_degree_type VALUES ( 13 , 'MFA' );
INSERT INTO open_sn__education_degree_type VALUES ( 14 , 'MA' );
INSERT INTO open_sn__education_degree_type VALUES ( 15 , 'MS' );
INSERT INTO open_sn__education_degree_type VALUES ( 16 , 'PHD' );
INSERT INTO open_sn__education_degree_type VALUES ( 17 , 'JD' );
INSERT INTO open_sn__education_degree_type VALUES ( 18 , 'MD' );
INSERT INTO open_sn__education_degree_type VALUES ( 19 , 'Certificate' );
INSERT INTO open_sn__education_degree_type VALUES ( 20 , 'MBA' );

CREATE TABLE open_sn__education_attendance_status (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL UNIQUE
);
INSERT INTO open_sn__education_attendance_status VALUES ( 10 , 'Attending' );
INSERT INTO open_sn__education_attendance_status VALUES ( 11 , 'Attended' );
INSERT INTO open_sn__education_attendance_status VALUES ( 12 , 'Attended+Graduated' );


CREATE TABLE open_sn__work_experience_company (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL,
	city_id INTEGER NOT NULL REFERENCES open_sn__city(id)
);


CREATE TABLE open_sn__education_school (
	id INTEGER NOT NULL PRIMARY KEY ,
	name VARCHAR(32) NOT NULL,
	city_id INTEGER NOT NULL REFERENCES open_sn__city(id) 
);



CREATE TABLE open_sn__profile (
	id BIGSERIAL PRIMARY KEY NOT NULL , 
	basic_gender_id INTEGER REFERENCES open_sn__gender(id),
	basic_age INTEGER ,
	basic_birthdate_year INTEGER ,
	basic_birthdate_month INTEGER ,
	basic_birthdate_day INTEGER ,
	basic_ethnicity_id INTEGER REFERENCES open_sn__ethnicity(id),
	basic_hair_id INTEGER REFERENCES open_sn__hair(id),
	basic_eyes_id INTEGER REFERENCES open_sn__eyes(id),
	basic_height VARCHAR(5),
	basic_height_units_id INTEGER REFERENCES open_sn__height_units(id),
	basic_weight VARCHAR(5),
	basic_weight_units_id INTEGER REFERENCES open_sn__weight_units(id),
	basic_zodiac_id INTEGER REFERENCES open_sn__zodiac(id),
	relationships_relationship_status_id INTEGER REFERENCES open_sn__relationship_status(id),
	relationships_sexual_orientation_id INTEGER REFERENCES open_sn__sexual_orientation(id),
	relationships_children_id INTEGER REFERENCES open_sn__children(id),
	beliefs_religion_id INTEGER REFERENCES open_sn__religion(id),
	habits_alcohol_self_id INTEGER REFERENCES open_sn__alcohol(id),
	habits_alcohol_other_id INTEGER REFERENCES open_sn__alcohol(id),
	habits_tobacco_self_id INTEGER REFERENCES open_sn__tobacco(id),
	habits_tobacco_other_id INTEGER REFERENCES open_sn__tobacco(id),
	habits_drugs_self_id INTEGER REFERENCES open_sn__drugs(id),
	habits_drugs_other_id INTEGER REFERENCES open_sn__drugs(id),
	habits_diet_id INTEGER REFERENCES open_sn__diet(id),
	education_education_level_id INTEGER REFERENCES open_sn__education_level(id),
	location_current_city_name VARCHAR(32),
	location_current_postal_code VARCHAR(11),
	location_current_country_iso CHAR(2) REFERENCES open_sn__country_iso(iso_abbv),	
	location_current_state_province_id INTEGER REFERENCES open_sn__state_province(id),
	location_hometown_city_name VARCHAR(32),
	location_hometown_postal_code VARCHAR(11),
	location_hometown_country_iso CHAR(2) REFERENCES open_sn__country_iso(iso_abbv),	
	location_hometown_state_province_id INTEGER REFERENCES open_sn__state_province(id)
);

CREATE TABLE open_sn__profile_text (
	profile_id BIGINT NOT NULL PRIMARY KEY REFERENCES open_sn__profile(id),
	interests_general TEXT ,
	interests_music TEXT ,
	interests_movies TEXT ,
	interests_television TEXT ,
	interests_books TEXT ,
	interests_heroes TEXT ,
	interests_affiliations TEXT ,
	interests_dislikes TEXT ,
	basic_about_me TEXT ,
	basic_who_i_want_to_meet TEXT 
);

CREATE TABLE open_sn__profile_education (
	profile_id BIGINT NOT NULL PRIMARY KEY REFERENCES open_sn__profile(id),
	school_id INTEGER NOT NULL REFERENCES open_sn__education_school(id),
	attendance_start DATE ,
	attendance_finish DATE ,
	attendance_status_id INTEGER NOT NULL REFERENCES open_sn__education_attendance_status(id),
	degree_type_id INTEGER NOT NULL REFERENCES open_sn__education_degree_type(id)
);

CREATE TABLE open_sn__profile_work_experience (
	profile_id BIGINT NOT NULL PRIMARY KEY REFERENCES open_sn__profile(id),
	company_id INTEGER NOT NULL REFERENCES open_sn__work_experience_company(id),
	employment_start DATE ,
	employment_finish DATE ,
	position VARCHAR(32)
);

