--
-- ### Advanced Selection checks
-- 
-- 
--

drop if exists table sysmt_user;
create table sysmt_user(uid int not null,
       dbuser string(20),
       username string(20),
       userdesc string(1000),
       userright string(10),
       usermail string(100));


drop if exists table sysmt_system;
create table sysmt_system(sysid int not null,
       sid string(20),
       category string(10),
       sysdesc string(1000),
       pid int,
       systype string(10),
       mtinfo string(300),
       platform string(10),
       status string(10),
       saptype string(10),
       vip string(20),
       sysnr string(10),
       scs string(10),
       javaport string(10),
       smdid string(10),
       zoneA string(20),
       zoneB string(20),
       partA string(20),
       partB string(20),
       partC string(20),
       partD string(20),
       restartzones string(100),
       created datetime default sysdate,
       dbtype string(10),
       arch string(10),
       nfsvip string(30),
       vipip string(20),
       vipif string(20),
       dbversion string(20),
       sharedfs string(300),
       parthalog string(300),
       slaid int,
       sapversion string(20),
       sdbtype string(10),
       dbvip string(20),
       dbvA string(20),
       dbvB string(20));

-- project
drop if exists table sysmt_project;
create table sysmt_project(pid int not null,
       prjname string(100),
       prjdesc string(1000),
       prjmail string(100));

-- project 2 group
drop if exists table sysmt_prj2bgrp;
create table sysmt_prj2bgrp(pid int not null,
       bgid int not null);

-- user 2 group
drop if exists table sysmt_usr2bgrp;
create table sysmt_usr2bgrp(uid int not null,
       bgid int not null);


insert into sysmt_user ( uid, dbuser, username ) values ( 100, 'hugo', 'Hugo Habicht');
insert into sysmt_system ( sysid, sid, pid, sdbtype ) values ( 100, 'ABC', 100, 'NOSDB');
insert into sysmt_system ( sysid, sid, pid, sdbtype ) values ( 101, 'XYZ', 100, 'NOSDB');
insert into sysmt_system ( sysid, sid, pid, sdbtype ) values ( 102, 'WWW', 100, 'NOSDB');
insert into sysmt_project ( pid, prjname ) values ( 100, 'X-Project');
insert into sysmt_prj2bgrp values ( 100, 100 );
insert into sysmt_usr2bgrp values ( 100, 100 );
 
drop if exists view sysmt_usrview;
create view sysmt_usrview as
select
 u.uid as uid,
 u.dbuser as dbuser,
 u.username as username,
 u.userright as userright,
 (select count( distinct s.sid) from
        sysmt_system s,
        sysmt_prj2bgrp p2g,
        sysmt_usr2bgrp u2g
  where ( ( p2g.pid = s.pid and u2g.bgid = p2g.bgid )  and u2g.uid = u.uid ) order by s.sid) as numsys,
 (select count( distinct p.pid) from
        sysmt_project p,
        sysmt_prj2bgrp p2g,
        sysmt_usr2bgrp u2g
  where ( ( p2g.pid = p.pid and u2g.bgid = p2g.bgid ) and u2g.uid = u.uid ) order by p.pid) as numprj
from
 sysmt_user u;

select * from sysmt_usrview;
