IF DB_NAME() = 'master' BEGIN RAISERROR (
        '
====================================================================================
master database detected. This script should not be executed in the master database.
====================================================================================

'
        , 20
        , -1
    ) WITH LOG;
END
;
GO



-- Lync Call Center meta templates

IF NOT EXISTS ( SELECT 1 FROM meta_template_type mtt WHERE mtt.id = 'geomant-lync'
)
BEGIN INSERT INTO meta_template_type( id, display, seq
    ) VALUES('geomant-lync', 'Lync (Geomant)', 20131015)
    ;
END;
GO


IF NOT EXISTS ( SELECT TOP 1 1 FROM meta_template mt WHERE mt.eid = '0000' AND mt.meta_template_type_id = 'geomant-lync'
) BEGIN
 DECLARE @new_id INT;
 INSERT INTO meta_template( name, meta_template_type_id, creationdate, createdby, eid
    ) VALUES (
        'Lync (Geomant) Template', 'geomant-lync', getUTCDate(), -1, '0000'
    )
    ;
 SELECT @new_id = SCOPE_IDENTITY();
 INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'CampaignID'
        , 'Campaign ID', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ; INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'CustomerID'
        , 'Customer ID', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ; INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'CustomerURI'
        , 'Customer URI', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;
	INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'CallerURI'
        , 'Caller URI', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;
	INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'CalledURI'
        , 'Called URI', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;
	INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'AgentURI'
        , 'Agent URI', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;
	INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'SduID'
        , 'SDU ID', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;
	INSERT INTO meta_template_field( meta_template_id
        , business_id
        , uccx_id
        , name, type, appearance
        , items, editable, api_enabled, priv
        , creationdate, createdby
    ) SELECT
        @new_id
        , 'field-temp-' + CAST( (IsNull(MAX(id), 0) + 1) AS VARCHAR)
        , 'ConversationID'
        , 'Conversation ID', 'text', 'single-line'
        , '', 0, 1, 0
        , getUTCDate(), -1 FROM meta_template_field
    ;

 INSERT INTO meta_template_group( meta_template_id, group_id
    ) SELECT @new_id, g.group_id FROM [group] g WHERE g.eid = '0000'
    ;

END
GO




IF EXISTS( SELECT TOP 1 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[geomant_lync_create_template]')  AND OBJECTPROPERTY(id, N'IsProcedure') = 1
) BEGIN DROP PROCEDURE [dbo].[geomant_lync_create_template];
END;
GO


CREATE PROCEDURE [dbo].[geomant_lync_create_template] (
    @p_eid CHAR(4)
) AS BEGIN
 DECLARE @orig_id INT; DECLARE @new_id INT;
 DECLARE c CURSOR FOR SELECT t.id FROM meta_template t WHERE t.eid = '0000' AND t.meta_template_type_id = 'geomant-lync' ORDER BY t.id
    ; OPEN c
    ;
 FETCH NEXT FROM c INTO @orig_id;
 WHILE @@FETCH_STATUS = 0 BEGIN
 INSERT INTO meta_template( name, meta_template_type_id, uccx_ip_addresses, creationdate, createdby, eid
        ) SELECT t.name, t.meta_template_type_id, t.uccx_ip_addresses, getUTCDate(), -1, @p_eid FROM meta_template t WHERE t.id = @orig_id
        ;
 SELECT @new_id = SCOPE_IDENTITY();
 INSERT INTO meta_template_field( meta_template_id
            , business_id
            , uccx_id
            , name, type, appearance, items, editable, api_enabled, priv
            , creationdate, createdby
        ) SELECT
            @new_id
            , @p_eid + '-' + business_id AS business_id
            , uccx_id
            , name, type, appearance, items, editable, api_enabled, priv
            , getUTCDate(), -1 FROM meta_template_field WHERE meta_template_id = @orig_id
        ; UPDATE meta_template_field SET business_id = 'field-' + CAST(id AS VARCHAR) WHERE meta_template_id = @new_id
        ;
 INSERT INTO meta_template_group( meta_template_id, group_id
        ) SELECT @new_id, g.group_id FROM [group] g WHERE g.eid = @p_eid
        ;
 FETCH NEXT FROM c INTO @orig_id;
 END;
 CLOSE c; DEALLOCATE c;

END;
GO


DECLARE @eid_for_create_templ CHAR(4);

DECLARE c_for_create_templ CURSOR FOR
SELECT e.eid
FROM environment e
LEFT OUTER JOIN meta_template t ON t.eid = e.eid AND t.meta_template_type_id = 'geomant-lync'
WHERE t.id IS NULL
;

BEGIN OPEN c_for_create_templ; FETCH NEXT FROM c_for_create_templ INTO @eid_for_create_templ;
 WHILE @@FETCH_STATUS = 0 BEGIN exec [geomant_lync_create_template] @eid_for_create_templ
        ; FETCH NEXT FROM c_for_create_templ INTO @eid_for_create_templ; END;
 CLOSE c_for_create_templ; DEALLOCATE c_for_create_templ;

END;
GO





IF EXISTS ( SELECT TOP 1 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tai_environment_geomant_lync]')  AND OBJECTPROPERTY(id, N'IsTrigger') = 1
) DROP TRIGGER [dbo].[tai_environment_geomant_lync]
GO


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tai_environment_geomant_lync]
ON [dbo].[Environment]
AFTER INSERT
AS BEGIN
 DECLARE @eid CHAR(4);
 DECLARE ins_cursor_taie_gmt CURSOR FOR SELECT eid FROM inserted; OPEN ins_cursor_taie_gmt;
 FETCH NEXT FROM ins_cursor_taie_gmt INTO @eid;
 WHILE @@FETCH_STATUS = 0 BEGIN
 exec [geomant_lync_create_template] @eid
        ;
 FETCH NEXT FROM ins_cursor_taie_gmt INTO @eid; END
    ; CLOSE ins_cursor_taie_gmt
    ; DEALLOCATE ins_cursor_taie_gmt
    ;
END
;
GO

BEGIN DECLARE @mt_id INT
    ; DECLARE c_geomant_lync_missing cursor FOR SELECT mt.id FROM meta_template mt WHERE mt.meta_template_type_id = 'geomant-lync'
    ;
 OPEN c_geomant_lync_missing
    ;
 FETCH NEXT FROM c_geomant_lync_missing INTO @mt_id
    ; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM meta_template_field mtf WHERE mtf.meta_template_id = @mt_id AND mtf.uccx_id = 'BusinessTag'
        ) BEGIN INSERT INTO meta_template_field( meta_template_id
                , business_id
                , uccx_id
                , name, type, appearance
                , items, editable, api_enabled, priv
                , creationdate, createdby
            ) SELECT
                @mt_id
                , 'field-temp-' + CAST( (IsNull(MAX(mtf.id), 0) + 1) AS VARCHAR)
                , 'BusinessTag'
                , 'Business Tag', 'text', 'single-line'
                , '', 0, 1, 0
                , getUTCDate(), -1 FROM meta_template_field mtf
            ; END
        ;
         FETCH NEXT FROM c_geomant_lync_missing INTO @mt_id
        ; END
    ; CLOSE c_geomant_lync_missing; DEALLOCATE c_geomant_lync_missing;
END
;
GO
