package OWL2generator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.ListIterator;
import java.util.Optional;
import java.util.Set;
import java.sql.ResultSet;


public class DBFO_DButils
{
    private static final int columnType_BOOL                     = 1 ;
    private static final int columnType_INT                      = 2 ;
    private static final int columnType_STRING                   = 3 ;
    private static final Set<String> FLAG_COLS = Set.of("funct","invFunct","symm","aSymm","trans","refl","irRefl");
    
    /**
     *  The class conteins the folowing methods
     *
     *      DBupdate()
     *      decodeBFOentityMeaning()
     *      establishResultSet()
     *      getClassAnnotation()
     *      getColumn()
     *      getDisjointClasses()
     *      getInvObjProperty()
     *      getListOfClasses()
     *      getListOfObjProperties()
     *      getListOfDataProperties
     *      getSubclasses()
     *      getSuperclass()
     *      getSubObjProperty()
     *      getSupObjProperty()
     */
    
    DBFO_DButils()
    {
        ;
        
    }   //  end of constructor 
    
    public boolean DBupdate(Connection  DBconnection,
                            String      updateCommand
                           )
    {
        boolean returnValue = false ;
        
        try 
        {
            Statement stmt = DBconnection.createStatement() ;
            stmt.executeUpdate(updateCommand);

            stmt.close();
            
            returnValue = true ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot update the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        } 

        return returnValue ;
        
    }   //  end of method DBupdate
    
