create or replace function update_susceptor_db() returns void as 
$$ 
begin 
 
    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'SusceptorData') then 
 
	CREATE TABLE "SusceptorData"
	(
		"Id" serial NOT NULL,
		"Time" timestamp without time zone,
		"SusceptorId" text,
		"ItemType" text,
		"ItemData" text,
		"ItemRemark" text,
		CONSTRAINT "SusceptorData_pkey" PRIMARY KEY ("Id" )
	)
	WITH (
	OIDS=FALSE
	);
	ALTER TABLE "SusceptorData"
	OWNER TO postgres;
	GRANT SELECT ON TABLE "SusceptorData" TO postgres;
 
    end if; 




 
    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'SusceptorList') then 
 
	CREATE TABLE "SusceptorList"
	(
		"SusceptorId" text NOT NULL,
		"UserDefinedId" text,
		"CreateTime" timestamp without time zone,
		"DeleteTime" timestamp without time zone,
		"ProcessBeginTime" timestamp without time zone,
		"ProcessEndTime" timestamp without time zone,
		"ProcessIn" text,
		"RecipeName" text,
		"SusceptorStatus" text,
		"WafersOnSusceptor" text,
		"Description" text,
		CONSTRAINT "SusceptorList_pkey" PRIMARY KEY ("SusceptorId" )
	)
	WITH (
	OIDS=FALSE
	);
	ALTER TABLE "SusceptorList"
	OWNER TO postgres;
	GRANT SELECT ON TABLE "SusceptorList" TO postgres;
 
    end if; 

	
	 if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'EventManager') then 
 
	CREATE TABLE "EventManager"
(
  "PID" serial NOT NULL,
  "ID" integer,
  "EventEnum" text,
  "Type" text,
  "Source" text,
  "Description" text,
  "Level" text,
  "OccurTime" timestamp without time zone,
  CONSTRAINT "EventManager_pkey" PRIMARY KEY ("PID" )
)
	WITH (
	OIDS=FALSE
	);
	--ALTER TABLE "EventManager"
	--OWNER TO postgres;
	GRANT ALL ON TABLE "EventManager" TO postgres;
	GRANT SELECT ON TABLE "EventManager" TO postgres;

	CREATE INDEX "EventManager_OccurTime_ID_idx"
	ON "EventManager"
	USING btree
	("OccurTime" , "ID" );
    end if; 
	
	
	
	
	    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'RecipeRunHistory') then 
 
	CREATE TABLE "RecipeRunHistory"
	(
	  "RecipeRunGuid" text NOT NULL,
	  "SusceptorId" text,
	  "ProcessBeginTime" timestamp without time zone,
	  "ProcessEndTime" timestamp without time zone,
	  "ProcessIn" text,
	  "RecipeName" text,
	  "SusceptorStatus" text,
	  "UserDefinedId" text,
	  "Description" text,
	  "WafersOnSusceptor" text,
	  CONSTRAINT "RecipeRunHistory_pkey" PRIMARY KEY ("RecipeRunGuid" )
	)
	WITH (
	  OIDS=FALSE
	);
	ALTER TABLE "RecipeRunHistory"
	OWNER TO postgres;
	GRANT SELECT ON TABLE "RecipeRunHistory" TO postgres;
 
    end if; 
    
	 
    if not exists(select * from "RecipeRunHistory" where "Description" = 'DUMMY_RECORD_USED_FOR_UPDATING_ONLY') then

	  INSERT INTO "RecipeRunHistory"("RecipeRunGuid", "Description") Values('DUMMY_RECORD_USED_FOR_UPDATING_ONLY','DUMMY_RECORD_USED_FOR_UPDATING_ONLY');
		    
	  INSERT INTO "RecipeRunHistory"(
		    "RecipeRunGuid", "SusceptorId", "UserDefinedId", "ProcessBeginTime", "ProcessEndTime", "ProcessIn", 
		    "RecipeName", "SusceptorStatus", "Description")
	  SELECT "SusceptorId", "SusceptorId", "UserDefinedId", "ProcessBeginTime", "ProcessEndTime", "ProcessIn", 
		    "RecipeName", "SusceptorStatus", "Description" from "SusceptorList" where ("RecipeName" <> '');

     end if;



	 if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'VirtualDevice') then 
 
	CREATE TABLE "VirtualDevice"
	(
	  "ID" serial NOT NULL,
	  "ChamberID" text,
	  "DeviceName" character varying,
	  "State" integer,
	  "LastModifyDate" timestamp without time zone,
	  "FunctionBlock" text,
	  CONSTRAINT "VirtualDevice_pkey" PRIMARY KEY ("ID" )
	)
	WITH (
	  OIDS=TRUE
	);
	ALTER TABLE "VirtualDevice"
	  OWNER TO postgres;
	GRANT SELECT ON TABLE "VirtualDevice" TO postgres;
 
	end if; 


	if not exists(select * from information_schema.tables  
	where  
	table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
	and table_name = 'ApplicationRunInfo') then 
	CREATE TABLE "ApplicationRunInfo"
	(
	"ActTime"  timestamp with time zone,
	"Type" text,
	"Info" text,
	"ChamberInfo" text,
	bak2 text,
	"ID" serial NOT NULL,
	"LastUpdateTime"  timestamp with time zone,
	CONSTRAINT "ApplicationRunInfo_pkey" PRIMARY KEY ("ID" )
	)
	WITH (
	OIDS=FALSE
	); 
	ALTER TABLE "ApplicationRunInfo"
	OWNER TO postgres;
	GRANT SELECT ON TABLE "ApplicationRunInfo" TO postgres;
	end if; 

	 if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'WaferDisplayIndex') then 
	CREATE TABLE "WaferDisplayIndex"
	(
		"Id" text NOT NULL,
		"DisplayIndex" text,
		CONSTRAINT "WaferDisplayIndex_pkey" PRIMARY KEY ("Id" )
	)
	WITH (
	OIDS=FALSE
	);
	ALTER TABLE "WaferDisplayIndex"
	OWNER TO postgres;
	GRANT SELECT ON TABLE "WaferDisplayIndex" TO postgres;
    end if; 
end; 
$$ 
language 'plpgsql'; 
 
select update_susceptor_db(); 

CREATE OR REPLACE FUNCTION batch_delete_tables(text)
RETURNS int AS
$$
DECLARE
    r RECORD;
    count int;
BEGIN
    count := 0;
FOR r IN SELECT tablename FROM pg_tables where tablename like $1 || '%'  LOOP
    RAISE NOTICE 'tablename: %', r.tablename;
    EXECUTE 'DROP TABLE "' || r.tablename || '" CASCADE';
    count := count + 1;
END LOOP;
RETURN count;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;