import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;


public class CrowdSource_Database {
 private Connection connection;

     public CrowdSource_Database() throws SQLException {
         try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
            connection = DriverManager.getConnection("jdbc:hsqldb:file:"
            + "/Users/Aamer/Documents/Java/CS_DB","SA","");

            Statement delayStmt = connection.createStatement();
            try {delayStmt.execute("SET WRITE_DELAY FALSE");}  //Always update data on disk
            finally {delayStmt.close();}

            Statement sqlStmt = connection.createStatement();
            try {
             sqlStmt.execute("CREATE TABLE messages(contact VARCHAR(255) NOT NULL,"+
                             "message VARCHAR(4096) NOT NULL,timeposted BIGINT NOT NULL ,up INT NOT NULL DEFAULT 0, down INT NOT NULL DEFAULT 0, score LONG NOT NULL DEFAULT 0 )");
            } catch (SQLException e) {
             System.out.println("Warning: Database table \"messages\" already exists.");
            } finally {
             sqlStmt.close();
             connection.commit();
            }


     }


     public void close() throws SQLException {
         connection.close();
     }

     public void incrementUp(String contact_ ) throws SQLException {
         String stmt = "UPDATE messages SET up = up+1 WHERE contact= contact_";
            Statement updatevalue = connection.createStatement();

         try{
             updatevalue.execute(stmt);
        } finally {
             updatevalue.close();
             connection.commit();
        }
     }

     public void reset(){
         try {
                Statement stmt = connection.createStatement();


                String sql = "TRUNCATE messages";


                sql = "DELETE FROM messages";


                stmt.executeUpdate(sql);
            } catch (SQLException e) {
            }
     }

       public void incrementDown(String contact_ ) throws SQLException {
             String stmt = "UPDATE messages SET down = down+1 WHERE contact= contact_";
                Statement updatevalue = connection.createStatement();

             try{
                 updatevalue.execute(stmt);
            } finally {
                 updatevalue.close();
                 connection.commit();
            }




     }

       public void addMessage(ScamQuery m) throws SQLException {

             String stmt = "INSERT INTO MESSAGES(contact,message,timeposted) VALUES (?,?,?)";
              PreparedStatement insertMessage = connection.prepareStatement(stmt);

              try {
               insertMessage.setString(1, m.getContact()); //set value of first "?" to "Alastair"
               insertMessage.setString(2, m.getMessage());
               insertMessage.setLong(3, System.currentTimeMillis());
               insertMessage.executeUpdate();

              } finally { //Notice use of finally clause here to finish statement
               insertMessage.close();
               connection.commit();

              }

         }

       public List<ScamQuery> getQueries() throws SQLException {

             String stmt = "SELECT nick,message,timeposted,up,down,score FROM messages "+
             "ORDER BY timeposted";

             List<ScamQuery> store = new LinkedList<ScamQuery>();
             PreparedStatement recentMessages = connection.prepareStatement(stmt);
             try {
                 ResultSet rs = recentMessages.executeQuery();
                 try {
                     while (rs.next())
                         store.add(new ScamQuery(rs.getString(1), rs.getString(2), new Date(rs.getLong(3)), rs.getInt(4), rs.getInt(5), rs.getLong(6) ));

                 } finally {
                     rs.close();
                 }
             }
                finally {
                    recentMessages.close();
                }
                Collections.reverse(store);
            return store;


         }

       public long confidence (int up, int down){

            int n = up + down;
            if(n==0){
                return 0;
            }

            double prop = up/n;
            double z = 1.96; //95% confidence interval

            double confd = (prop + z*z/(2*n) - z * Math.sqrt((prop*(1-prop)+z*z/(4*n))/n))/(1+z*z/n);
            long conf = Math.round(confd);
            return conf;

        }

       public long calculateScore(String _contact) throws SQLException{
           String _up = "SELECT up FROM messages WHERE contact=_contact";
           String _down = "SELECT down FROM messages WHERE contact=_contact";
           int up;
           int down;
           PreparedStatement upstmt = connection.prepareStatement(_up);
           PreparedStatement downstmt = connection.prepareStatement(_down);
           try{
               ResultSet ups = upstmt.executeQuery();
               ResultSet downs = downstmt.executeQuery();
               try{
                   up = ups.getInt(1);
                   down = downs.getInt(1);
               }
               finally{
                   ups.close();
                   downs.close();
               }

           }
           finally{

                   upstmt.close();
                   downstmt.close();

           }
           long score = confidence(up, down);
           return score;


       }

       public void updateScore(String _contact) throws SQLException{
           @SuppressWarnings("unused")
        long newScore = calculateScore(_contact);

           String stmt = "UPDATE messages SET score = newScore WHERE contact= contact_";
            Statement updatevalue = connection.createStatement();

         try{
             updatevalue.execute(stmt);
        } finally {
             updatevalue.close();
             connection.commit();
        }
       }
}