package dqMonitor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.LinkedList;

public class DQQueryList extends LinkedList<DQQueryElement>
{
    private static final long serialVersionUID = 1L;
    private Statement selectQueryStatement;
    private ResultSet queryResultSet;
    private boolean queryFound;
    private Connection connection;
    private Calendar today;
    private DateFormat dateFormat;
    private String todayAsString;


    public DQQueryList(Connection connection, LogFile logFile)
    {
        
        this.connection = connection;

        today = Calendar.getInstance();
        dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
        todayAsString = new String(dateFormat.format(today.getTime()));
        
        try
        {
            selectQueryStatement = connection.createStatement();
        }
        catch(SQLException e)
        {
            logFile.logMessage(e.toString());
        }

        try
        {
            queryResultSet = selectQueryStatement.executeQuery
            (
                "select " +
                    "id, " +
                    "query_file, " +
                    "run_frequency, " +
                    "connection_id " +
                "from " +
                    "query " +
                "where " +
                    "last_run_date < to_date('" + todayAsString + "', 'DD-Mon-YYYY') " +
                "order by " +
                    "last_elapsed_time, " +
                    "id"
            );

        }
        catch(SQLException e)
        {
            logFile.logMessage(e.toString());
        }
    }
    
    public void load(LogFile logFile)
    {
        Calendar today;
        int dayOfWeek;
        String runFrequency;
        queryFound = true;
        int connectionId;
        DQQueryElement queryElement;


        today = Calendar.getInstance();
        dayOfWeek = today.get(Calendar.DAY_OF_WEEK);
        
        while(queryFound)
        {
            try
            {
                queryFound = queryResultSet.next();
            } 
            catch (SQLException e)
            {
                logFile.logMessage(e.toString());
            }
            
            if (queryFound)
            {
                try 
                {
                    runFrequency = new String(queryResultSet.getString("run_frequency"));
                    connectionId = queryResultSet.getInt("connection_id");
                    if ((dayOfWeek  == Calendar.SUNDAY) || runFrequency.equals("Daily"))
                    {
                        try
                        {
                            queryElement = new DQQueryElement
                            (
                                connectionId, 
                                queryResultSet.getString("query_file"), 
                                queryResultSet.getInt("id")
                            );
                            this.add(queryElement);
                        } 
                        catch (SQLException e)
                        {
                            logFile.logMessage(e.toString());
                        }
                    }
                } 
                catch (SQLException e) 
                {
                    logFile.logMessage(e.toString());
                }

            }
        }
        try 
        {
            selectQueryStatement.close();
        } catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }
    }
    
    public void updateQuery(int queryId, long lastElapsedTime, LogFile logFile)
    {
        Statement statement = null;    
        
        try 
        {
            statement = connection.createStatement();
        } 
        catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }
        
        try 
        {
            statement.executeUpdate
            (
                "update query " +
                "set " +
                    "last_elapsed_time = " + (lastElapsedTime / 1000) + ", " +
                    "last_run_date = to_date('" + todayAsString + "', 'DD-Mon-YYYY') " +
                "where " +
                    "id = " + queryId                    
            );
        } 
        catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }

//        Needed for Oracle repository but not for Postgres
//        try 
//        {
//            connection.commit();
//        } 
//        catch (SQLException e) 
//        {
//            e.printStackTrace();
//        }
        
        try 
        {
            statement.close();
        } 
        catch (SQLException e) 
        {
            logFile.logMessage(e.toString());
        }
    }
}