package dqMonitor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.sql.Date;
import java.util.Calendar;
import java.util.ListIterator;


public class DQMeasure 
{
    private Connection metadataConnection;
    private ConnectionList connectionList;
    private DQQueryList dqQueryList;
    private LogFile logFile;
    private DQQueryElement queryElement;


    public DQMeasure(DQQueryList dqQueryList, Connection metadataConnection, LogFile logFile)
    {
        this.metadataConnection = metadataConnection;
        this.logFile = logFile;
        this.dqQueryList = dqQueryList;
        connectionList = new ConnectionList(metadataConnection, logFile);
    }
    
//    private int getNextId(Connection connection)
//    {
//        This version assumes an Oracle repository for the metadata database.
//        
//        Statement statement = null;
//        ResultSet resultSet = null;
//        int next_id = 0;
//        
//        try 
//        {
//            statement = connection.createStatement();
//        } 
//        catch (SQLException e) 
//        {
//            e.printStackTrace();
//        }
//        
//        try
//        {
//            resultSet = statement.executeQuery
//            (
//                "select data_quality_sequence.nextval as next_id from dual"
//            );
//        }
//        catch(SQLException sqle)
//        {
//            sqle.printStackTrace();
//            System.out.println(sqle);
//        }
//        
//        try
//        {
//            resultSet.next();
//            next_id = resultSet.getInt("next_id");
//        } 
//        catch (SQLException e) 
//        {
//            e.printStackTrace();
//        }
//        
//        try 
//        {
//            statement.close();
//        } 
//        catch (SQLException e1) 
//        {
//            e1.printStackTrace();
//        }
//
//        return next_id;
//    }
    

    public void insertUpdateDQMeasure
    (
        int      dqFactorId,
        Date     runDate,
        double   value,
        LogFile  logFile
    )
    {
    	String             selectMeasure;
    	PreparedStatement  selectStatement = null;
        ResultSet          selectResultSet = null;
        String             insertMeasure;
        PreparedStatement  insertStatement = null;
        String             updateMeasure;
        PreparedStatement  updateStatement = null;
        int                rowsAffected = 0;
        boolean            valueFound;
        int                measureId = 0;
        double             oldValue;
        DateFormat         runDateFormat;
        String             runDateString;

        runDateFormat = new SimpleDateFormat("yyyy-MM-dd");     // default date format for PostgreSQL, also works with Oracle


        
        selectMeasure = new String
        (
            "select " +
                "id, " +
                "value " +
            "from " +
                "measure " +
            "where " +
                "factor_id = ? and " +     // 1. factor_id
                "run_date = ? "            // 2. run_date
        );       
        
        try 
        {
            selectStatement = metadataConnection.prepareStatement(selectMeasure);
        } 
        catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }

        try 
        {
			selectStatement.setInt   (1, dqFactorId);
			selectStatement.setDate  (2, runDate);
		} 
        catch (SQLException e) 
        {
        	logFile.logMessage(e.toString());
		}
        
        try
        {
            selectResultSet = selectStatement.executeQuery();
        }
        catch(SQLException e)
        {
            logFile.logMessage(e.toString());
        }

        valueFound = false;
        try
        {
            valueFound = selectResultSet.next();
        } 
        catch (SQLException e)
        {
            logFile.logMessage(e.toString());
        }

        oldValue = value;
    	runDateString = new String(runDateFormat.format(runDate.getTime()));
        if (valueFound)
        {
            try 
            {
                measureId = selectResultSet.getInt("id");
                oldValue = selectResultSet.getDouble("value");
            } 
            catch (SQLException e) 
            {
                logFile.logMessage(e.toString());
            }
        }
        
