/*--------------------------------------------------------------------------
----------------------------------------------------------------------------
-- Upgrade from PCv1
--
-- This script should be run after all other scripts. It will read all the
-- V1 security info and upgrade to V2 security model.
--
----------------------------------------------------------------------------
--------------------------------------------------------------------------*/

declare @AdminGrpGUID       as uniqueidentifier
declare @ProjMgrGrpGUID     as uniqueidentifier
declare @TeamMemberGrpGUID  as uniqueidentifier
declare @ResMgrGrpGUID      as uniqueidentifier
declare @ExecutivesGrpGUID  as uniqueidentifier

select @AdminGrpGUID      = WSEC_GRP_GUID from MSP_WEB_SECURITY_GROUPS where WSEC_GRP_ID=1
select @ExecutivesGrpGUID = WSEC_GRP_GUID from MSP_WEB_SECURITY_GROUPS where WSEC_GRP_ID=2
select @ProjMgrGrpGUID    = WSEC_GRP_GUID from MSP_WEB_SECURITY_GROUPS where WSEC_GRP_ID=4
select @ResMgrGrpGUID     = WSEC_GRP_GUID from MSP_WEB_SECURITY_GROUPS where WSEC_GRP_ID=5
select @TeamMemberGrpGUID = WSEC_GRP_GUID from MSP_WEB_SECURITY_GROUPS where WSEC_GRP_ID=7

--
-- Set the enterprise features correctly according to settings in MSP_WEB_ADMIN
-- 1000 -- The enterprise feature ID
--  504 -- View Enterprise Resource View
--  505 -- View Vision
--  506 -- View Models
--
declare @fEntAllow as tinyint
declare @fEntDeny  as tinyint
select @fEntAllow = WADMIN_ENABLE_ENTERPRISE from MSP_WEB_ADMIN
set @fEntAllow = CASE WHEN (@fEntAllow = 0) THEN 0 ELSE 1 END 
set @fEntDeny  = CASE WHEN (@fEntAllow = 0) THEN 1 ELSE 0 END
update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@fEntAllow, WSEC_DENY=@fEntDeny where WSEC_FEA_ACT_ID in ( select WSEC_FEA_ACT_ID from MSP_WEB_SECURITY_FEATURES_ACTIONS where WSEC_FEA_ACT_ID=1000 or WSEC_FEA_ACT_PARENT=1000 )
update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@fEntAllow, WSEC_DENY=@fEntDeny where WSEC_FEA_ACT_ID=504
update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@fEntAllow, WSEC_DENY=@fEntDeny where WSEC_FEA_ACT_ID=505
update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@fEntAllow, WSEC_DENY=@fEntDeny where WSEC_FEA_ACT_ID=506

--
-- Upgrade for WADMIN_NEW_ACCOUNT_PRIVILEGE field in MSP_WEB_ADMIN into MSP_WEB_SECURITY_ORG_PERMISSIONS
-- Permission: 901 -- Create Accounts from Project
--             902 -- Create Manager Accounts from Project
--             903 -- Create Accounts when Delegating Tasks
--             904 -- Create Acccounts when Requesting Status Reports
--
declare @iPrivilege as int
declare @NewAcct1Allow as tinyint, @NewAcct1Deny as tinyint
declare @NewAcct2Allow as tinyint, @NewAcct2Deny as tinyint

select @iPrivilege = WADMIN_NEW_ACCOUNT_PRIVILEGE from MSP_WEB_ADMIN
set @NewAcct1Allow = CASE WHEN (@iPrivilege & 1 != 0) THEN 1 ELSE 0 END
set @NewAcct1Deny  = CASE WHEN (@iPrivilege & 1 != 0) THEN 0 ELSE 1 END
set @NewAcct2Allow = CASE WHEN (@iPrivilege & 2 != 0) THEN 1 ELSE 0 END
set @NewAcct2Deny  = CASE WHEN (@iPrivilege & 2 != 0) THEN 0 ELSE 1 END

Update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@NewAcct1Allow, WSEC_DENY=@NewAcct1Deny where WSEC_FEA_ACT_ID in (901,902,904)
Update MSP_WEB_SECURITY_ORG_PERMISSIONS set WSEC_ALLOW=@NewAcct2Allow, WSEC_DENY=@NewAcct2Deny where WSEC_FEA_ACT_ID = 903

--
-- When upgrade, all the V1 resouces should have: WRES_CAN_LOGIN=1, WRES_COUNT_LICENSE=1
-- Their default values are: 0.
--
Update MSP_WEB_RESOURCES set WRES_CAN_LOGIN=1, WRES_COUNT_LICENSE=1


