create table master_employer (
	emp_id int auto_increment primary key,
	user_id int,
	foreign key (user_id) references user(user_id)
)engine=myisam;


alter table user add column emp_id int not null default -1 references master_employer(emp_id);


create table user_permission (
	id int auto_increment primary key,
	user_id int not null,
	data_item_type_id int not null,
	can_add boolean default 0,
	can_edit boolean default 0,
	can_delete boolean default 0,
	can_activate boolean default 0,
	can_grant_permission boolean default 0,
	date_created DateTime default current_timestamp,
    date_modified DateTime default current_timestamp on update current_timestamp,
foreign key (user_id) references user(user_id),
foreign key (data_item_type_id) references data_item_type(data_item_type_id)
)engine=myisam;

alter table company add column emp_id int not null references master_employer(emp_id);

create table company_branch (
	branch_id int auto_increment primary key,
	branch_name varchar(120) not null,
	company_id int not null,
	entered_by int not null,
	modified_by int,
	is_active boolean default 1,
	is_default boolean default 0,
	address text,
	state varchar(50),
	city varchar(50),
	zip varchar(16),
	phone varchar(40),
	notes text,
	date_created DateTime default current_timestamp,
    date_modified DateTime default current_timestamp on update current_timestamp,
foreign key (company_id) references company(company_id),
foreign key (entered_by) references user(user_id),
foreign key (modified_by) references user(user_id)
)engine=myisam;

create table user_company (
	user_id int not null,
	company_id int not null,
foreign key (company_id) references company(company_id),
foreign key (user_id) references user(user_id)
)engine=myisam;

create table user_company_branch (
	user_id int not null,
	company_id int not null,
	branch_id int not null,
foreign key (company_id) references company(company_id),
foreign key (user_id) references user(user_id),
foreign key (branch_id) references company_branch(branch_id)
)engine=myisam;

alter table joborder add column branch_id int not null references company_branch(branch_id);

insert into data_item_type (data_item_type_id,short_description) values (500, 'Company Branch'),(600, 'Users');

alter table company add column modified_by int references user(user_id);

ALTER TABLE company MODIFY COLUMN entered_by INT,ADD CONSTRAINT fk_entered_by FOREIGN KEY (entered_by) REFERENCES user(user_id);

alter table company add column is_active boolean default 1;

alter table company modify column date_created DateTime default current_timestamp;


alter table company change column address addressLine1 text;

alter table company add column addressLine2 text;

alter table company modify column date_modified DateTime default current_timestamp on update current_timestamp;

ALTER TABLE user_permission ADD COLUMN can_view boolean DEFAULT 1;

ALTER TABLE company ADD COLUMN is_branch_managed boolean default false;


alter table company_branch change column address addressLine1 text;

alter table company_branch add column addressLine2 text;

alter table company add column logo_name varchar(100);





Migration queries 

INSERT INTO master_employer (user_id) values (1354); //1354 should be replaced by bhagi sir 's id
//emp_id should be bhagi sir 's emp_id
UPDATE user SET emp_id=6  WHERE user_id  not in (SELECT user_id from master_employer me);








alter table candidate_employer_profile add column emp_id int;

 update candidate_employer_profile cep inner join master_employer as me on me.user_id= cep.entered_by set cep.emp_id=me.emp_id;

 update candidate_employer_profile cep inner join user as u on u.user_id= cep.entered_by set cep.emp_id=u.emp_id where cep.emp_id is null;

 alter table candidate_employer_profile modify column emp_id int not null;

 update candidate_contact cc inner join master_employer as me on me.user_id= cc.created_by set cc.employer_id=me.emp_id;

 update candidate_contact cc inner join user as u on u.user_id = cc.created_by set cc.employer_id=u.emp_id where cc.employer_id is null;

UPDATE candidate_contact SET employer_id = null WHERE is_primary =1

UPDATE user SET emp_id=6  WHERE user_id  not in (SELECT user_id from master_employer me);

UPDATE company  SET emp_id=6;


INSERT INTO company_branch (company_id, branch_name, entered_by, addressLine1, addressLine2, city, state, zip, phone, notes, is_internal)
SELECT company_id, name, entered_by, addressLine1, addressLine2, city, state, zip, phone1, notes, 1
FROM company WHERE emp_id =6;