        try 
        {
            selectStatement.close();
        } 
        catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }

        if (valueFound)
        {
            if (oldValue == value)
            {
                logFile.logMessage("Factor " + dqFactorId + ", date " + runDateString + " no change in value.");
            }
            else
            {
            	updateMeasure = new String
                (
                    "update measure " +
                    "set " +
                         "value = ? , " +            // 1. measure value
                         "updated_at = now() " +
                    "where " +
                        "id = " + measureId
                );
            	
            	
            	
            	try 
                {
                    updateStatement = metadataConnection.prepareStatement(updateMeasure);
                } 
                catch (SQLException e) 
                {
                    logFile.logMessage(e.toString());
                }
                
                try 
                {
    				updateStatement.setDouble(1, value);
    			} 
                catch (SQLException e) 
                {
                    logFile.logMessage(e.toString());
    			}

                try
                {
                    rowsAffected = updateStatement.executeUpdate();
                    
                    logFile.logMessage("Measure " + measureId + ", factor " + dqFactorId + ", date " + runDateString + " updated.");
                }
                catch(SQLException e)
                {
                    logFile.logMessage
                    (
                        "Failed to update MEASURE, Measure " +
                        measureId +
                        ", factor " + 
                        dqFactorId + 
                        ", date " + 
                        runDate + 
                        ".  " +
                        e.toString()
                    );
                }
                if (rowsAffected > 1)
                    logFile.logMessage("Measure " + measureId + ", factor " + dqFactorId + ", date " + runDateString + " has duplicates.");
                
                try 
                {
                    updateStatement.close();
                } 
                catch (SQLException e) 
                {
                    logFile.logMessage(e.toString());
                }
            }
        }
        else            // no value found so insert new row
        {
//            measureId = getNextId(metadataConnection);    // Not needed with Postgres repository
        	
        	insertMeasure = new String
            (
                "insert into measure " +
                "(" +
                    "factor_id, " +
                    "run_date, " +
                    "value " +
                ") " +
                "values " +
                "(" +
                    " ? , " +    // 1. factor_id
                    " ? , " +    // 2. run_date
                    " ? " +      // 3. value
                ")"
            );
        	
            try 
            {
                insertStatement = metadataConnection.prepareStatement(insertMeasure);
            } 
            catch (SQLException e) 
            {
                logFile.logMessage(e.toString());
            }
            
            try 
            {
				insertStatement.setInt   (1, dqFactorId);
				insertStatement.setDate  (2, runDate);
				insertStatement.setDouble(3, value);
			} 
            catch (SQLException e) 
            {
                logFile.logMessage(e.toString());
			}
            
            try
            {
                rowsAffected = insertStatement.executeUpdate();
                
//                logFile.logMessage("Factor " + dqFactorId + ", date " + runDate + " inserted.");
            }
            catch(SQLException e)
            {
                logFile.logMessage(e.toString());
            }
            
            logFile.logMessage("Factor " + dqFactorId + ", date " + runDateString + " inserted.");
            
            try 
            {
                insertStatement.close();
            } 
            catch (SQLException e) 
            {
            	runDateString = new String(runDateFormat.format(runDate.getTime()));
                logFile.logMessage
                (
                    "Failed to insert into MEASURE, ID " +
                    measureId +
                    "Factor " + 
                    dqFactorId + 
                    ", date " + 
                    runDateString + 
                    ".  " +
                    e.toString()
                );
            }
        }
//      Needed for Oracle repository; not needed for Postgres
//      try 
//      {
//          metadataConnection.commit();
//      } 
//      catch (SQLException e) 
//      {
//          e.printStackTrace();
//      }
    }

    public void populate(String queryFileLocation)
    {
        String                         queryFileName;
        int                            connectionId;
        String                         query;
        ListIterator<DQQueryElement>   dqQueryIterator;
        DQMeasureList                  dqMeasureList;
        ListIterator<DQMeasureElement> dqMeasureIterator;
        QueryFile                      queryFile;
        Calendar                       startDate;
        Calendar                       endDate;
        long                           startTime;
        long                           endTime;
        
        int dqFactorId = 0;
        Date runDate = null;
        double value = 0;
        DQMeasureElement dqMeasureElement;

        for (dqQueryIterator = dqQueryList.listIterator(); dqQueryIterator.hasNext(); )
        {
            queryElement = (DQQueryElement) dqQueryIterator.next();
            queryFileName = queryElement.getQueryFile();    // This is the name of the file... need to open it and read it
            connectionId = queryElement.getConnectionId();
                    
            queryFile = new QueryFile(queryFileLocation + queryFileName, logFile);
            query = queryFile.getQuery();
            queryFile.close();
            
            if (query.isEmpty())
            {
                logFile.logMessage("No query to run.  Query may have been invalid.  See messages above.");
            }
            else
            {
                startDate = Calendar.getInstance();
                startTime = startDate.getTimeInMillis();
                dqMeasureList = new DQMeasureList(connectionId, connectionList, query, logFile);
                endDate = Calendar.getInstance();
                endTime = endDate.getTimeInMillis();
                
                dqQueryList.updateQuery(queryElement.getQueryId(), (endTime - startTime), logFile);
                
                for (dqMeasureIterator = dqMeasureList.listIterator(); dqMeasureIterator.hasNext(); )
                {
                    dqMeasureElement = (DQMeasureElement) dqMeasureIterator.next();
                    
                    dqFactorId = dqMeasureElement.getDqFactorId();
                    runDate = dqMeasureElement.getRunDate();
                    value = dqMeasureElement.getValue();
    
                    insertUpdateDQMeasure(dqFactorId, runDate, value, logFile);
                }
            }
        }
    }
}