CREATE OR REPLACE PACKAGE BODY MODELSERVICE AS

    FUNCTION_C VARCHAR2(255) := 'CMPFunction';
    UNIQUEKEY_C VARCHAR2(255) := 'CMPUniqueKey';

PK_FC VARCHAR2(255) := 'oracle.wh.repos.impl.items.CMPPrimaryKey';
UK_FC VARCHAR2(255) := 'oracle.wh.repos.impl.items.CMPUniqueKey';

FUNCTION deleteAllPropertyValues(propDef IN NUMBER, cname IN VARCHAR2, viewName IN VARCHAR2, isFCO IN NUMBER) 
RETURN NUMBER IS
    phyID NUMBER;
    fcoID NUMBER;
    isPK NUMBER;
BEGIN
    if (viewName=UNIQUEKEY_C) then
        if (cname=PK_FC) then
            isPK := 1;
        else 
            isPK := 0;
        end if;
        for c in (select /*+ORDERED*/ nvl(pu.definitionkey, pd.definitionkey) definitionkey
                  from PropertyUsageDefinition_v pu, PropertyDefinition_v pd
                  where pu.propertyDefinition=propDef and pu.propertydefinition=pd.elementId
            ) loop
            delete /*+ORDERED*/ CMPStringPropertyValue_v where propertyowner in 
              ( select /*+ORDERED*/ r.elementId from CMPPhysicalObject_v p, 
                     CMPReferencePropertyValue_v r, CMPUniqueKey_v u
                where r.referencedelement=u.elementid and r.logicalname=UDP_KEY
                      and r.propertyowner=p.elementid  
              ) and logicalname=c.definitionKey;
            delete /*+ORDERED*/ CMPClobPropertyValue_v where propertyowner in 
              ( select /*+ORDERED*/ r.elementId from CMPPhysicalObject_v p, 
                     CMPReferencePropertyValue_v r, CMPUniqueKey_v u
                where r.referencedelement=u.elementid and r.logicalname=UDP_KEY
                      and r.propertyowner=p.elementid 
              ) and logicalName=c.definitionKey;
        end loop;
        return 0;
    else        
        for c in (select /*+ORDERED*/ nvl(pu.definitionkey, pd.definitionkey) definitionkey
                  from PropertyUsageDefinition_v pu, PropertyDefinition_v pd
                  where pu.propertyDefinition=propDef and pu.propertydefinition=pd.elementId
            ) loop
            if (isFCO=1) then
              delete /*+ORDERED*/ CMPStringPropertyValue_v where propertyowner in 
                ( select /*+ORDERED*/ p.elementId from CMPPhysicalObject_v p, 
                       FirstClassObject_v u
                  where p.logicalObject=u.elementId 
                  and (u.strongtypename= cname
                       or 
                       (u.strongtypename is null and u.className=viewName))
                ) and logicalname=c.definitionKey;
              delete /*+ORDERED*/ CMPClobPropertyValue_v where propertyowner in 
                ( select /*+ORDERED*/ p.elementId from CMPPhysicalObject_v p, 
                       FirstClassObject_v u
                  where p.logicalObject=u.elementid 
                  and (u.strongtypename= cname
                       or 
                       (u.strongtypename is null and u.className=viewName))
                ) and logicalName=c.definitionKey;
            else   
              delete /*+ORDERED*/ CMPStringPropertyValue_v where propertyowner in 
                ( select /*+ORDERED*/ r.elementId from CMPPhysicalObject_v p, 
                       CMPReferencePropertyValue_v r, SecondClassObject_v u
                  where p.elementId=r.propertyowner and r.logicalName=UDP_KEY 
                        and r.referencedelement=u.elementid 
                        and (u.strongtypename= cname
                            or 
                            (u.strongtypename is null and u.className=viewName))
                ) and logicalname=c.definitionKey;
             delete /*+ORDERED*/ CMPClobPropertyValue_v where propertyowner in 
                ( select /*+ORDERED*/ r.elementId from CMPPhysicalObject_v p, 
                       CMPReferencePropertyValue_v r, SecondClassObject_v u
                  where r.referencedelement=u.elementid and r.logicalname=UDP_KEY
                        and r.propertyowner=p.elementid 
                        and (u.strongtypename= cname
                            or 
                            (u.strongtypename is null and u.className=viewName))
                ) and logicalName=c.definitionKey;
            end if;
        end loop;
        return 0;
    end if;
    --return 0;
EXCEPTION
    WHEN OTHERS THEN
        return 1;
END;

   FUNCTION getClassID(cName IN VARCHAR2) RETURN NUMBER IS
       defID NUMBER(9);
   BEGIN 
       select elementId into defID from ClassDefinition_v 
       where name like '%.' || cName or name like cName;

       return defID;
   EXCEPTION 
       WHEN OTHERS THEN 
           snapshot.debug('getClassID(VARCHAR2) failed on ', cName);
           return 0;
   END;  

