package com.sangdol.experiment.portableDb;

import org.h2.jdbcx.JdbcConnectionPool;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hugh
 */
public class ViewDao {
    public static final DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS");
    private static final JdbcConnectionPool cp = JdbcConnectionPool.create("jdbc:h2:./view", "sa", "");

    public ViewDao() throws ClassNotFoundException {
        Class.forName("org.h2.Driver");
        cp.setMaxConnections(50);
    }

    public List<View> getLatest10Visitors(int userId) {
        List<View> views = new ArrayList<>();
        try (Connection connection = cp.getConnection()) {
            Statement statement = connection.createStatement();

            String tenDaysAgo = DateTime.now().minusDays(10).toString(fmt);
            ResultSet rs = statement.executeQuery(String.format(
                    " SELECT visitor_id, date FROM view " +
                    " WHERE host_id = %d AND date > '%s' " +
                    " ORDER BY id DESC LIMIT 10", userId, tenDaysAgo));

            while (rs.next()) {
                int visitorId = rs.getInt("visitor_id");
                DateTime date = fmt.parseDateTime(rs.getString("date"));
                views.add(new View(visitorId, date));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return views;
    }

    public View createView(int hostId, int visitorId) {
        // TODO think about the discrepancy between date in db and returning date
        DateTime now = DateTime.now();

        try (Connection connection = cp.getConnection()) {
            Statement statement = connection.createStatement();

            statement.executeUpdate(String.format(
                   "INSERT INTO view (host_id, visitor_id, date) VALUES (%d, %d, NOW())", hostId, visitorId));

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return new View(visitorId, now);
    }

    public void clear() {
        try (Connection connection = cp.getConnection()) {
            Statement statement = connection.createStatement();
            statement.executeUpdate(
                    " DELETE FROM view where id IN " +
                        " (SELECT id FROM " +
                            " (SELECT id, (SELECT count(*) FROM view v2 WHERE v1.id <= v2.id AND v1.host_id = v2.host_id) AS rank " +
                                " FROM view v1 ORDER BY id DESC) sub " +
                    " WHERE rank > 10) "
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

@@ -824,32 +824,40 @@
 ) %7B%0A            
+Prepared
 Statement statem
@@ -877,166 +877,23 @@
 ion.
-createStatement();%0A%0A            String tenDaysAgo = DateTime.now().minusDays(10).toString(fmt);%0A            ResultSet rs = statement.executeQuery(String.forma
+prepareStatemen
 t(%0A 
@@ -989,18 +989,17 @@
 st_id = 
-%25d
+?
  AND dat
@@ -1006,12 +1006,34 @@
 e %3E 
-'%25s'
+DATEADD('DAY', -10, NOW())
  %22 +
@@ -1085,29 +1085,103 @@
  10%22
-, userId, tenDaysAgo)
+);%0A            statement.setInt(1, userId);%0A%0A            ResultSet rs = statement.executeQuery(
 );%0A%0A
@@ -1769,32 +1769,40 @@
 ) %7B%0A            
+Prepared
 Statement statem
@@ -1810,37 +1810,38 @@
 nt = connection.
-creat
+prepar
 eStatement();%0A%0A 
@@ -1839,63 +1839,10 @@
 ent(
-);%0A
 %0A 
-           statement.executeUpdate(String.format(%0A
     
@@ -1914,14 +1914,12 @@
 ES (
-%25d, %25d
+?, ?
 , NO
@@ -1927,16 +1927,82 @@
 ())%22
-, hostId
+);%0A            statement.setInt(1, hostId);%0A            statement.setInt(2
 , vi
@@ -2009,19 +2009,56 @@
 sitorId)
-)
 ;%0A
+            statement.executeUpdate();
 %0A       
@@ -2284,16 +2284,24 @@
         
+Prepared
 Statemen
@@ -2325,21 +2325,22 @@
 nection.
-creat
+prepar
 eStateme
@@ -2346,47 +2346,8 @@
 ent(
-);%0A            statement.executeUpdate(
 %0A   
@@ -2706,16 +2706,56 @@
       );
+%0A%0A            statement.executeUpdate();
 %0A       

Change Statement to PreparedStatement