/* Delete the tables if they already exist */
drop table if exists person;
drop table if exists worktype;
drop table if exists education;
drop table if exists maritalstatus;
drop table if exists occupation;
drop table if exists familyrole;
drop table if exists ethnicity;
drop table if exists gender;
drop table if exists country;
drop table if exists salary;

PRAGMA foreign_keys = ON;
/* enum tables - to not to allow to enter not approved values*/
create table worktype (
    type text PRIMARY KEY,
    seq int
);

INSERT INTO worktype VALUES ('Private',1);
INSERT INTO worktype VALUES ('Self-emp-not-inc',2);
INSERT INTO worktype VALUES ('Self-emp-inc',3);
INSERT INTO worktype VALUES ('Federal-gov',4);
INSERT INTO worktype VALUES ('Local-gov',5);
INSERT INTO worktype VALUES ('State-gov',6);
INSERT INTO worktype VALUES ('Without-pay',7);
INSERT INTO worktype VALUES ('Never-worked',8);


create table education (
    level text PRIMARY KEY,
    seq int
);

INSERT INTO education VALUES ('Bachelors',1);
INSERT INTO education VALUES ('Some-college',2);
INSERT INTO education VALUES ('11th',3);
INSERT INTO education VALUES ('HS-grad',4);
INSERT INTO education VALUES ('Prof-school',5);
INSERT INTO education VALUES ('Assoc-acdm',6);
INSERT INTO education VALUES ('Assoc-voc',7);
INSERT INTO education VALUES ('9th',8);
INSERT INTO education VALUES ('7th-8th',9);
INSERT INTO education VALUES ('12th',10);
INSERT INTO education VALUES ('Masters',11);
INSERT INTO education VALUES ('1st-4th',12);
INSERT INTO education VALUES ('10th',13);
INSERT INTO education VALUES ('Doctorate',14);
INSERT INTO education VALUES ('5th-6th',15);
INSERT INTO education VALUES ('Preschool',16);


create table maritalstatus (
    status text PRIMARY KEY,
    seq int
);

INSERT INTO maritalstatus VALUES ('Divorced',1);
INSERT INTO maritalstatus VALUES ('Married-civ-spouse',2);
INSERT INTO maritalstatus VALUES ('Never-married',3);
INSERT INTO maritalstatus VALUES ('Separated',4);
INSERT INTO maritalstatus VALUES ('Widowed',5);
INSERT INTO maritalstatus VALUES ('Married-spouse-absent',6);
INSERT INTO maritalstatus VALUES ('Married-AF-spouse',7);


create table occupation (
    area text PRIMARY KEY,
    seq int
);

INSERT INTO occupation VALUES ('Sales',1);
INSERT INTO occupation VALUES ('Tech-support',2);
INSERT INTO occupation VALUES ('Craft-repair',3);
INSERT INTO occupation VALUES ('Other-service',4);
INSERT INTO occupation VALUES ('Exec-managerial',5);
INSERT INTO occupation VALUES ('Prof-specialty',6);
INSERT INTO occupation VALUES ('Handlers-cleaners',7);
INSERT INTO occupation VALUES ('Machine-op-inspct',8);
INSERT INTO occupation VALUES ('Adm-clerical',9);
INSERT INTO occupation VALUES ('Farming-fishing',10);
INSERT INTO occupation VALUES ('Transport-moving',11);
INSERT INTO occupation VALUES ('Priv-house-serv',12);
INSERT INTO occupation VALUES ('Protective-serv',13);
INSERT INTO occupation VALUES ('Armed-Forces',14);


create table familyrole (
    role text PRIMARY KEY,
    seq int
);

INSERT INTO familyrole VALUES ('Wife',1);
INSERT INTO familyrole VALUES ('Own-child',2);
INSERT INTO familyrole VALUES ('Husband',3);
INSERT INTO familyrole VALUES ('Not-in-family',4);
INSERT INTO familyrole VALUES ('Other-relative',5);
INSERT INTO familyrole VALUES ('Unmarried',6);


create table ethnicity (
    race text PRIMARY KEY,
    seq int
);

INSERT INTO ethnicity VALUES ('White',1);
INSERT INTO ethnicity VALUES ('Asian-Pac-Islander',2);
INSERT INTO ethnicity VALUES ('Amer-Indian-Eskimo',3);
INSERT INTO ethnicity VALUES ('Other',4);
INSERT INTO ethnicity VALUES ('Black',5);

create table gender (
    type text PRIMARY KEY,
    seq int
);

INSERT INTO gender VALUES ('Female',1);
INSERT INTO gender VALUES ('Male',2);


create table country (
    name text PRIMARY KEY,
    seq int
);

INSERT INTO country VALUES ('United-States',1);
INSERT INTO country VALUES ('Cambodia',2);
INSERT INTO country VALUES ('England',3);
INSERT INTO country VALUES ('Puerto-Rico',4);
INSERT INTO country VALUES ('Canada',5);

INSERT INTO country VALUES ('Germany',6);
INSERT INTO country VALUES ('Outlying-US(Guam-USVI-etc)',7);
INSERT INTO country VALUES ('India',8);
INSERT INTO country VALUES ('Japan',9);
INSERT INTO country VALUES ('Greece',10);

INSERT INTO country VALUES ('South',11);
INSERT INTO country VALUES ('China',12);
INSERT INTO country VALUES ('Cuba',13);
INSERT INTO country VALUES ('Iran',14);
INSERT INTO country VALUES ('Honduras',15);

INSERT INTO country VALUES ('Philippines',16);
INSERT INTO country VALUES ('Italy',17);
INSERT INTO country VALUES ('Poland',18);
INSERT INTO country VALUES ('Jamaica',19);
INSERT INTO country VALUES ('Vietnam',20);

INSERT INTO country VALUES ('Mexico',21);
INSERT INTO country VALUES ('Portugal',22);
INSERT INTO country VALUES ('Ireland',23);
INSERT INTO country VALUES ('France',24);
INSERT INTO country VALUES ('Dominican-Republic',25);

INSERT INTO country VALUES ('Laos',26);
INSERT INTO country VALUES ('Ecuador',27);
INSERT INTO country VALUES ('Taiwan',28);
INSERT INTO country VALUES ('Haiti',29);
INSERT INTO country VALUES ('Columbia',30);

INSERT INTO country VALUES ('Hungary',31);
INSERT INTO country VALUES ('Guatemala',32);
INSERT INTO country VALUES ('Nicaragua',33);
INSERT INTO country VALUES ('Scotland',34);
INSERT INTO country VALUES ('Thailand',35);

INSERT INTO country VALUES ('Yugoslavia',36);
INSERT INTO country VALUES ('El-Salvador',37);
INSERT INTO country VALUES ('Trinadad&Tobago',38);
INSERT INTO country VALUES ('Peru',39);
INSERT INTO country VALUES ('Hong',40);
INSERT INTO country VALUES ('Holand-Netherlands',41);


create table salary (
    level text PRIMARY KEY,
    seq int
);

INSERT INTO salary VALUES ('>50K',1);
INSERT INTO salary VALUES ('<=50K',2);


create table person(
    age int,
    worktype text REFERENCES worktype (type),
    educationlevel text REFERENCES education (level),
    maritalstatus text REFERENCES maritalstatus (status),
    occupation text REFERENCES occupation (area),
    familyrole text REFERENCES familyrole (role),
    ethnicity text REFERENCES ethnicity (race),
    gender text REFERENCES gender (type),
    capitalgain int,
    capitalloss int, 
    workinhoursperweek int,
    nativecountry text REFERENCES country (name),
    ** text REFERENCES salary (level)
);