-- ABS-98988 Process didn't run and ran twice in some cases ABS-99419

-- take CLUSTERTYPE field out of APPSERVERCLUSTERCONTENT primary key
ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_APPSRVCLUSTCONT;

ALTER TABLE APPSERVERCLUSTERCONTENT DROP CONSTRAINT XPKAPPSERVERCLUSTERCONTENT;

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

-- correct data in APPSERVERCLUSTERCONTENT
delete from appserverclustercontent scc
 where exists (select 1
          from appservercluster sc
         where sc.appservercluster_id = scc.appservercluster_id
           and sc.clustertype != scc.clustertype)
   and exists
 (select 1
          from appserverclustercontent scc2
         where scc2.appservercluster_id = scc.appservercluster_id
           and scc2.clustertype != scc.clustertype);
commit;

update appserverclustercontent scc
   set scc.clustertype =
       (select clustertype
          from appservercluster
         where appservercluster_id = scc.appservercluster_id)
 where exists (select 1
          from appservercluster sc
         where sc.appservercluster_id = scc.appservercluster_id
           and sc.clustertype != scc.clustertype);
commit;

-- correct data in FAILOVERMASTER
delete from failovermaster fom
 where fom.applicationserver_id !=
       (select sc.masterappserver_id
          from appservercluster sc
         where sc.appservercluster_id = fom.appservercluster_id)
    or not exists
 (select 1
          from appserverclustercontent scc
         where scc.appservercluster_id = fom.appservercluster_id
           and scc.applicationserver_id = fom.applicationserver_id);
commit;

-- clean up FAILOVERMASTER old structure
ALTER TABLE FAILOVERMASTER DROP CONSTRAINT XPKFAILOVERMASTER;

ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_AC;

ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_AS;

DROP INDEX XAK1FAILOVERMASTER;

-- restore FAILOVERMASTER new structure
ALTER TABLE FAILOVERMASTER
  ADD CONSTRAINT XPKFAILOVERMASTER PRIMARY KEY (APPSERVERCLUSTER_ID);

CREATE INDEX XIF1FAILOVERMASTER ON FAILOVERMASTER (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID);
  
ALTER TABLE FAILOVERMASTER
  ADD CONSTRAINT FAILOVERMASTER_APPSRVCLUSTCONT FOREIGN KEY (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID)
  REFERENCES APPSERVERCLUSTERCONTENT (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID) ON DELETE CASCADE;