use  language;


create table catagory(
	id			int auto_increment primary key,
    name		varchar(255) not null,
    comment		varchar(255)
);
insert into catagory (name) values('general');
insert into catagory (name) values('family');
insert into catagory (name) values('colors');
insert into catagory (name) values('name');
insert into catagory (name) values('unknown');

create table part_of_speech(
	id			int auto_increment primary key,
    name		varchar(255) not null,
    comment		varchar(255)
);
insert into part_of_speech(name) values('noun');
insert into part_of_speech(name) values('verb');
insert into part_of_speech(name) values('adverb');
insert into part_of_speech(name) values('adjective');
insert into part_of_speech(name) values('pronoun');
insert into part_of_speech(name) values('article');
insert into part_of_speech(name) values('letter');
insert into part_of_speech(name) values('phrase');

create table words(
	id			int auto_increment primary key,
    word		varchar(255) CHARACTER SET 'utf8mb4' not null,
    word_latin	varchar(255), 
    meaning		varchar(255) not null,
    part_of_speech	int,
    foreign key (part_of_speech) references part_of_speech(id),
    word_level	int not null, 		/*how difficult is word */
    catagory	int default 0, 		/* default is general */
    foreign key (word_level) references catagory(id),
    gender		enum('M', 'F', 'N'),	/*word gender										*/
    pronounce1	blob,				/*word out loud										*/
    pronounce2	blob,
    pronounce3	blob,
    word_added	datetime,
    comment		varchar(255)
);
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('папа', 'papa', 'father, informal', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('отец', 'father', 'father, formal', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('брат', 'brother', 'brother', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('мама', 'mama', 'mother, informal', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('мать', 'mother', 'mother, formal', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('сестра', 'sister', 'sister', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('Дед', 'grandfather', 'grandfather', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('бабушка', 'grandmother', 'grandmother', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('кузен', 'cousin', 'male cousin', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('кузен', 'cousin', 'female cousin', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('дядя', 'uncle', 'uncle', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('тетя', 'aunt', 'aunt', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('внук', 'grandson', 'grandson', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('внучка', 'granddaughter', 'granddaughter', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'F', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('внук', 'grandchild', 'grandchild', (select id from part_of_speech where name = 'noun'), 1, (select id from catagory where name = 'family'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('красный', 'red', 'color red', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('зеленый', 'green', 'color green', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('белый', 'white', 'color white', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('черный', 'black', 'color black', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('синий', 'blue', 'color blue', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('апельсин', 'orange', 'color orange', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('коричневый', 'brown', 'color brown', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('желтый', 'yellow', 'color yellow', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, catagory, gender, word_added) values ('фиолетовый', 'purple', 'color purple', (select id from part_of_speech where name = 'adjective'), 1, (select id from catagory where name = 'colors'), 'M', now());

insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('А', 'A', 'ah sound', (select id from part_of_speech where name = 'letter'), 1, 'fAther', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Б', 'B', 'b sound', (select id from part_of_speech where name = 'letter'), 3, 'Bad',  now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('В', 'V', 'v sound', (select id from part_of_speech where name = 'letter'), 2, 'Vine', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Г', 'G', 'hard g sound', (select id from part_of_speech where name = 'letter'), 3, 'Go', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Д', 'D', 'd sound', (select id from part_of_speech where name = 'letter'), 3, 'Do', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Е', 'ye', 'yeh sound', (select id from part_of_speech where name = 'letter'), 1, 'YEs', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ё', 'yo', 'yo sound', (select id from part_of_speech where name = 'letter'), 3, 'YOur', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ж', 'zh', 'zh sound', (select id from part_of_speech where name = 'letter'), 3, 'meaSure', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('З', 'Z', 'z sound', (select id from part_of_speech where name = 'letter'), 3, 'Zoo', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('И', 'I', 'short i sound, long e sound', (select id from part_of_speech where name = 'letter'), 3, 'polIce', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Й', 'Y, I, J', 'short y sound', (select id from part_of_speech where name = 'letter'), 3, 'toY', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('К', 'K', 'k sound', (select id from part_of_speech where name = 'letter'), 1, 'Kept', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Л', 'L', 'l sound', (select id from part_of_speech where name = 'letter'), 3, 'Lamp, feeL', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('М', 'M', 'm sound', (select id from part_of_speech where name = 'letter'), 1, 'Map', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Н', 'N', 'n sound', (select id from part_of_speech where name = 'letter'), 2, 'Not', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('О', 'O', 'soft o sound', (select id from part_of_speech where name = 'letter'), 1, 'mOre', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('П', 'P', 'p sound', (select id from part_of_speech where name = 'letter'), 3, 'Pet', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Р', 'R', 'rolled r sound', (select id from part_of_speech where name = 'letter'), 2, 'rolled r', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('С', 'S', 's sound', (select id from part_of_speech where name = 'letter'), 2, 'Set', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Т', 'T', 't sound', (select id from part_of_speech where name = 'letter'), 1, 'Top', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('У', 'U', 'oo sound', (select id from part_of_speech where name = 'letter'), 3, 'tOOl', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ф', 'F', 'f sound', (select id from part_of_speech where name = 'letter'), 3, 'Face', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Х', 'ch', 'soft ch sound', (select id from part_of_speech where name = 'letter'), 2, 'loCH ness', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ц', 'ts', 'ts sound', (select id from part_of_speech where name = 'letter'), 3, 'siTS', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ч', 'ch', 'hard ch sound', (select id from part_of_speech where name = 'letter'), 3, 'CHeck', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ш', 'sh', 'sh sound', (select id from part_of_speech where name = 'letter'), 3, 'SHrimp', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Щ', 'sch', 'longer sh sound', (select id from part_of_speech where name = 'letter'), 3, 'puSH SHips', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ъ', '', 'silent', (select id from part_of_speech where name = 'letter'), 2, 'silent, prevents palatalization of the preceding consonant', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ы', 'R', 'r sound', (select id from part_of_speech where name = 'letter'), 3, 'Her', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ь', '', 'silent', (select id from part_of_speech where name = 'letter'), 2, 'silent, palatalizes the preceding consonant (if phonologically possible)', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Э', 'E', 'short e sound', (select id from part_of_speech where name = 'letter'), 3, 'mEt', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Ю', 'U', 'long u sound', (select id from part_of_speech where name = 'letter'), 3, 'Use', now());
insert into words (word, word_latin, meaning, part_of_speech, word_level, comment, word_added) values ('Я', 'ya', 'ya sound', (select id from part_of_speech where name = 'letter'), 3, 'YArd', now());


create table customerwordstatus(
	id 	int auto_increment primary key,
    wordid	int not null,
    foreign key (wordid) references words(id),
    custid	int not null,
    foreign key (custid) references customer(id),
    firstintroduced datetime,
    attempts	int default 0,
    lastattempt	datetime,
    wrongattempts	int default 0,
    lastwrongattempt datetime
);
alter table customerwordstatus add index (wordid);
alter table customerwordstatus add index (custid);

    