    public String decodeBFOentityMeaning(Connection  DBconnection,
                                         String      bfo_entity_name
                                        ) 
    {
        String annotation = "unknown BFO entity name..." ;
     
        ResultSet rs = establishResultSet(DBconnection, 
                       "SELECT bfo_literal FROM BFO_ANNOTATIONS WHERE  " +
                       "bfo_entity_name = \"" + bfo_entity_name + "\" ;" ) ;
        try
        {
            if (rs.next()) 
            {
                annotation = rs.getString("bfo_literal") ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }

        return bfo_entity_name + " - " + annotation ;
    
    }   //  end of method decodeBFOentityMeaning()

    public ResultSet establishResultSet(Connection conn,
                                        String     query
                                       )
    {
        ResultSet rs = null ;

        try 
        {
            Statement stmt = conn.createStatement() ;
            rs = (ResultSet)stmt.executeQuery(query) ;
        } 
        catch (SQLException ex) 
        {
            System.out.println("Cannot query the database") ;
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: "     + ex.getSQLState());
            System.out.println("VendorError: "  + ex.getErrorCode());
            ex.printStackTrace();
        }

        return rs ;

    }   //  end of method establishResultSet()
    
    public String[] getClassAnnotation(String     class_IRI,
                                       Connection DBconnection
                                      )
    {
        String[] annotation = { "", "", ""}; 

        String query =
            "SELECT * FROM CLASS_ANNOTATIONS " + 
                    "WHERE (class_IRI = \"" + class_IRI + "\")" ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
       
        try
        {
        	rs.next();
        	
        	annotation[0] = rs.getString("annotation") ;
        	annotation[1] = rs.getString("annotation_type_IRI") ;
        	annotation[2] = rs.getString("language") ;
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return annotation ;
        
    }   //  end of method getClassAnnotation()
    
    public String _getColumn(Connection  DBconnection,
                            String      tableName,
                            String      columnName,
                            int         columnType,
                            String      whereClause
                           )
    {
        String returnValue = "" ;
        
        ResultSet rs = establishResultSet(DBconnection, 
                       "SELECT " + columnName + " FROM " + tableName + 
                       " " + whereClause + " ;") ;
        try 
        {
            if (rs.next()) 
            {
                switch (columnType)
                {
                    case columnType_BOOL   : 
                        
                         if (rs.getBoolean(columnName) == true) 
                             returnValue = "true" ;
                         else
                             returnValue = "false" ;
                         break; 
                         
                    case columnType_INT    :
                         returnValue = Integer.toString(rs.getInt(columnName)) ;
                         break; 
                         
                    case columnType_STRING :
                         returnValue = rs.getString(columnName) ; 
                         break;
                         
                    default: throw new IllegalArgumentException() ;
                }
            }
            else
            {
                throw new IllegalArgumentException() ;
            }
            
            rs.close() ;  
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot query the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());
            SQLex.printStackTrace();
        }
        catch(IllegalArgumentException ArgEx)
        {
            System.out.println("Field " + columnName + " does not exist in " + tableName + ".") ;
            ArgEx.printStackTrace();
        }

        return returnValue ;
        
    }   //  end of method getColumn()
    
    public ArrayList<String>  getDisjointClasses(String class_IRI,
    		                                     Connection  DBconnection
    		                                    )
    {
    	ArrayList<String> result = new ArrayList<String>() ; 
    	
        String query =  "SELECT owl_class_IRI FROM DISJOINT_OWL_CLASSES " +
                        "WHERE  disjoint_group_id = (SELECT disjoint_group_id FROM DISJOINT_OWL_CLASSES " + 
                            "WHERE  owl_class_IRI = \"" + class_IRI + 
                         "\" ); " ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;

        try
        {
            while (rs.next()) 
            {
                String disjunctClass = rs.getString("owl_class_IRI") ;
            	
            	if (disjunctClass.compareTo(class_IRI) != 0)
                    result.add(disjunctClass) ;
            }
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return result ;
    	
    }   //  end of method getDisjointClasses()

    public String getInvObjProperty(Connection  DBconnection,
                                    String      object_property_IRI
                                   )
    {
      	    String result = "";
    	    
            String query = "SELECT inverse_object_property_IRI FROM INVERSE_OBJECT_PROPERTIES WHERE object_property_IRI = '" + 
                           object_property_IRI.replace("'", "''") + "'";
            
            try (Statement stmt = DBconnection.createStatement();
                 ResultSet rs1   = stmt.executeQuery(query)) {
                
                if (rs1.next()) 
                {  
                    result = rs1.getString("inverse_object_property_IRI");
                } 
                else
                {
                    query = "SELECT object_property_IRI FROM INVERSE_OBJECT_PROPERTIES WHERE inverse_object_property_IRI = '" + 
                           object_property_IRI.replace("'", "''") + "'";
            
                    ResultSet rs2   = stmt.executeQuery(query) ;     
                                                
                    if (rs2.next()) 
                        result = rs2.getString("object_property_IRI") ; 
                    else
                        result = "DB inconsistency !" ;
                }
                
            } catch (SQLException SQLex) {
                System.out.println("Cannot select the database");
                System.out.println("SQLException: " + SQLex.getMessage());
                System.out.println("SQLState: " + SQLex.getSQLState());
                System.out.println("VendorError: " + SQLex.getErrorCode());
                SQLex.printStackTrace();
            }
    	    
    	    return result;    	

    }   //  end of method getInvObjProperty()
    
    public ArrayList<String> getListOfClasses(Connection  DBconnection)
    {
    	ArrayList<String> result = new ArrayList<String>() ; 

        String query = "SELECT class_IRI FROM OWL_CLASSES ;" ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
        try
        {
            while (rs.next()) 
            {
                result.add(rs.getString("class_IRI")) ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return result ;
        
    }   //  end of method getListOfClasses()
    
    public ArrayList<TypeOf_ObjectProperty> getListOfObjProperties(Connection DBconnection)
    {
    	ArrayList<TypeOf_ObjectProperty> result = new ArrayList<TypeOf_ObjectProperty>() ; 

        String query = "SELECT object_property_IRI, " + 
                       "annotation, " + 
                       "annotation_type_IRI, " + 
                       "language FROM OBJECT_PROPERTIES ORDER BY object_property_IRI ;" ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
        try
        {
            while (rs.next()) 
            {
            	TypeOf_ObjectProperty op = new TypeOf_ObjectProperty() ;
            	
            	op.object_property_IRI         = rs.getString("object_property_IRI") ;
            	op.annotation                  = rs.getString("annotation") ;
                op.annotation_type_IRI         = rs.getString("annotation_type_IRI") ;
                op.language                    = rs.getString("language") ;
                op.inverse_object_property_IRI = getInvObjProperty(DBconnection, op.object_property_IRI) ;
           		op.super_object_property_IRI   = getSupObjProperty(DBconnection, op.object_property_IRI) ;
           		
                //  getting object property characteristic flags
           		
                op.opc.ch_functional        = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "funct") ;
                op.opc.ch_inverseFunctional = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "invFunct") ;
                op.opc.ch_symmetric         = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "symm") ;
                op.opc.ch_asymmetric        = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "aSymm") ;
                op.opc.ch_transitive        = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "trans") ;
                op.opc.ch_reflexive         = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "refl") ;
                op.opc.ch_irReflexive       = getObjPropCharactericFlag(DBconnection, op.object_property_IRI, "irRefl") ;

           		op.listOfsubObjProperties = getSubObjProperty(DBconnection, op.object_property_IRI) ;
                ListIterator<String> itr = op.listOfsubObjProperties.listIterator();            
                String subObjectProperties = "" ;

                while (itr.hasNext())
                	subObjectProperties += itr.next() + ", ";
                
                //  cutting of the last " ," 
                if (subObjectProperties.length() > 0)
                    subObjectProperties = subObjectProperties.substring(0, subObjectProperties.length() - 2) ;		

                op.sub_object_property_IRI     = subObjectProperties ;
            	
            	result.add(op) ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return result ;
        
    }   //  end of method getListOfObjProperties()
    
