##################### NAPLNENI DATABASE MyHOME ##########################
#jednodussi verze
#nesmi byt v souboru dva stejne prikazy nad stejnou tabulkou ale s jinyma hodnotama
##############################################################################
######################## tableDatabase ##############################
##############################################################################

DELETE FROM `MyWeb`.`articlehassubject`;
DELETE FROM `MyWeb`.`subject`;
DELETE FROM `MyWeb`.`file`;
DELETE FROM `MyWeb`.`userhasrole`;
DELETE FROM `MyWeb`.`subjectlevel`;
DELETE FROM `MyWeb`.`comment`;

DELETE FROM `MyWeb`.`role`;
DELETE FROM `MyWeb`.`article`;
DELETE FROM `MyWeb`.`user`;
DELETE FROM `MyWeb`.`state`;
DELETE FROM `MyWeb`.`tabledatabase`;
DELETE FROM `MyWeb`.`namestate`;
DELETE FROM `MyWeb`.`state`;
DELETE FROM `MyWeb`.`typeFile`;

ALTER TABLE state AUTO_INCREMENT = 1;
ALTER TABLE tabledatabase AUTO_INCREMENT = 1;
ALTER TABLE namestate AUTO_INCREMENT = 1;
ALTER TABLE role AUTO_INCREMENT = 1;
ALTER TABLE user AUTO_INCREMENT = 1;
ALTER TABLE userhasrole AUTO_INCREMENT = 1;
ALTER TABLE typeFile AUTO_INCREMENT = 1;
ALTER TABLE File AUTO_INCREMENT = 1;
ALTER TABLE article AUTO_INCREMENT = 1;
ALTER TABLE subject AUTO_INCREMENT = 1;
ALTER TABLE subjectlevel AUTO_INCREMENT = 1;
ALTER TABLE comment AUTO_INCREMENT = 1;

INSERT INTO tabledatabase (id_tableDatabase, tableDatabase_table) VALUE (null,'article'),
(null,'articlehassubject'),
(null,'comment'),
(null,'file'),
(null,'namestate'),
(null,'role'),
(null,'state'),
(null,'state_has_state'),
(null,'subject'),
(null,'subjectlevel'),
(null,'tabledatabase'),
(null,'typefile'),
(null,'user'),
(null,'userhasrole');

INSERT INTO namestate(id_NameState, nameState_name)VALUE(NULL,'disabled'),(NULL,'enabled');

insert into state(id_state, state_state, NameState_id_NameState, TableDatabase_id_tableDatabase) VALUE
(null,'stav',1,1),(null,'stav',2,1),
(null,'stav',1,2),(null,'stav',2,2),
(null,'stav',1,3),(null,'stav',2,3),
(null,'stav',1,4),(null,'stav',2,4),
(null,'stav',1,5),(null,'stav',2,5),
(null,'stav',1,6),(null,'stav',2,6),
(null,'stav',1,7),(null,'stav',2,7),
(null,'stav',1,8),(null,'stav',2,8),
(null,'stav',1,9),(null,'stav',2,9),
(null,'stav',1,10),(null,'stav',2,10),
(null,'stav',1,11),(null,'stav',2,11),
(null,'stav',1,12),(null,'stav',2,12),
(null,'stav',1,13),(null,'stav',2,13),
(null,'stav',1,14),(null,'stav',2,14);



insert into role(id_role, role_role, state_id_state) VALUE
(null,'administrator',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'role' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'user',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'role' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'editor',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'role' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
);




insert into user(id_user, user_name, user_userName, user_registrationDateTime, user_passwd, State_id_state) VALUE
(null,'name1','user name1',NOW(),PASSWORD('aaa'),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'user' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'name2','user name2',NOW(),PASSWORD('aaa'),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'user' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'name3','user name3',NOW(),PASSWORD('aaa'),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'user' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
);


insert into userhasrole(user_id_user, role_id_role) VALUE
(1,1),(2,3),(3,3);

insert into typeFile(id_typeFile, typeFile_type) VALUE
(null,'document'),(null,'move'),(null,'picture');

insert into file(id_file, file_path, file_name, file_dateTime, state_id_state, typeFile_id_typeFile,User_id_user) VALUE
(null,'file path1','file_name1',NOW(),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'file' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ),1,1),
(null,'file path2','file_name2',NOW(),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'file' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ),2,2),
(null,'file path3','file_name3',NOW(),
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'file' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ),3,3);


insert into article(id_article, article_text, article_dateTime, user_id_user, state_id_state) VALUE
(null,'article1',NOW(),1,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'article' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'article2',NOW(),2,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'article' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'article3',NOW(),3,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'article' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ));


insert into subject(id_subject, subject_name, State_id_state) VALUE
(null,'subject1',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'subject' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'#enabled
  )
),
(null,'subject2',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'subject' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'subject3',
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'subject' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ));



insert into subjectlevel(id_subjectLevel, subjectLevel_name, State_id_state) VALUE
(null,1,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'SubjectLevel' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'#enabled
  )
),
(null,2,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'SubjectLevel' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,3,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'SubjectLevel' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ));



insert into comment(id_comment, comment_text, comment_dateTime, Article_id_article, State_id_state) VALUE
(null,'comment1',NOW(),1,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'comment' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'#enabled
  )
),
(null,'comment2',NOW(),2,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'comment' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  )
),
(null,'comment3',NOW(),1,
  (
    select
    state.id_state
    from
    state,
    tabledatabase,
    namestate
    where
    state.TableDatabase_id_tableDatabase = tabledatabase.id_tableDatabase AND
    tabledatabase.tableDatabase_table = 'comment' AND
    state.NameState_id_NameState = namestate.id_NameState AND
    namestate.nameState_name = 'enabled'
  ));


insert into articlehassubject(Article_id_article, subject_id_subject, SubjectLevel_id_subjectLevel) VALUE
(1,3,3),(1,2,2),(1,1,1),(2,2,2),(2,1,1),(3,1,1);