/*
   FUNCTION unclear(cname IN VARCHAR2) RETURN BOOLEAN IS 
   BEGIN
       if (cname=RELATIONALDBITEM_C OR cname=FUNCTION_C OR cname=UNIQUEKEY_C
           OR cname=STAGECOMPONENT_C OR cname=MAPPING_C) then
           return true;
       else 
           return false;
       end if;
   END;
*/

   PROCEDURE cleanProperties IS       
   BEGIN       
       for c in (select elementId, type from tempfco 
                 where type=SnapshotCreateRestore.FCOCHILDREN) loop
           --snapshot.debug('cleanProperties for ', 
           --           'Element ' || c.elementId || ' type ' || c.type);
           cleanProperties(c.elementId);
       end loop;
   EXCEPTION
       WHEN OTHERS THEN
           snapshot.debug('cleanProperties() failed ', '');
   END; 
  
   -- process 8 primitive types
   FUNCTION isValid(tname IN VARCHAR2, value IN VARCHAR2) RETURN VARCHAR2 IS
       d DATE;
       f NUMBER(38);
       t DATE;
       dp VARCHAR2(255);
       sp VARCHAR2(255);
       idx NUMBER(9);
   BEGIN
       if (tname=STRING_TYPE) THEN
           return 'Y';
       elsif (tname=URL_TYPE) THEN
           return 'Y';
       elsif (tname=BOOLEAN_TYPE) THEN
           if (value='true' OR value='false') then
               return 'Y';
           else 
               return 'N';
           end if;
       elsif (tname=DATE_TYPE) THEN
           d := to_date(value, 'FXYYYY-MM-DD');
           return 'Y';
       elsif (tname=DOUBLE_TYPE) THEN
           f := to_number(value, 'TM9');
           return 'Y';
       elsif (tname=FLOAT_TYPE) THEN
           f := to_number(value, '99999999.99999999');
           return 'Y';
       elsif (tname=INTEGER_TYPE) THEN
           f := to_number(value, '999999');
           return 'Y';
       elsif (tname=LONG_TYPE) THEN 
           f := to_number(value, '999999999999');
           return 'Y';
       elsif (tname=TIMESTAMP_TYPE) THEN
           idx := instr(value, ',');
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 'idx for , is ' || idx);
           if (idx>0) then
               return 'N';
           end if;           
           idx := instr(value, '.', -1);
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 'idx for . is ' || idx);
           if (idx=0) then 
               return 'N';
           end if;
           dp := substr(value, 1, idx-1);
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 'dp is "' || dp || '"');  
           sp := substr(value, idx+1);
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 'sp is "' || sp || '"');
           t := to_date(dp, 'YYYY-MM-DD HH:MI:SS.');
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 't is ' || t);
           f := to_number(sp, '999999999');
           --snapshot.debug('isValid(VARCHAR2, VARCHAR2): ', 'f is ' || f);
           return 'Y';
       else 
           return 'N';
       end if;
   EXCEPTION
       WHEN OTHERS THEN
           snapshot.debug('isValid(VARCHAR2, VARCHAR2) failed for ', 
                          tname || '/' || value);
           return 'N';
   END;  

   PROCEDURE cleanPropertiesInOwner (cDef IN NUMBER, powner IN NUMBER) IS
       cnt NUMBER;
   BEGIN
       -- delete invalid string properties 
       delete /*+ORDERED*/ CMPStringPropertyValue_V 
           where elementId in (
             select /*+ORDERED*/ v.elementId 
             from propertysetdefinition_v s, propertyusagedefinition_v u, 
                  PropertyDefinition_V p, WBPrimitiveType_V t, 
                  CMPStringPropertyValue_V v
             where s.classdefinition in 
                   (select distinct inheritancechild 
                    from classinheritancedefinition_v i 
                    start with inheritancechild = cDef connect by 
                    prior inheritanceparent = inheritancechild
                   ) 
                 and s.propertytype=USER_DEFINED and s.elementid=u.propertysetdefinition
                 and p.elementId=u.propertydefinition 
                 and t.elementid=p.datatype and v.propertyowner=powner
                 and v.logicalname=nvl(u.definitionkey, p.definitionkey)
                 and isValid(t.name, v.value)<>'Y'
           );                 
           
       -- CLOB contains lines so need some special process
       delete /*+ORDERED*/ CMPCLOBPropertyValue_V
           where elementId in (
           select /*+ORDERED*/ v.elementId 
           from propertysetdefinition_v s, propertyusagedefinition_v u, 
                PropertyDefinition_V p, WBPrimitiveType_V t, 
                CMPCLOBPropertyValue_V v
           where s.classdefinition in 
                 (select distinct inheritancechild 
                  from classinheritancedefinition_v i 
                  start with inheritancechild = cDef connect by 
                  prior inheritanceparent = inheritancechild
                 ) 
               and s.propertytype=USER_DEFINED and s.elementid=u.propertysetdefinition
               and p.elementId=u.propertydefinition 
               and t.elementid=p.datatype and v.propertyowner=powner
               and v.logicalname=nvl(u.definitionkey, p.definitionkey)
               and t.name<>'String'
           );

   EXCEPTION
       WHEN OTHERS THEN
           snapshot.debug('cleanPropertiesInOwner(NUMBER, NUMBER) failed on ', 
                  'ClassDef ' || cDef || ', Property Owner ' || powner);
   END;
   
   PROCEDURE cleanProperties (logId IN NUMBER, phyID IN NUMBER) IS
       cname VARCHAR2(255);
       uid VARCHAR2(255);
       fcoDefID NUMBER(38);
   BEGIN
       for c in (select o.elementId ownerId, 
                        decideClassDefId(s.className, s.elementId) defId
              from SecondClassObject_v s, CMPReferencePropertyValue_v o 
              where s.firstClassObject=logId and o.propertyOwner=phyId
                    and o.referencedElement=s.elementId
                    and o.firstClassObject=logId 
                    and o.logicalname=UDP_KEY) 
       loop 
           -- snapshot.debug('cleanProperties(NUMBER, NUMBER): ', 
           --         'owner is ' || c.ownerId || ', def is ' || c.defId);
           cleanPropertiesInOwner(c.defId, c.ownerId);
       end loop;

       select decideClassDefId(className, logId) into fcoDefID 
       from CMPElement_V where elementId=logId;
       
       --snapshot.debug('cleanProperties(NUMBER, NUMBER): ', 
       --          'physical ID is ' || phyID || ', fcoDefID is ' || fcoDefID);
       cleanPropertiesInOwner(fcoDefID, phyID);

   EXCEPTION 
       WHEN OTHERS THEN
           snapshot.debug('cleanProperties(NUMBER, NUMBER) failed on ', 
                          'Logical ' || logID || ', Physical ' || phyID);
   END;         
   
   PROCEDURE cleanProperties (elemId IN NUMBER) IS
   BEGIN
       for c in (select elementId from CMPPhysicalObject_V 
                 where logicalObject=elemId) loop
           -- snapshot.debug('cleanProperties(NUMBER): ', '' || elemId);
           cleanProperties(elemId, c.elementId);
       end loop; 
   EXCEPTION
       WHEN OTHERS THEN
           snapshot.debug('cleanProperties(NUMBER) failed on ', 'Element ' || elemId);
   END;                     
       
   FUNCTION decideClassDefId(viewName IN VARCHAR2, elemId IN NUMBER) RETURN NUMBER IS
       cName VARCHAR2(255);
   BEGIN 
       cName := viewName;
       if (viewName=UNIQUEKEY_C) then
           select decode(primaryKey, 1, 'CMPPrimaryKey', 'CMPUniqueKey') into cName 
           from CMPUniqueKey_V where elementId=elemId;
       else 
           select nvl(StrongTypeName, className) into cName from CMPElement_v 
           where elementId=elemId;
       end if;        
       return getClassID(cName);
   EXCEPTIOn
       WHEN OTHERS THEN
           snapshot.debug('decideClassDefId(NUMBER) failed on ', 'Element ' || elemId);
           return 0;
   END;

   FUNCTION getFullClassName(cname in VARCHAR2) RETURN VARCHAR2
   IS 
       fname VARCHAR2(255);
   BEGIN
       select fullname into fname from FullClassNameLookup
       where name=cname;
       return fname;
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
           return cname;
   END;
   
   FUNCTION getClassId(cname IN NUMBER) RETURN NUMBER 
   IS
       fname VARCHAR2(255);
       cid NUMBER;
   BEGIN
       fname := getFullClassName(cname);
       
       select elementId into cid from ClassDefinition_v 
       where name=fname;
       return cid;
   EXCEPTION 
       WHEN NO_DATA_FOUND THEN
           return null;
   END;

   FUNCTION getComponentKind(cname IN VARCHAR2) RETURN NUMBER 
   IS
       cid NUMBER;
   BEGIN
       cid := getClassId(cname);       
       return getComponentKind(cid);
   EXCEPTION
       WHEN OTHERS THEN
           return UNKNOWN;  
   END;

   FUNCTION getComponentKind(cid IN NUMBER) RETURN NUMBER     
   IS 
       res NUMBER(1);
   BEGIN
       if (cid is null) then
           return UNKNOWN;
       end if;
       if (FCO_ID is null) then
           select elementId into FCO_ID from ClassDefinition_v where name=FCO_CLASS;
           select elementId into SCO_ID from ClassDefinition_v where name=SCO_CLASS;
           select elementId into FOLDER_ID from ClassDefinition_v where name=FOLDER_CLASS;
       end if;
       if (cid = FCO_ID) THEN
           return FCO;
       elsif (cid = SCO_ID) THEN
           return SCO;
       elsif (cid = FOLDER_ID) THEN
           return FOLDER;
       end if;
       for c in (select inheritanceParent pid from ClassInheritanceDefinition_V 
                 where inheritanceChild=cid)
       loop 
           res := getComponentKind(cid);
           if (res <> UNKNOWN) THEN
               return res;
           end if;
       end loop;
       return UNKNOWN;
   END;
       
END MODELSERVICE;
/