    public ArrayList<TypeOf_DataProperty> getListOfDataProperties(Connection DBconnection)
    {
    	ArrayList<TypeOf_DataProperty> result = new ArrayList<TypeOf_DataProperty>() ; 

        String query = "SELECT "
                         + "data_property_ID, "
                         + "data_property_IRI, "
                         + "data_property_type, "
                         + "data_property_annotationtype, "
                         + "super_data_property_IRI "
                         + "FROM DATA_PROPERTIES ORDER BY data_property_IRI " ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
        try
        {
            while (rs.next()) 
            {
            	TypeOf_DataProperty dp = new TypeOf_DataProperty() ;
            	
                dp.data_property_ID              = rs.getInt(   "data_property_ID"            ) ;
                dp.data_property_IRI             = rs.getString("data_property_IRI"           ) ;
                dp.data_property_type            = rs.getString("data_property_type"          ) ;
                dp.data_property_annotationtype  = rs.getString("data_property_annotationtype") ;
                dp.super_data_property_IRI       = rs.getString("super_data_property_IRI"     ) ;
            	
            	result.add(dp) ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return result ;
        
    }   //  end of method getListOfDataProperties()

    
    public boolean getObjPropCharactericFlag(Connection conn, String iri, String column) 
    {
        Optional<Boolean> result = getObjPropCharacteristics(conn, iri, column) ;
        
        return result.orElse(false);  // Default to false if not found
        
    }   //  end of mathod getObjPropCharactericFlag()
    
    public ArrayList<String>  getSubclasses(String class_IRI,
    		                                Connection  DBconnection
    		                               )
    {
    	ArrayList<String> result = new ArrayList<String>() ; 

        String query = "SELECT class_IRI FROM OWL_CLASSES " + 
                       "WHERE (superclass_of_IRI = \"" + class_IRI + "\")" ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
        try
        {
            while (rs.next()) 
            {
            	result.add(rs.getString("class_IRI")) ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
        
        return result ;
    	
    }   //  end of method getSubclasses()

    public ArrayList<String> getSuperclass(String class_IRI,
    		                               Connection  DBconnection
    		                              )
    {
    	ArrayList<String> result = new ArrayList<String>() ; 

        String query =
            "SELECT superclass_of_IRI FROM OWL_CLASSES " + 
            "WHERE (class_IRI = \"" + class_IRI + "\")" ;
        
        ResultSet rs = establishResultSet(DBconnection, query) ;
        try
        {
            while (rs.next()) 
            {
            	result.add(rs.getString("superclass_of_IRI")) ;
            }
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }

        return result ;
    	
    }   //  end of method getSuperclass()

    public  ArrayList<String> getSubObjProperty(Connection  DBconnection,
                                                String      object_property_IRI
                                               )
    {
    	ArrayList<String> result = new ArrayList<String>() ; 
	    
	    String query = "SELECT object_property_IRI FROM SUPER_OBJECT_PROPERTIES " +
	                   "WHERE  super_object_property_IRI = '" + object_property_IRI.replace("'", "''") + "'";
	    
	    try (Statement stmt = DBconnection.createStatement();
	         ResultSet rs   = stmt.executeQuery(query)) 
	    {
            while (rs.next()) 
            {
            	result.add(rs.getString("object_property_IRI")) ;
            }
        
            rs.close() ;
	        
	    } catch (SQLException SQLex) {
	        System.out.println("Cannot select the database");
	        System.out.println("SQLException: " + SQLex.getMessage());
	        System.out.println("SQLState: " + SQLex.getSQLState());
	        System.out.println("VendorError: " + SQLex.getErrorCode());
	        SQLex.printStackTrace();
	    }
	    
	    return result;    	
        
    }   //  end of method getSubObjProperty()
   

    public String getSupObjProperty(Connection  DBconnection,
                                    String      object_property_IRI
                                   )
    {
        String result = "";
	    
	    String query = "SELECT super_object_property_IRI FROM SUPER_OBJECT_PROPERTIES " +
	                   "WHERE  object_property_IRI = '" + object_property_IRI.replace("'", "''") + "'";
	    
	    try (Statement stmt = DBconnection.createStatement();
	         ResultSet rs   = stmt.executeQuery(query)) {
	        
	        if (rs.next()) 
    	        result = rs.getString("super_object_property_IRI");
	        
	    } catch (SQLException SQLex) {
	        System.out.println("Cannot select the database");
	        System.out.println("SQLException: " + SQLex.getMessage());
	        System.out.println("SQLState: " + SQLex.getSQLState());
	        System.out.println("VendorError: " + SQLex.getErrorCode());
	        SQLex.printStackTrace();
	    }
	    
	    return result;    	
        
    }   //  end of method getSupObjProperty()

    public Optional<Boolean> getObjPropCharacteristics(Connection conn, String iri, String column) 
    {
        if (!FLAG_COLS.contains(column)) 
        	throw new IllegalArgumentException("Bad column in DButils.getFlag()");
        
        String sql = "SELECT " + column + " FROM OBJECT_PROPERTIES WHERE object_property_IRI = ? LIMIT 1";
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) 
        {
            ps.setString(1, iri);
            
            try (ResultSet rs = ps.executeQuery()) 
            {
                if (!rs.next()) 
                	return Optional.empty();
                
                boolean v = rs.getBoolean(1);
                
                return rs.wasNull() ? Optional.empty() : Optional.of(v);
            }
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
            return Optional.empty();
        }
        
    }   //  end of method getObjPropCharacteristics() 

    public boolean chkClassNameValidity(Connection conn, 
    		                            String     class_name
    		                           )
    {
 	    boolean result = false ;
 	   
        String query =   "SELECT class_IRI FROM OWL_CLASSES "
        		       + "WHERE (class_IRI = '" + class_name +  "')"      
        		;
        
        ResultSet rs = establishResultSet(conn, query) ;
        try
        {
            while (rs.next()) 
            	if (class_name.compareTo(rs.getString("class_IRI")) == 0)
            	    result = true ;	
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
 	   
 	   return result ;
 	   
    }   //  end of method chkClassNameValidity()

    public boolean chkObjectPropertyValidity(Connection conn, 
    		                                 String     object_property_IRI
    		                                )
    {
 	    boolean result = false ;
  	   
        String query =   "SELECT object_property_IRI FROM OBJECT_PROPERTIES "
        		       + "WHERE (object_property_IRI = '" + object_property_IRI +  "')"      
        		;
        
        ResultSet rs = establishResultSet(conn, query) ;
        try
        {
            while (rs.next()) 
            	if (object_property_IRI.compareTo(rs.getString("object_property_IRI")) == 0)
            	    result = true ;	
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
 	   
 	   return result ;
 	   
    }   //  end of method chkPredicateValidity()

    public boolean chkDataPropertyValidity(Connection conn, 
    		                               String     data_property_IRI
    		                              )
    {
 	    boolean result = false ;
   	   
        String query =   "SELECT data_property_IRI FROM DATA_PROPERTIES "
        		       + "WHERE (data_property_IRI = '" + data_property_IRI +  "')"      
        		;
        
        ResultSet rs = establishResultSet(conn, query) ;
        try
        {
            while (rs.next()) 
            	if (data_property_IRI.compareTo(rs.getString("data_property_IRI")) == 0)
            	    result = true ;	
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }
 	   
 	   return result ;
    	
    }   //  end of method chkDataPropertyValidity()

    public String getDataPropertyType(Connection conn, String data_property_IRI
                                     )
    {
    	
        String result = "" ;

        String query =   "SELECT data_property_type FROM DATA_PROPERTIES "
                       + "WHERE (data_property_IRI = '" + data_property_IRI +  "')"      
                ;
        
        ResultSet rs = establishResultSet(conn, query) ;
        try
        {
            if (rs.next()) 
                result = rs.getString("data_property_type") ;
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }

        return result ;
        
    }   //  end of method getDataPropertyType()

    public String getIndividualExplanation(Connection conn, String individual)
    {
        String result = "" ;

        String class_IRI = convertIndividualNameToClassName(individual) ;
        
        String query =   "SELECT annotation FROM CLASS_ANNOTATIONS "
                       + "WHERE (class_IRI = '" + class_IRI +  "')"  ;
        
        ResultSet rs = establishResultSet(conn, query) ;
        try
        {
            if (rs.next()) 
                result = rs.getString("annotation") ;
        
            rs.close() ;
        }
        catch(SQLException SQLex)
        {
            System.out.println("Cannot select the database") ;
            System.out.println("SQLException: " + SQLex.getMessage());
            System.out.println("SQLState: "     + SQLex.getSQLState());
            System.out.println("VendorError: "  + SQLex.getErrorCode());

            SQLex.printStackTrace();
        }

        return result ;

    }   //  end of method getIndividualExplanation()

    public String convertIndividualNameToClassName(String individual)
    {
    	individual    = individual.substring(0, individual.length() - 9) ;
    	
    	return cvtThe1stCharToUppercase(individual) ;
    			
    }   //  end of method convertIndividualNameToClassName()
    
    private String cvtThe1stCharToUppercase(String s) 
    {
        if (s == null || s.isEmpty()) 
            return s;
        
        int firstCodePoint = s.codePointAt(0);
        int lowerFirst     = Character.toUpperCase(firstCodePoint);
        int firstLen       = Character.charCount(firstCodePoint);
        
        if (firstCodePoint == lowerFirst) 
            return s;

        return new StringBuilder(s.length())
                                  .appendCodePoint(lowerFirst)
                                  .append(s.substring(firstLen))
                                  .toString();
        
    }   //  end of method cvtThe1stCharToUppercase ()
    
}   //  end of class DButils