package OWLpreprocessing;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

 
public class OWL_DButils 
{
    private final int columnType_BOOL                     = 1 ;
    private final int columnType_INT                      = 2 ;
    private final int columnType_STRING                   = 3 ;
	
	OWL_DButils()
	{ ; }

    
    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 ResultSet establishResultSet(Connection DBconnection,
                                        String     query
                                       )
    {
        ResultSet rs = null ;

        try 
        {
            Statement stmt = DBconnection.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 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() ;
            }
        }
        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 int getIncrementedIndividualCount(java.sql.Connection conn, String classIri) 
    {
        int count = 0;
    
        // Session diagnostics
        try (java.sql.Statement s = conn.createStatement();
             java.sql.ResultSet rs = s.executeQuery(
                 "SELECT DATABASE(), @@autocommit, @@session.tx_read_only, @@transaction_isolation")) 
        { }
        catch (java.sql.SQLException ignore) {}
    
        // Read current count
        try (java.sql.PreparedStatement psSel = conn.prepareStatement(
                "SELECT class_individual_count FROM DBFOsociology.OWL_CLASSES WHERE class_IRI = ?")) 
        {
            psSel.setString(1, classIri);
            try (java.sql.ResultSet rs = psSel.executeQuery()) 
            {
                if (rs.next()) 
                {
                    count = rs.getInt(1);
                }
            }
        } 
        catch (java.sql.SQLException e) 
        {
            System.out.println("Cannot query the database");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
            e.printStackTrace();
            return count;
        }
        
        int newCount = count + 1;
        
        boolean originalAutoCommit = true;
        try 
        {
            originalAutoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
    
            try (java.sql.PreparedStatement psUpd = conn.prepareStatement(
                    "UPDATE DBFOsociology.OWL_CLASSES SET class_individual_count = ? WHERE class_IRI = ?")) 
            {
                psUpd.setInt(1, newCount);
                psUpd.setString(2, classIri);
                psUpd.executeUpdate();
            }
    
            conn.commit();
        } 
        catch (java.sql.SQLException e) 
        {
            try { conn.rollback(); } catch (java.sql.SQLException ignore) 
            {}
            
            System.out.println("Cannot update the database");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
            e.printStackTrace();
        } 
        finally 
        {
            try 
            { conn.setAutoCommit(originalAutoCommit); } 
            catch (java.sql.SQLException ignore) 
            { }
        }
    
        // Verify after commit
        try (java.sql.PreparedStatement psVerify = conn.prepareStatement(
                "SELECT class_individual_count FROM DBFOsociology.OWL_CLASSES WHERE class_IRI = ?")) 
        {
            psVerify.setString(1, classIri);
            try (java.sql.ResultSet rsVerify = psVerify.executeQuery()) 
            { }
        } 
        catch (java.sql.SQLException e) 
        {
            System.out.println("Cannot verify the database value");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
            e.printStackTrace();
        }
    
        return newCount;
        
    }   //  end of method getIncrementedIndividualCount() 

    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()

}   //  end of class OWL_DButils