UPDATE joborder j
SET branch_id = (
    SELECT cb.branch_id
    FROM company_branch cb
    WHERE cb.company_id = j.company_id limit 1
)
WHERE j.company_id IN (SELECT company_id FROM company_branch);



INSERT INTO user_company (user_id,company_id)
SELECT user_id, -100
FROM user WHERE user_id  not in (SELECT user_id from master_employer me) and emp_id = 6;

INSERT INTO user_company_branch (company_id, branch_id, user_id)
SELECT c.company_id, -100, u.user_id
FROM company c
CROSS JOIN user u WHERE u.user_id  not in (SELECT user_id from master_employer me) and u.emp_id =6;


UPDATE company SET is_active=0 where company_id=1




INSERT INTO user_permission (user_id,data_item_type_id,can_view,can_add,can_edit,can_delete,can_activate,can_grant_permission)
SELECT user_id,100,true,true,true,false,false,false
FROM user WHERE user_id  not in (SELECT user_id from master_employer me) and emp_id =6;


INSERT INTO user_permission (user_id,data_item_type_id,can_view,can_add,can_edit,can_delete,can_activate,can_grant_permission)
SELECT user_id,200,true,true,true,false,false,false
FROM user WHERE user_id  not in (SELECT user_id from master_employer me) and emp_id =6;


INSERT INTO user_permission (user_id,data_item_type_id,can_view,can_add,can_edit,can_delete,can_activate,can_grant_permission)
SELECT user_id,400,true,true,true,false,true,false
FROM user WHERE user_id  not in (SELECT user_id from master_employer me) and emp_id =6;


INSERT INTO user_permission (user_id,data_item_type_id,can_view,can_add,can_edit,can_delete,can_activate,can_grant_permission)
SELECT user_id,500,true,true,true,false,true,false
FROM user WHERE user_id  not in (SELECT user_id from master_employer me) and emp_id =6;

DROP VIEW IF EXISTS  dashboard_view;

CREATE VIEW `dashboard_view` AS
select
    `staging-jobeze`.`job_temp`.`count` AS `total_recruited`,
    `j`.`date_created` AS `date_created`,
    `j`.`date_modified` AS `date_modified`,
    `j`.`joborder_id` AS `joborder_id`,
    `j`.`title` AS `title`,
    `j`.`description` AS `description`,
    `j`.`type` AS `type`,
    `j`.`duration` AS `duration`,
    `j`.`openings` AS `openings`,
    `j`.`state` AS `state`,
    `j`.`city` AS `city`,
    `j`.`openings_available` AS `openings_available`,
    `j`.`min_experience` AS `min_experience`,
    `j`.`max_experience` AS `max_experience`,
    `j`.`cover_letter_required` AS `cover_letter_required`,
    `j`.`questionnaire_required` AS `questionnaire_required`,
    `c`.`name` AS `name`,
    `c`.`logo` AS `logo`,
    `ex`.`value` AS `role`,
    `wm`.`workmode` AS `workmode`,
    `wm`.`id` AS `workmode_id`,
    NULL AS `redirect_link`,
    `staging-jobeze`.`skills`.`skill` AS `skill`,
    `c`.`company_id` AS `company_id`
from
    ((((((`staging-jobeze`.`joborder` `j`
join `staging-jobeze`.`extra_field` `ex` on
    ((`j`.`joborder_id` = `ex`.`data_item_id`)))
join `staging-jobeze`.`company` `c` on
    ((`j`.`company_id` = `c`.`company_id`)))
join `staging-jobeze`.`job_temp` on
    ((`j`.`joborder_id` = `staging-jobeze`.`job_temp`.`joborder_id`)))
left join `staging-jobeze`.`job_skills` on
    ((`j`.`joborder_id` = `staging-jobeze`.`job_skills`.`joborder_id`)))
left join `staging-jobeze`.`skills` on
    ((`staging-jobeze`.`job_skills`.`skill_id` = `staging-jobeze`.`skills`.`id`)))
left join `staging-jobeze`.`workmodes` `wm` on
    ((`j`.`workmode_id` = `wm`.`id`)))
where
    ((`j`.`public` = 1)
        and (`c`.`is_active` = 1)
            and (`ex`.`field_name` = 'Category')
                and (`ex`.`data_item_type` = 400)
                    and (`j`.`status` = 'Active'));