--
-- Insert self-record into MSP_WEB_SECURITY_GROUP_MEMBERS (Administrator's record should have already been added)
--
insert into MSP_WEB_SECURITY_GROUP_MEMBERS select WRES_GUID, WRES_GUID from MSP_WEB_RESOURCES where WRES_ID<>1


--
--
-- In PCv1, we have 4 pre-installed categories: Team Member(1), Project Manager(2), Resource Manager(3), Executive Manager(4)
--          from PCv1 UI, users cannot delete Team Member and Project Manager category.
--
-- Upgrade strategy: 
-- 1. Map categories to groups
--   Map Category "Team Member" (v1) --> Group "Team Member" (v2)
--   Map Category "Project Manager" (v1) --> Group "Project Manager" (v2)
--   Map Category "Resource Manager" (v1)--> Group "Resource Manager" (v2)
--   Map Category "Executive Managers"(v1)-->Group "Executives" (v2)
--
-- 2. In MSP_WEB_RESOURCES, we have WRES_IS_MANAGER and WRES_IS_ADMIN fields.
--   In PC2000, 
--   if a resource is a Admin, the 2 flags are: WRES_IS_MANAGER = 1, and WRES_IS_ADMIN = 1
--   if a resource is a Project Manager, the 2 flags are: WRES_IS_MANAGER = 1, and WRES_IS_ADMIN = 0
--   if a resource only, the 2 flags are: WRES_IS_MANAGER = 0, and WRES_IS_ADMIN = 0
--
-- 3. If a user is an Administrator in PC2000, he will be added into "Administrators" group only.
--
--

--
-- Upgrade "Administrators"
--
insert into MSP_WEB_SECURITY_GROUP_MEMBERS (WSEC_GRP_GUID, WRES_GUID)
(
  select @AdminGrpGUID, WRES_GUID 
    from MSP_WEB_RESOURCES 
   where WRES_ID <> 1
     and WRES_IS_MANAGER <> 0
     and WRES_IS_ADMIN <> 0
)

--
-- Upgrade "Team Member" (1)
--
insert into MSP_WEB_SECURITY_GROUP_MEMBERS (WSEC_GRP_GUID, WRES_GUID)
(
  select @TeamMemberGrpGUID, WRES_GUID 
    from MSP_WEB_RESOURCES 
   where WRES_ID <> 1
     and WRES_ID in 
         (
           select R.WRES_ID
             from MSP_WEB_RESOURCES_CATEGORIES RC,
                  MSP_WEB_RESOURCES R 
            where RC.WCAT_ID=1
              and RC.WRES_ID=R.WRES_ID
              and R.WRES_IS_ADMIN=0

           UNION

           select WRES_ID from MSP_WEB_RESOURCES where WRES_IS_MANAGER=0 and WRES_IS_ADMIN=0
         )
)

--
-- Upgrade "Project Manager" (2)
--
insert into MSP_WEB_SECURITY_GROUP_MEMBERS (WSEC_GRP_GUID, WRES_GUID)
(
   select @ProjMgrGrpGUID, WRES_GUID 
     from MSP_WEB_RESOURCES 
    where WRES_ID <> 1
      and WRES_ID in 
          (
            select R.WRES_ID
              from MSP_WEB_RESOURCES_CATEGORIES RC,
                   MSP_WEB_RESOURCES R 
             where RC.WCAT_ID=2
               and RC.WRES_ID=R.WRES_ID
               and R.WRES_IS_ADMIN=0

            UNION

            select WRES_ID from MSP_WEB_RESOURCES where WRES_IS_MANAGER<>0 and WRES_IS_ADMIN=0
          )
)

-- Upgrade "Resource Manager" (3)
insert into MSP_WEB_SECURITY_GROUP_MEMBERS (WSEC_GRP_GUID, WRES_GUID)
(
   select @ResMgrGrpGUID, WRES_GUID 
     from MSP_WEB_RESOURCES 
    where WRES_ID <> 1
      and WRES_ID in
          (
            select R.WRES_ID
              from MSP_WEB_RESOURCES_CATEGORIES RC,
                   MSP_WEB_RESOURCES R 
             where RC.WCAT_ID=3
               and RC.WRES_ID=R.WRES_ID
               and R.WRES_IS_ADMIN=0
          )
)

-- Upgrade "Executive" (4)
insert into MSP_WEB_SECURITY_GROUP_MEMBERS (WSEC_GRP_GUID, WRES_GUID)
(
   select @ExecutivesGrpGUID, WRES_GUID 
     from MSP_WEB_RESOURCES_CATEGORIES RC,
          MSP_WEB_RESOURCES R 
    where RC.WCAT_ID=4
      and RC.WRES_ID=R.WRES_ID
      and R.WRES_ID <> 1
      and R.WRES_IS_ADMIN=0
)

----------------------------------------------------------------
-- Upgrade other categories (v1) to Groups (v2)
----------------------------------------------------------------
declare @NewGrpGUID        uniqueidentifier
declare @iV1CatID          int
declare @sV1CatName        nvarchar(255)
declare @sV1CatDesc        nvarchar(255)
declare @fV1AllProjects    tinyint
declare @fV1AllTeamAssign  tinyint
declare @iNewCatID         int

set @iNewCatID = 100

set IDENTITY_INSERT MSP_WEB_SECURITY_CATEGORIES ON

DECLARE cur_v1Categories CURSOR FOR 
select WCAT_ID, WCAT_NAME, WCAT_DESCRIPTION, WCAT_INCL_ALL_PROJECTS, WCAT_INCL_TEAMASSIGN_PROJECTS
  from MSP_WEB_VIEW_CATEGORIES 
 where WCAT_ID not in (1,2,3,4)
 order by WCAT_ID

OPEN cur_v1Categories
FETCH NEXT FROM cur_v1Categories INTO @iV1CatID, @sV1CatName, @sV1CatDesc, @fV1AllProjects, @fV1AllTeamAssign
WHILE (@@FETCH_STATUS = 0)
BEGIN
  --
  -- Create a Category (v2) for it.
  --
  set @iNewCatID = @iNewCatID + 1
  insert into MSP_WEB_SECURITY_CATEGORIES (WSEC_CAT_ID, WSEC_CAT_NAME, WSEC_CAT_DESC) values( @iNewCatID, @sV1CatName, @sV1CatDesc )
  print 'Insert New Category :' + STR(@iNewCatID)

  --
  -- Add Category rules
  --
  if (@fV1AllTeamAssign<>0)
  begin
    insert into MSP_WEB_SECURITY_CATEGORY_RULES values(@iNewCatID, 1, 3)  -- all team assign projects
  end

  if (@fV1AllProjects<>0)
    begin
      insert into MSP_WEB_SECURITY_CATEGORY_RULES values(@iNewCatID, 1, 0)  -- all current and future projects
    end
  else
    begin
      insert into MSP_WEB_SECURITY_CATEGORY_RULES values(@iNewCatID, 1, 1)  -- selected projects

      -- Add Category Objects
      insert into MSP_WEB_SECURITY_CATEGORY_OBJECTS (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_ID)
      (
        select @iNewCatID, 1, WPROJ_ID from MSP_WEB_VIEW_PROJECTCATEGORIES where WCAT_ID=@iV1CatID
      )
    end

  --
  -- Add views for the category
  --
  insert into MSP_WEB_SECURITY_CATEGORY_OBJECTS (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_ID)
  (
    select @iNewCatID, 3, WVIEW_ID from MSP_WEB_VIEW_REPORTCATEGORIES where WCAT_ID=@iV1CatID
  )

  --
  -- Resources for Assignment View. 
  -- !!! CANNOT UPGRADE MSP_WEB_VIEW_RESOURCES !!!
  -- In PCv1, the resources that a user can view are directly associated with user
  -- In PCv2, the resources that a user can view are associated with categories.
  --

  --
  -- Add resources into Category
  --
  insert into MSP_WEB_SECURITY_SP_CAT_RELATIONS (WSEC_SP_GUID, WSEC_CAT_ID)
  (
    select WRES_GUID, @iNewCatID
      from MSP_WEB_RESOURCES_CATEGORIES RC,
           MSP_WEB_RESOURCES R
     where WCAT_ID=@iV1CatID
       and RC.WRES_ID=R.WRES_ID
  )

  -- **
  -- Set default permissions for the upgraded category later in a batch
  -- At least the same permissions for "Team Members" group on "My Tasks" category
  --

  -- fetch next record of the old categories
  FETCH NEXT FROM cur_v1Categories INTO @iV1CatID, @sV1CatName, @sV1CatDesc, @fV1AllProjects, @fV1AllTeamAssign
END
CLOSE cur_v1Categories
DEALLOCATE cur_v1Categories

set IDENTITY_INSERT MSP_WEB_SECURITY_CATEGORIES OFF


-- **
-- Now set the default permissions for the upgraded category:
-- At least the same permissions for "Team Members" group on "My Tasks" category
--
insert into MSP_WEB_SECURITY_SP_CAT_PERMISSIONS (WSEC_REL_ID, WSEC_FEA_ACT_ID, WSEC_ALLOW, WSEC_DENY, WSEC_ACCESS)
( -- "See Projects in Project Center"
  select WSEC_REL_ID, 508, 1, 0, 1 from MSP_WEB_SECURITY_SP_CAT_RELATIONS where WSEC_REL_ID > 100
)
insert into MSP_WEB_SECURITY_SP_CAT_PERMISSIONS (WSEC_REL_ID, WSEC_FEA_ACT_ID, WSEC_ALLOW, WSEC_DENY, WSEC_ACCESS)
( -- "See Projects in Project Views"
  select WSEC_REL_ID, 509, 1, 0, 1 from MSP_WEB_SECURITY_SP_CAT_RELATIONS where WSEC_REL_ID > 100
)
insert into MSP_WEB_SECURITY_SP_CAT_PERMISSIONS (WSEC_REL_ID, WSEC_FEA_ACT_ID, WSEC_ALLOW, WSEC_DENY, WSEC_ACCESS)
( -- "View Documents and Issues"
  select WSEC_REL_ID, 512, 1, 0, 1 from MSP_WEB_SECURITY_SP_CAT_RELATIONS where WSEC_REL_ID > 100
)
