--APPSERVERCLUSTER TABLE

CREATE SEQUENCE SEQ_APPSERVERCLUSTER
START WITH 1
INCREMENT BY 1;

CREATE TABLE APPSERVERCLUSTER (
APPSERVERCLUSTER_ID       NUMBER NOT NULL,
CODE                      VARCHAR2(10) NOT NULL,
DESCRIPTION               VARCHAR2(30) NOT NULL,
MASTERAPPSERVER_ID        NUMBER NULL,
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);


ALTER TABLE APPSERVERCLUSTER
ADD CONSTRAINT  XPKAPPSERVERCLUSTER PRIMARY KEY (APPSERVERCLUSTER_ID);

CREATE UNIQUE INDEX XAK1APPSERVERCLUSTER ON APPSERVERCLUSTER
(CODE   ASC);

CREATE INDEX XIF1APPSERVERCLUSTER ON APPSERVERCLUSTER
(MASTERAPPSERVER_ID   ASC);

ALTER TABLE APPSERVERCLUSTER
ADD CONSTRAINT APPSERVERCLUSTER_AS FOREIGN KEY (MASTERAPPSERVER_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE SET NULL;



--APPSERVERCLUSTERCONTENT TABLE

CREATE TABLE APPSERVERCLUSTERCONTENT (
APPSERVERCLUSTER_ID       NUMBER NOT NULL,
APPLICATIONSERVER_ID      NUMBER NOT NULL,
ACTUALSTATUS              NUMBER DEFAULT 0 NOT NULL CONSTRAINT  ActualStatus_valid CHECK (ACTUALSTATUS IN (0, 1, 2, 3, 4, 5)),
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);


ALTER TABLE APPSERVERCLUSTERCONTENT
ADD CONSTRAINT  XPKAPPSERVERCLUSTERCONTENT PRIMARY KEY (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID);

COMMENT ON COLUMN APPSERVERCLUSTERCONTENT.ACTUALSTATUS IS '0 = Unknown
1 = Connectable
2 = Not connectable
3 = Master
4 = Master/ needs refresh
5 = Connectable/ needs refresh';

CREATE INDEX XIF1APPSERVERCLUSTERCONTENT ON APPSERVERCLUSTERCONTENT
(APPSERVERCLUSTER_ID   ASC);

CREATE INDEX XIF2APPSERVERCLUSTERCONTENT ON APPSERVERCLUSTERCONTENT
(APPLICATIONSERVER_ID   ASC);

ALTER TABLE APPSERVERCLUSTERCONTENT
ADD CONSTRAINT APPSERVERCLUSTERCONTENT_APPSC FOREIGN KEY (APPSERVERCLUSTER_ID) REFERENCES APPSERVERCLUSTER (APPSERVERCLUSTER_ID) ON DELETE CASCADE;

ALTER TABLE APPSERVERCLUSTERCONTENT
ADD CONSTRAINT APPSERVERCLUSTERCONTENT_APPS FOREIGN KEY (APPLICATIONSERVER_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE CASCADE;



--FAILOVERMASTER TABLE

CREATE TABLE FAILOVERMASTER (
APPSERVERCLUSTER_ID       NUMBER NOT NULL,
APPLICATIONSERVER_ID      NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);


ALTER TABLE FAILOVERMASTER
ADD CONSTRAINT  XPKFAILOVERMASTER PRIMARY KEY (APPLICATIONSERVER_ID);

CREATE INDEX XAK1FAILOVERMASTER ON FAILOVERMASTER
(APPSERVERCLUSTER_ID   ASC);

ALTER TABLE FAILOVERMASTER
ADD CONSTRAINT FAILOVERMASTER_AC FOREIGN KEY (APPSERVERCLUSTER_ID) REFERENCES APPSERVERCLUSTER (APPSERVERCLUSTER_ID) ON DELETE CASCADE;

ALTER TABLE FAILOVERMASTER 
ADD CONSTRAINT FAILOVERMASTER_AS FOREIGN KEY (APPLICATIONSERVER_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE CASCADE;



--FAILOVERPROCESS TABLE

CREATE TABLE FAILOVERPROCESS (
PLANTPROCESS_ID           NUMBER NOT NULL,
APPLICATIONSERVER_ID      NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);


ALTER TABLE FAILOVERPROCESS
ADD CONSTRAINT  XPKFAILOVERPROCESS PRIMARY KEY (PLANTPROCESS_ID);

CREATE INDEX XIF1FAILOVERPROCESS ON FAILOVERPROCESS
(APPLICATIONSERVER_ID   ASC);

ALTER TABLE FAILOVERPROCESS
ADD CONSTRAINT FAILOVERPROCESS_PP FOREIGN KEY (PLANTPROCESS_ID) REFERENCES PLANTPROCESS (PLANTPROCESS_ID) ON DELETE CASCADE;

ALTER TABLE FAILOVERPROCESS 
ADD CONSTRAINT FAILOVERPROCESS_AS FOREIGN KEY (APPLICATIONSERVER_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE CASCADE;



--PLANTPROCESSHISTORY TABLE

CREATE SEQUENCE SEQ_PLANTPROCESSHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTPROCESSHISTORY (
PLANTPROCESSHISTORY_ID           NUMBER NOT NULL,
PLANTPROCESS_ID                  NUMBER NOT NULL,
HOST                             VARCHAR2(80) NOT NULL,
PORT                             NUMBER NOT NULL,
STARTTIMESTAMP                   DATE NULL,
ENDTIMESTAMP                     DATE NULL,
STATUS                           NUMBER DEFAULT 0 NOT NULL CONSTRAINT  Status_valid_1 CHECK (STATUS IN (0, 1, 2, 3, 4, 5, 6))
);


ALTER TABLE PLANTPROCESSHISTORY
ADD CONSTRAINT  XPKPLANTPROCESSHISTORY PRIMARY KEY (PLANTPROCESSHISTORY_ID);

COMMENT ON COLUMN PLANTPROCESSHISTORY.STATUS IS '0 = Waiting for next run
1 = In progress
2 = Run successfully
3 = Main script Failed
4 = Pre-Script failed
5 = Post-Script failed
6=Process with errors
';

CREATE INDEX XIF1PLANTPROCESSHISTORY ON PLANTPROCESSHISTORY
(PLANTPROCESS_ID   ASC);

ALTER TABLE PLANTPROCESSHISTORY
ADD CONSTRAINT PLANTPROCESSHISTORY_PP FOREIGN KEY (PLANTPROCESS_ID) REFERENCES PLANTPROCESS (PLANTPROCESS_ID) ON DELETE CASCADE;




--table PLANTPROCESS 
--!!! instructions MUST be in this order !!!

/*ALTER TABLE PLANTPROCESS ADD (APPSERVERCLUSTER_ID NUMBER NULL);

create or replace procedure createorupdateappservercluster is
  existingappservercluster appservercluster.appservercluster_id%type;
  newAppServerCluster      appservercluster.appservercluster_id%type;
  maxCluster               number;
  cursor info is
    select * from plantprocess pp where pp.appservercluster_id is null;
begin
  for dataInfo in info loop
    begin
      -- search appservercluster that contains plantprocess.applicationserver_id
      select appsc.appservercluster_id
        into existingAppServerCluster
        from appservercluster appsc, appserverclustercontent ascc
       where appsc.appservercluster_id = ascc.appservercluster_id
         and ascc.applicationserver_id = dataInfo.applicationserver_id;
    exception
      when no_data_found then
        null;
    end;
    if (existingAppServerCluster is not null) then
      -- update pp.appservercluster_id with the existing one
      begin
        update plantprocess pp
           set pp.appservercluster_id = existingAppServerCluster
         where pp.plantprocess_id = dataInfo.plantprocess_id;
      exception
        when others then
          null;
      end;
    else
      newAppServerCluster := SEQ_APPSERVERCLUSTER.Nextval;
      maxCluster          := 0;
      begin
        select max(to_number(substr(appsc.code, 9))) + 1
          into maxCluster
          from appservercluster appsc
         where appsc.code like 'CLUSTER_%';
      exception
        when others then
          null;
      end;
      if (maxCluster is null) then
        maxCluster := 1;
      end if;
      --create appservercluster
      begin
        insert into appservercluster
          (appservercluster_id,
           code,
           description,
           MASTERAPPSERVER_ID,
           SYSTEMUSER_ID,
           timestamp)
        values
          (newAppServerCluster,
           'CLUSTER_' || maxCluster,
           'CLUSTER_' || maxCluster,
           NULL,
           0,
           SYSDATE);
      end;
      --create appserverclustercontent
      begin
        insert into appserverclusterCONTENT
          (appservercluster_id,
           APPLICATIONSERVER_ID,
           ACTUALSTATUS,
           SYSTEMUSER_ID,
           timestamp)
        values
          (newAppServerCluster,
           dataInfo.applicationserver_id,
           0,
           0,
           SYSDATE);
      end;
      --update pp.appservercluster_id with the new one just created
      begin
        update plantprocess pp
           set pp.appservercluster_id = newAppServerCluster
         where pp.plantprocess_id = dataInfo.plantprocess_id;
      exception
        when others then
          null;
      end;
    end if;
  end loop;
end createOrUpdateAppServerCluster;


/

execute createOrUpdateAppServerCluster;

drop procedure createOrUpdateAppServerCluster;


ALTER TABLE PLANTPROCESS MODIFY (APPSERVERCLUSTER_ID NOT NULL);

ALTER TABLE PLANTPROCESS DROP COLUMN APPLICATIONSERVER_ID;


ALTER TABLE PLANTPROCESS ADD (PROCESSEDBYAPPSRV_ID NUMBER NULL);


CREATE INDEX XIF7PLANTPROCESS ON PLANTPROCESS
(PROCESSEDBYAPPSRV_ID   ASC);

CREATE INDEX XIF8PLANTPROCESS ON PLANTPROCESS
(APPSERVERCLUSTER_ID   ASC);

ALTER TABLE PLANTPROCESS
ADD CONSTRAINT PLANTPROCESS_APPSRV FOREIGN KEY (PROCESSEDBYAPPSRV_ID) REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE SET NULL;

ALTER TABLE PLANTPROCESS
ADD CONSTRAINT PLANTPROCESS_APPCLUSTER FOREIGN KEY (APPSERVERCLUSTER_ID) REFERENCES APPSERVERCLUSTER (APPSERVERCLUSTER_ID);

*/
