
CREATE TABLE WFS.TTV_SELECTOR ( 
    SELECTIONKEY        VARCHAR2(4000) 	NOT NULL,
    PROCESSDEFINITIONID NUMBER(19,0)   	NULL,
    CREATEDATE          DATE		NOT NULL,
    UPDATEDATE          DATE   		NOT NULL
)
TABLESPACE WFS_DATA;

ALTER TABLE WFS.TTV_SELECTOR
	ADD ( CONSTRAINT PK_TTV_SELECTOR
	PRIMARY KEY (SELECTIONKEY)
	NOT DEFERRABLE INITIALLY IMMEDIATE
    USING INDEX
    TABLESPACE WFS_INDEX );

ALTER TABLE WFS.TTV_SELECTOR
    ADD ( CONSTRAINT TTV_SELECTOR#JBPM_PROCESSDEF
	FOREIGN KEY(PROCESSDEFINITIONID)
	REFERENCES WFS.JBPM_PROCESSDEFINITION("ID_")
	ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE );

CREATE OR REPLACE TRIGGER WFS.TRG_TTV_SELECTOR_SEQ
                            BEFORE INSERT ON WFS.TTV_SELECTOR
                            FOR EACH ROW
                            BEGIN
                                
				:NEW.CreateDate := sysdate;
                                :NEW.UpdateDate := sysdate;

                            END;
/

CREATE OR REPLACE TRIGGER WFS.TRG_TTV_SELECTOR_UPD
                            BEFORE UPDATE
                            ON WFS.TTV_SELECTOR
                            FOR EACH ROW
                            BEGIN
                                :NEW.UpdateDate := sysdate;
                            END;
/

Alter Table WFS.JBPM_TOKEN
ADD (OPERATIONALSTATUS      Number(19,0) NULL,
     ADMINISTRATIVESTATUS   Number(19,0) NULL,
     Constraint FK_TOKEN#PROCESSSTATUS_OPER
        Foreign Key(OPERATIONALSTATUS)
        References WFS.TTV_PROCESSSTATUS(PROCESSSTATUSID),
     Constraint FK_TOKEN#PROCESSSTATUS_ADMIN
        Foreign Key(ADMINISTRATIVESTATUS)
        References WFS.TTV_PROCESSSTATUS(PROCESSSTATUSID));


Alter Table WFS.TTV_RESOURCEGROUP
ADD (FUNCTIONALTYPE varchar2(1024)  NULL,
     ISVISIBLE      number(1)       DEFAULT(0) NOT NULL,  
     IMAGEPATH      varchar2(4000)  NULL);

ALTER TABLE WFS.TTV_RESOURCEGROUP
MODIFY(ALLOCATIONSTRATEGYCLASSID NULL);

Alter Table WFS.TTV_RESOURCE
ADD (FUNCTIONALTYPE varchar2(1024) NULL);


-- Need a job to create new unique IDs and make NOT NULL
Alter table WFS.TTV_RESOURCETYPE
ADD (SYSTEMID       varchar2(4000)  NULL); 

CREATE UNIQUE INDEX WFS.IDX_TTV_RESOURCETYPE_UK
    ON WFS.TTV_RESOURCETYPE(SYSTEMID)
TABLESPACE WFS_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
PARALLEL( DEGREE 1 )
LOGGING;

insert into WFS.TTV_PROCESSSTATUS(PROCESSSTATUSID,NAME,CREATEDATE,UPDATEDATE)
Values(8,'BRANCH',sysdate,sysdate);



-- JBPM_TRANSITION changes

CREATE TABLE WFS.TTV_TRANSITIONTYPE (
    TRANSITIONTYPEID    NUMBER NOT NULL,
    NAME        VARCHAR2(50) NULL,
    CREATEDATE  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
    UPDATEDATE  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
    PRIMARY KEY(TRANSITIONTYPEID)
    USING INDEX (CREATE UNIQUE INDEX WFS.PK_TTV_TRANSITIONTYPE
                 ON WFS.TTV_TRANSITIONTYPE(TRANSITIONTYPEID)
                 TABLESPACE WFS_INDEX)
)
TABLESPACE WFS_DATA
NOCACHE
MONITORING;

