-- ABS-98988 Process didn't run and ran twice in some cases ABS-99419
-- take CLUSTERTYPE field out of APPSERVERCLUSTERCONTENT primary key
DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_APPSRVCLUSTCONT';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE APPSERVERCLUSTERCONTENT DROP CONSTRAINT XPKAPPSERVERCLUSTERCONTENT';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE APPSERVERCLUSTERCONTENT ADD CONSTRAINT XPKAPPSERVERCLUSTERCONTENT PRIMARY KEY (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID)';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

-- 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
DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER DROP CONSTRAINT XPKFAILOVERMASTER';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_AC';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER DROP CONSTRAINT FAILOVERMASTER_AS';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'DROP INDEX XAK1FAILOVERMASTER';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

-- restore FAILOVERMASTER new structure
DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER ADD CONSTRAINT XPKFAILOVERMASTER PRIMARY KEY (APPSERVERCLUSTER_ID)';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'CREATE INDEX XIF1FAILOVERMASTER ON FAILOVERMASTER (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID)';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/

DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE FAILOVERMASTER ADD CONSTRAINT FAILOVERMASTER_APPSRVCLUSTCONT FOREIGN KEY (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID)
  REFERENCES APPSERVERCLUSTERCONTENT (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID) ON DELETE CASCADE';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/