create database testcase ;
use testcase ;

create table tb_office (
    OFFICE_ID           varchar(10) not null, 
    LEVEL_OFFICE_ID     varchar(10) , 
    LEVEL               int
) ;


drop view if exists vw_office_hierachy ;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `vw_office_hierachy` AS 
select `pos`.`OFFICE_ID` AS `office_id`,group_concat(concat('#',`pos`.`LEVEL_OFFICE_ID`,'#') order by `pos`.`LEVEL` ASC separator ',') AS `OFFICE_HIERACHY`,
count(0) AS `CALCULATED_LEVEL`,`pos`.`LEVEL` AS `LEVEL` 
from `tb_office` `pos` 
group by `pos`.`OFFICE_ID` order by `pos`.`LEVEL` desc ;


delete from tb_office ;
insert into tb_office values 
    (   1,    1,  1) , 
    (1101,    1,  2) , 
    (1201,    1,  2) , 
    (1102, 1101,  3) , 
    (1103, 1101,  3) , 
    (1104, 1101,  3) , 
    (1202, 1201,  3) , 
    (1203, 1201,  3) , 
    (1204, 1201,  3) , 
    (   2,    2,  1) , 
    (2101,    2,  2) , 
    (2201,    2,  2) , 
    (2301,    2,  2) , 
    (2102, 2101,  3) , 
    (2103, 2101,  3) , 
    (2104, 2101,  3) , 
    (2202, 2201,  3) , 
    (2203, 2201,  3) , 
    (2204, 2201,  3) , 
    (2302, 2301,  3) , 
    (2303, 2301,  3) , 
    (2304, 2301,  3) , 
    (2305, 2301,  3) , 
    (9999,    1,  1) ,
    (9999,    2,  2) ,
    (9999, 2101,  3) ,
    (9999, 9999,  4)  
;

drop table if exists tb_office_hierachy ;
create table tb_office_hierachy as select * from vw_office_hierachy ;

select * from vw_office_hierachy ;
select * from tb_office_hierachy ;


drop table if exists tb_staff ;
create table tb_staff (staff_id bigint not null, office_id varchar(10) ) ;

delete from tb_staff ;
insert into tb_staff values 
    (5000001,     1) ,
    (5000002,     2) ,
    (5000003,  9999) 
;

drop table if exists tb_user_data ;
create table tb_user_data (staff_id bigint not null, profile_id bigint, office_id varchar(10) ) ;

delete from tb_user_data ;
insert into tb_user_data values 
    (5000001,  2000001, 1104) , 
    (5000002,  2000002, 1204) , 
    (5000003,  2000003, 9999) 
;

drop table if exists tb_user_profile ;
create table tb_user_profile (profile_id bigint) ;

delete from tb_user_profile ;
insert into tb_user_profile values 
    (2000001) , 
    (2000002) , 
    (2000003) 
;



select "Expected Result when using table" ;

select staff.staff_id, cugd.office_id 
from  tb_user_profile ugp 
    inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id) 
    inner join tb_staff staff on
    ( staff.office_id IN ( SELECT office_id FROM tb_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%'))  or (cugd.office_id IS NULL) )
where ugp.profile_id = 2000003 
;


select "Unexpected Result when using view" ;

select staff.staff_id, cugd.office_id 
from  tb_user_profile ugp 
    inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id) 
    inner join tb_staff staff on
    ( staff.office_id IN ( SELECT office_id FROM vw_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%'))  or (cugd.office_id IS NULL) )
where ugp.profile_id = 2000003 
;