COMMENT ON TABLE WFS.TTV_TRANSITIONTYPE IS 'Required as a result of sub-classing JBPM_TRANSITION entity';

CREATE OR REPLACE TRIGGER WFS.TRG_TTV_TRANSITIONTYPE_UPD
BEFORE UPDATE
ON WFS.TTV_TRANSITIONTYPE
FOR EACH ROW
DECLARE updatedate      date;
BEGIN
SELECT  SYSTIMESTAMP
INTO    updatedate
FROM    DUAL;
:NEW.UpdateDate := updatedate;
END;
/

Insert Into WFS.TTV_TRANSITIONTYPE(TRANSITIONTYPEID, NAME,CREATEDATE,UPDATEDATE)
Values(1,'JBPM Standard Transition',systimestamp,systimestamp);

Insert into WFS.TTV_TRANSITIONTYPE(TRANSITIONTYPEID, NAME,CREATEDATE,UPDATEDATE)
Values(2,'TTV Transition',systimestamp,systimestamp);

ALTER TABLE "WFS"."JBPM_TRANSITION"
        ADD ( "TRANSITIONTYPEID" NUMBER DEFAULT(2) NULL );

Update "WFS"."JBPM_TRANSITION"
Set TRANSITIONTYPEID = (Select TRANSITIONTYPEID
                        From WFS.TTV_TRANSITIONTYPE
                        Where NAME = 'JBPM Standard Transition');

ALTER TABLE WFS.JBPM_TRANSITION
    MODIFY(TRANSITIONTYPEID NOT NULL);

ALTER TABLE WFS.JBPM_TRANSITION
    ADD ( CONSTRAINT FK_JBPM_TRANS_TTV_TRANSTYPE
        FOREIGN KEY(TRANSITIONTYPEID)
        REFERENCES WFS.TTV_TRANSITIONTYPE(TRANSITIONTYPEID)
        ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE );

CREATE OR REPLACE TRIGGER WFS.TRG_JBPM_VARIABLEINSTANCE_SEQ
BEFORE INSERT ON WFS.JBPM_VARIABLEINSTANCE
FOR EACH ROW
BEGIN

    IF :NEW.ID_ IS NULL THEN
        SELECT WFS.JBPM_VARIABLEINSTANCE_SEQ.NEXTVAL INTO :NEW.ID_ FROM DUAL;
    END IF;

    IF :NEW.STRINGVALUE_ IS NULL THEN
        SELECT ' ' INTO :NEW.STRINGVALUE_ FROM DUAL;
    END IF;

END;
/

CREATE OR REPLACE TRIGGER WFS.TRG_JBPM_VARIABLEINSTANCE_UPD
BEFORE UPDATE
ON WFS.JBPM_VARIABLEINSTANCE
FOR EACH ROW
BEGIN

  SELECT SYSTIMESTAMP
  INTO :NEW.UpdateDate
  FROM DUAL;

  IF :NEW.STRINGVALUE_ IS NULL THEN
      SELECT ' '
      INTO :NEW.STRINGVALUE_
      FROM DUAL;
  END IF;

END;
/

CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_04
    ON WFS.JBPM_VARIABLEINSTANCE(TOKENVARIABLEMAP_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TASKINSTANCE_02
    ON WFS.JBPM_TASKINSTANCE(TASKMGMTINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_SWIMLANEINSTANCE_01
    ON WFS.JBPM_SWIMLANEINSTANCE(TASKMGMTINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_RUNTIMEACTION_01
    ON WFS.JBPM_RUNTIMEACTION(PROCESSINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_COMMENT_01
    ON WFS.JBPM_COMMENT(TOKEN_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TOKEN_01
    ON WFS.JBPM_TOKEN(PARENT_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_TTV_PROCESSINSTANCENOTE_01
    ON WFS.TTV_PROCESSINSTANCENOTE(PROCESSINSTANCEID)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_MODULEINSTANCE_01
    ON WFS.JBPM_MODULEINSTANCE(PROCESSINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TOKENVARIABLEMAP_01
    ON WFS.JBPM_TOKENVARIABLEMAP(CONTEXTINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_01 
    ON WFS.JBPM_VARIABLEINSTANCE (NAME_, STRINGVALUE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_02 
    ON WFS.JBPM_VARIABLEINSTANCE (PROCESSINSTANCE_, NAME_, STRINGVALUE_, TOKENVARIABLEMAP_, CLASS_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_03
    ON WFS.JBPM_VARIABLEINSTANCE(TASKINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_PROCESSINSTANCE_01
    ON WFS.JBPM_PROCESSINSTANCE(PROCESSDEFINITION_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TASK_01
    ON WFS.JBPM_TASK(TASKNODE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TASK_02
    ON WFS.JBPM_TASK(STARTSTATE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_NODE_01
    ON WFS.JBPM_NODE(PROCESSDEFINITION_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_TOKEN_02
    ON WFS.JBPM_TOKEN(NODE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_LOG_01 
    ON WFS.JBPM_LOG (CLASS_, NODE_, TOKEN_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_COMMENT_02 
    ON WFS.JBPM_COMMENT (TASKINSTANCE_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_PROCESSDEFINITION_01 
    ON WFS.JBPM_PROCESSDEFINITION (NAME_)
TABLESPACE WFS_INDEX;

CREATE INDEX WFS.IDX_JBPM_PROCESSDEFINITION_02 
    ON WFS.JBPM_PROCESSDEFINITION (PROCESSDEFINITIONTYPEID)
TABLESPACE WFS_INDEX;

-- #########################
-- Update the Resource Groups to contain the correct values visibility and functional type
-- #########################
-- Update the visibility
update wfs.ttv_resourcegroup set isvisible = 0
/

update wfs.ttv_resourcegroup set isvisible = 1 
where resourcetypeid in 
    (select resourcetypeid from wfs.ttv_resourcetype where systemId in ('03', '05', '08', '11'))
/


-- Update the functional type for HD Encoders
update wfs.ttv_resourcegroup set functionaltype = 'HD Encoder'
where resourcetypeid in (select resourcetypeid from wfs.ttv_resourcetype where systemId = '11')
/
update wfs.ttv_resource set functionaltype = 'HD Encoder'
where resourcetypeid in (select resourcetypeid from wfs.ttv_resourcetype where systemId = '11')
/
-- Update the functional type for SD Encoders
update wfs.ttv_resourcegroup set functionaltype = 'SD Encoder'
where resourcetypeid in (select resourcetypeid from wfs.ttv_resourcetype where systemId = '05')
/
update wfs.ttv_resource set functionaltype = 'SD Encoder'
where resourcetypeid in (select resourcetypeid from wfs.ttv_resourcetype where systemId = '05')
/

-- #########################
-- Change all Resources and Groups of Resource Type 'XPort HD' to the new 'XPort Producer' type
-- #########################
update wfs.ttv_resourcegroup set resourcetypeid = (select resourcetypeid from wfs.ttv_resourcetype where systemId = '05')
where resourcetypeId = (select resourcetypeid from wfs.ttv_resourcetype where systemId = '11')
/
update wfs.ttv_resource set resourcetypeid = (select resourcetypeid from wfs.ttv_resourcetype where systemId = '05')
where resourcetypeId = (select resourcetypeid from wfs.ttv_resourcetype where systemId = '11')


DECLARE

  sKey varchar2(50);
  idx number;

  cursor procDefs is
    select ID_, NAME_ from WFS.JBPM_PROCESSDEFINITION where PROCESSDEFINITIONTYPEID IN (2, 3);

BEGIN
    delete from WFS.TTV_SELECTOR;
    dbms_output.put_line('Deleted all records in TTV_SELECTOR table - will be reloaded as per JBPM_PROCESSDEFINITION table');
    for rec in procDefs loop
        sKey := null;
        idx := instr(rec.name_, ' (');
        if idx > 0 then
            --dbms_output.put_line('Before removing version: ' || rec.name_);
            rec.name_ := substr(rec.name_, 1, idx - 1);
            --dbms_output.put_line('After removing version: ' || rec.name_);
        end if;
        case rec.name_
            when 'Template Selector' then sKey := '020101';
            when 'Purge' then sKey := '020102';
            when 'Adult Asset Arrival Success' then sKey := '060101_ADULT';
            when 'Adult Asset Arrival Failure' then sKey := '060102_ADULT';
            when 'Adult Movie SD' then sKey := 'Adult Movie SD Template';
            when 'Adult Movie HD' then sKey := 'Adult Movie HD Template';
            when 'Adult Poster' then sKey := 'Adult Poster Template';
            when 'Adult Trailer' then sKey := 'Adult Trailer Template';
            when 'Adult Trailer using XPort' then sKey := 'Adult Trailer Template';
            when 'Asset Arrival Success' then sKey := '060101_NON-ADULT';
            when 'Asset Arrival Failure' then sKey := '060102_NON-ADULT';
            when 'Movie SD' then sKey := 'Non-Adult Movie SD Template';
            when 'Movie HD' then sKey := 'Non-Adult Movie HD Template';
            when 'Home Page' then sKey := 'Non-Adult HomePage Template';
            when 'Row Ad' then sKey := 'Non-Adult RowAd Template';
            when 'Trailer' then sKey := 'Non-Adult Trailer Template';
            when 'Trailer using XPort' then sKey := 'Adult Trailer Template';
            when 'Poster' then sKey := 'Non-Adult Poster Template';
            else dbms_output.put_line('No mapping defined for template: ' || rec.name_);
        end case;
        if sKey is not null then
            insert into wfs.ttv_selector (selectionkey, processdefinitionid) values (sKey, rec.id_);
            dbms_output.put_line('Record inserted into selector table: ' || sKey || ' | ' || rec.id_);
        end if;
    end loop;
END;
/

INSERT INTO WFS.TTV_MODULE(ModuleID, NAME) 
    VALUES(WFS.TTV_MODULE_SEQ.nextval, 'Plugins')
/
INSERT INTO WFS.TTV_PERMISSION(PermissionID, NAME, SECTIONNAME, MODULEID) 
   VALUES(WFS.TTV_PERMISSION_SEQ.nextval, 'View', 'Plugins', WFS.TTV_MODULE_SEQ.currval)
/
INSERT INTO WFS.TTV_ACTION(ACTIONID, NAME, PERMISSIONID, PATH) 
    VALUES(89, 'View Plugins', WFS.TTV_PERMISSION_SEQ.currval, 'plugins.do?method=getPlugins')
/
INSERT INTO WFS.TTV_ACTION(ACTIONID, NAME, PERMISSIONID, PATH) 
    VALUES(90, 'Sort Plugins', WFS.TTV_PERMISSION_SEQ.currval, 'plugins.do?method=sortPlugins')
/
INSERT INTO WFS.TTV_BIGROLEPERMISSION(PERMISSIONID, BIGROLE) 
    VALUES(WFS.TTV_PERMISSION_SEQ.currval, 'Admin')
/
INSERT INTO WFS.TTV_MENUITEM(NAME, PERMISSIONID) 
    VALUES('TabbedPlugins', WFS.TTV_PERMISSION_SEQ.currval)
/
INSERT INTO WFS.TTV_RESOURCESTATUS(RESOURCESTATUSID,NAME,CREATEDATE,UPDATEDATE)
    VALUES(WFS.TTV_RESOURCESTATUS_SEQ.nextval, 'Invalid', sysdate, sysdate)
/

-- Bug Fix
UPDATE WFS.ALERT_NAME
SET PATTERN = '.*execution has failed.*'
WHERE ALERT_NAME = 'Workorder Failed Pattern'
/

UPDATE WFS.ALERT_NAME
SET PATTERN = '.*License unavailable for template.*'
WHERE ALERT_NAME = 'Invalid License for Templates Pattern'
/

INSERT INTO WFS.ALERT_NAMES(ALERT_NAME, PATTERN) 
    VALUES('Invalid License for Templates Pattern', '.*Invalid license for templates.*')
/
INSERT INTO WFS.ALERT_ACTIONS(IDENTIFIER, ACTION_TYPE, DETAIL) 
    VALUES ('Invalid Template License', 1, 11)
/
INSERT INTO WFS.ALERTS(IDENTIFIER, ALERT_NAME, THRESH_COUNT, THRESH_SECS, FREQ_COUNT, FREQ_SECS, TYPE) 
    VALUES ('Invalid Template License', 'Invalid License for Templates Pattern', 1, 3600, 1, 3600, 'LOG')
/

--------------------------------------------------------------------------
-- Everything below this is for Watchpoint 2.6
--------------------------------------------------------------------------

--	Bug Fix
--	DAW 10/22/2007 
--	Removing the following records because the are no longer needed
DELETE  FROM WFS.ALERT_ACTIONS_HIST
WHERE   HIST_KEY IN (
        SELECT  HIST_KEY
        FROM    WFS.ALERTS_HIST
        WHERE   IDENTIFIER IN ('Monitoring System Down', 'Duplicate Request For Workorder'));
DELETE  FROM WFS.ALERTS_HIST
WHERE   IDENTIFIER IN ('Monitoring System Down', 'Duplicate Request For Workorder');
DELETE  FROM WFS.ALERT_ACTIONS 
WHERE   IDENTIFIER IN ('Monitoring System Down', 'Duplicate Request For Workorder');
DELETE  FROM WFS.ALERTS
WHERE   IDENTIFIER IN ('Monitoring System Down', 'Duplicate Request For Workorder');
DELETE  FROM  WFS.ALERT_NAMES
WHERE   ALERT_NAME IN  ('Monitoring System Down', 'Duplicate Request For Workorder Pattern');

-- Inserts the template ingest failure alert information
INSERT INTO WFS.ALERT_NAMES (ALERT_NAME, PATTERN) 
	VALUES('Template Ingest Failure Pattern', '.*Failed to ingest template.*')
/
INSERT INTO WFS.ALERTS (IDENTIFIER, ALERT_NAME, THRESH_COUNT, THRESH_SECS, FREQ_COUNT, FREQ_SECS, TYPE) 
	VALUES ('Template Ingest Failure', 'Template Ingest Failure Pattern', 1, 3600, 1, 3600, 'LOG')
/
INSERT INTO WFS.ALERT_ACTIONS (IDENTIFIER, ACTION_TYPE, DETAIL) 
	VALUES ('Template Ingest Failure', 1, '12')
/

--DAW 11/28/2007 - Adding a TTV_ACTION
--I would have preferred to hard code the value for PermissionId value but in old versions of the databases
--I can't guarantee that the value will always be the same
--This script assumes that the sequence used for TTV_ACTION is currently correct.
INSERT INTO WFS.TTV_ACTION (NAME, PERMISSIONID, PATH, CREATEDATE, UPDATEDATE)
SELECT	'Help', 
		PERMISSIONID, 
		'help.do?method=getHelp', 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'), 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM')
FROM	WFS.TTV_PERMISSION
WHERE 	NAME = 'Dashboard'
/

--DAW 11/30/2007 - Adding Inserts for TTV_ACTION & TTV_MENUITEM
--This script assumes that the sequence used for TTV_ACTION & TTV_MENUITEM is currently correct.
INSERT INTO WFS.TTV_ACTION (NAME, PERMISSIONID, PATH, CREATEDATE, UPDATEDATE)
SELECT	'View Selector Keys', 
		PERMISSIONID, 
		'viewselectorkeys.do?method=getSelectorKeyDetails', 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'), 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM')
FROM	WFS.TTV_PERMISSION
WHERE 	SECTIONNAME = 'View Work Orders'
/
INSERT INTO WFS.TTV_ACTION (NAME, PERMISSIONID, PATH, CREATEDATE, UPDATEDATE)
SELECT	'Add Selector Key', 
		PERMISSIONID, 
		'viewselectorkeys.do?method=addSelectorKey', 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'), 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM')
FROM	WFS.TTV_PERMISSION
WHERE 	SECTIONNAME = 'Create Work Orders'
/
INSERT INTO WFS.TTV_ACTION (NAME, PERMISSIONID, PATH, CREATEDATE, UPDATEDATE)
SELECT	'Delete Selector Key', 
		PERMISSIONID, 
		'viewselectorkeys.do?method=deleteSelectorKey', 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'), 
		TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM')
FROM	WFS.TTV_PERMISSION
WHERE 	SECTIONNAME = 'Delete Work Orders'
/
INSERT INTO WFS.TTV_MENUITEM(NAME,PERMISSIONID, CREATEDATE, UPDATEDATE) 
VALUES('TabbedSelectorKeysDetails', 2, TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'), TO_DATE('30/11/2007 12:00:00 AM','DD/MM/YYYY HH12:MI:SS PM'))
/

exit 0
