CREATE TABLE [dbo].[ca_tenant]
(
[id] [binary] (16) NOT NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[tenant_number] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[service_provider] [int] NULL,
[contact] [binary] (16) NULL,
[logo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fax_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_phone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[location] [binary] (16) NULL,
[inactive] [int] NOT NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_tenant__versi__571A2AEC] DEFAULT ('0'),
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[tenant] [binary] (16) NULL,
[ldap_tenant_group] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[parent] [binary] (16) NULL,
[fkey_group] [binary] (16) NULL,
[subtenants_ok] [int] NULL,
[subtenant_group] [binary] (16) NULL,
[supertenant_group] [binary] (16) NULL,
[relatedtenant_group] [binary] (16) NULL,
[tenant_depth] [int] NULL,
[auto_rep_version] [timestamp] NULL,
[tou_id] [binary] (16) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- =========================================================================
--     Trigger to sync common fields from ca_tenant to usm_tenant_ext
--    when ca_tenant insert operation happens.
--    :only if recored is not exist in usm_tenant_ext
--    (ca_tenant.id == usm_tenant_ext.ca_tenant_uuid)
--  Trigger is enabled when MT option is ON/turned OFF (default OFF)
-- =========================================================================
CREATE TRIGGER usm_trg_ca_tenant_insert ON ca_tenant
AFTER INSERT
AS
BEGIN
    DECLARE @cnt int
    DECLARE @insrt_retcode    int
    DECLARE @ca_tenant_uuid_in varchar(50)
    DECLARE @ca_tenant_uuid_parent varchar(50)
    DECLARE @ca_tenant_name NVARCHAR(300)
    DECLARE @sp int
    DECLARE @subtenants_ok int

    select @cnt = count(i.id)
    from inserted i left join usm_tenant_ext t on (i.id = t.ca_tenant_uuid)
    where t.ca_tenant_uuid is null

    if (@cnt > 0)
    begin
        -- call function to insert into usm_tenant_ext
        -- when multi-tenancy is managed by other SM product (CAF or Service Desk)
        -- USM need to sync the information to usm_tenant_ext table when ca_tenant is created
        print 'INFO: USM CA Tenant Insert Trigger: Multi-tenancy syncing to USM'
        --
        DECLARE ca_tenant_cursor CURSOR FOR
        --select i.id, i.parent, i.name, i.service_provider, i.subtenants_ok
        --from inserted i left join usm_tenant_ext t on (i.id = t.ca_tenant_uuid)
        --where t.ca_tenant_uuid is null
        select sys.fn_varbintohexsubstring(0,i.id,1,0), sys.fn_varbintohexsubstring(0,i.parent,1,0), i.name, i.service_provider, i.subtenants_ok
        from inserted i left join usm_tenant_ext t on (i.id = t.ca_tenant_uuid)
        where t.ca_tenant_uuid is null

        --call procedure to the synching.
        OPEN ca_tenant_cursor

        FETCH NEXT FROM ca_tenant_cursor INTO @ca_tenant_uuid_in,@ca_tenant_uuid_parent,@ca_tenant_name,@sp,@subtenants_ok

        WHILE @@FETCH_STATUS = 0
        BEGIN
            --PRINT 'calling procedure : usm_sp_sync_insert_usm_tenant'
            --PRINT 'procedure : usm_sp_sync_insert_usm_tenant:' + @ca_tenant_uuid_in + @ca_tenant_name+ convert(varchar(10),@sp) + convert(varchar(10),@subtenants_ok)
            
            PRINT 'usm_trg_ca_tenant_insert : ca_tenant_uuid=' + @ca_tenant_uuid_in
            if (@ca_tenant_uuid_parent is null)
                PRINT 'usm_trg_ca_tenant_insert : @ca_tenant_uuid_parent is NULL'
            else
                PRINT 'usm_trg_ca_tenant_insert : @ca_tenant_uuid_parent=' + @ca_tenant_uuid_parent
            PRINT 'usm_trg_ca_tenant_insert : @ca_tenant_name=' + @ca_tenant_name
            PRINT 'usm_trg_ca_tenant_insert : @sp=' + convert(varchar(10),@sp)
            if (@subtenants_ok is null)
                PRINT 'usm_trg_ca_tenant_insert : @subtenants_ok is NULL'
            else
                PRINT 'usm_trg_ca_tenant_insert : @subtenants_ok=' + convert(varchar(10),@subtenants_ok)
            --
            SET @insrt_retcode=0
            EXECUTE @insrt_retcode = usm_sp_sync_insert_usm_tenant @ca_tenant_uuid_in,@sp,@subtenants_ok,@ca_tenant_name,@ca_tenant_uuid_parent,null,null

            PRINT 'usm_trg_ca_tenant_insert : @insrt_retcode=' + convert(varchar(10),@insrt_retcode)
            
            FETCH NEXT FROM ca_tenant_cursor INTO @ca_tenant_uuid_in,@ca_tenant_uuid_parent,@ca_tenant_name,@sp,@subtenants_ok
        END
        CLOSE ca_tenant_cursor
        DEALLOCATE ca_tenant_cursor

    end
END
GO
-- =========================================================================
--     trigger to sync common fields from ca_tenant to usm_tenant_ext
--    when ca_tenant update operation happens.
--    :only if (ca_tenant.id == usm_tenant_ext.ca_tenant_uuid)
--    this means that the tenants between the two has been merged.
-- =========================================================================
CREATE TRIGGER usm_trg_ca_tenant_update ON ca_tenant
AFTER UPDATE
AS
IF EXISTS (SELECT * FROM inserted a JOIN deleted b ON a.id=b.id)
BEGIN
    DECLARE @usm_tenant_id    varchar(50)
    DECLARE @ca_tenant_uuid    varchar(50)
    DECLARE @sp_new    int
    DECLARE @sp_old    int
    DECLARE @inactive_new            int
    DECLARE @inactive_old            int
    DECLARE @subtenants_ok_new        int
    DECLARE @subtenants_ok_old        int
    DECLARE @ca_tenant_parent_uuid_new    varchar(50)
    DECLARE @ca_tenant_parent_uuid_old    varchar(50)
    
    -- DEBUG:
    -- DECLARE @stmt varchar(4000)

    --
    DECLARE ca_tenant_cursor CURSOR FOR
    select t.tenant_id, sys.fn_varbintohexsubstring(0,t.ca_tenant_uuid,1,0), a.service_provider, b.service_provider, a.inactive, b.inactive, a.subtenants_ok, b.subtenants_ok, sys.fn_varbintohexsubstring(0,a.parent,1,0), sys.fn_varbintohexsubstring(0,b.parent,1,0)
    from usm_tenant t, inserted a, deleted b
    where t.ca_tenant_uuid = a.id and a.id=b.id and t.ca_tenant_uuid = b.id

    OPEN ca_tenant_cursor

    FETCH NEXT FROM ca_tenant_cursor
    INTO @usm_tenant_id, @ca_tenant_uuid, @sp_new, @sp_old, @inactive_new, @inactive_old, @subtenants_ok_new, @subtenants_ok_old, @ca_tenant_parent_uuid_new, @ca_tenant_parent_uuid_old

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- PRINT 'calling procedure : usm_sp_sync_update_usm_tenant'
        --
        -- DEBUG:
        -- insert into usm_test_tbl(stmt) values ('[TRIGGER] is calling ... usm_sp_sync_update_usm_tenant')

        execute usm_sp_sync_update_usm_tenant @ca_tenant_uuid, @sp_new, @inactive_new, @subtenants_ok_new, @sp_old, @inactive_old, @subtenants_ok_old

        -- if service provider, then do not call the update
        -- update sync for tenant move
        IF ((@sp_new is null) or (@sp_new = 0))
        BEGIN

            -- DEBUG:
            -- insert into usm_test_tbl(stmt) values ('[TRIGGER] sp_new is null or 0')
    
            if (@ca_tenant_parent_uuid_new is null)    set @ca_tenant_parent_uuid_new = '';
            if (@ca_tenant_parent_uuid_old is null)    set @ca_tenant_parent_uuid_old = '';

            IF (@ca_tenant_parent_uuid_new <> @ca_tenant_parent_uuid_old)
            BEGIN
                -- DEBUG:
                -- insert into usm_test_tbl(stmt) values ('[TRIGGER] is calling ... usm_sp_sync_update_tenant_path')
                execute usm_sp_sync_update_tenant_path @ca_tenant_uuid, @ca_tenant_parent_uuid_new
            END

            -- DEBUG:
            -- set @stmt = 'insert into usm_test_tbl(stmt) values (''CID:'+@ca_tenant_uuid+' --- Tenant ID:'+@usm_tenant_id+' --- Parent New:'+@ca_tenant_parent_uuid_new+'--- Parent Old:'+@ca_tenant_parent_uuid_old+''')'
            -- EXEC(@stmt)
        END
        
        FETCH NEXT FROM ca_tenant_cursor
        INTO @usm_tenant_id, @ca_tenant_uuid, @sp_new, @sp_old, @inactive_new, @inactive_old, @subtenants_ok_new, @subtenants_ok_old, @ca_tenant_parent_uuid_new, @ca_tenant_parent_uuid_old
    END
    CLOSE ca_tenant_cursor
    DEALLOCATE ca_tenant_cursor
END
GO
ALTER TABLE [dbo].[ca_tenant] ADD CONSTRAINT [PK__ca_tenant__5A1B2568] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ca_tenant_x01] ON [dbo].[ca_tenant] ([name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_tenant_x02 ] ON [dbo].[ca_tenant] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_tenant] ADD CONSTRAINT [ca_tenant_fk01] FOREIGN KEY ([tou_id]) REFERENCES [dbo].[ca_tou] ([id])
GO
GRANT SELECT ON  [dbo].[ca_tenant] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_tenant] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_tenant] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_tenant] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_tenant] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_tenant] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_tenant] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_tenant] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_tenant] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_tenant] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_tenant] TO [usmgroup]
GRANT INSERT ON  [dbo].[ca_tenant] TO [usmgroup]
GRANT DELETE ON  [dbo].[ca_tenant] TO [usmgroup]
GRANT UPDATE ON  [dbo].[ca_tenant] TO [usmgroup]
GO