package pl.mkrystek.mkbot.pl.mkrystek.mkbot.database;

import static com.google.common.collect.Lists.newArrayList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import org.sqlite.JDBC;
import pl.mkrystek.mkbot.pl.mkrystek.mkbot.BotProperties;

public class SkypeDatabase {

    private Connection databaseConnection;
    private List<String> chatParticipants;
    private Integer conversationId;
    private int lastMessageId;

    public SkypeDatabase() {
        try {
            databaseConnection = JDBC.createConnection(JDBC.PREFIX + BotProperties.getSkypeDbPath(), new Properties());
            extractProperties();
            updateLastMessageId();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void extractProperties() throws SQLException {
        PreparedStatement ps = databaseConnection.prepareStatement("SELECT participants, conv_dbid FROM Chats WHERE topic = ?");
        ps.setString(1, BotProperties.getChatName());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String participants = rs.getString("participants");
            chatParticipants = newArrayList(participants.split(" "));
            conversationId = rs.getInt("conv_dbid");
        } else {
            //chat name is not a group conversation
            //TODO handle direct conversations later
        }
        ps.close();
        rs.close();
    }

    private void updateLastMessageId() throws SQLException {
        PreparedStatement ps = databaseConnection.prepareStatement("SELECT inbox_message_id FROM Conversations WHERE displayname = ?");
        ps.setString(1, BotProperties.getChatName());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            lastMessageId = rs.getInt("inbox_message_id");
        }
        ps.close();
        rs.close();
    }

    public List<String> getNewMessages() {
        List<String> newMessages = newArrayList();
        try {
            PreparedStatement ps = databaseConnection
                .prepareStatement("SELECT from_dispname, body_xml FROM Messages WHERE convo_id = ? AND id > ?");
            ps.setInt(1, conversationId);
            ps.setInt(2, lastMessageId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                newMessages.add(String.format("%s - %s", rs.getString("from_dispname"), rs.getString("body_xml")));
            }
            ps.close();
            rs.close();
            updateLastMessageId();
        } catch (SQLException e) {
            //do nothing, happens
        }

        return newMessages;
    }

    public void close() {
        try {
            if (databaseConnection != null) {
                databaseConnection.close();
            }
        } catch (SQLException e) {

        }
    }

    public List<String> getChatParticipants() {
        return chatParticipants;
    }
}

@@ -281,16 +281,17 @@
 erties;%0A
+%0A
 import o
@@ -529,19 +529,20 @@
 private 
-int
+long
  lastMes
@@ -1580,38 +1580,36 @@
 ageId() 
-throws SQLException %7B%0A
+%7B%0A        try %7B%0A    
         
@@ -1679,61 +1679,57 @@
 ECT 
-inbox_message
+max(id) as 'last
 _id
+'
  FROM 
-Conversations WHERE displayname
+messages where convo_id
  = ?
@@ -1744,54 +1744,47 @@
     
+  
+  
 ps.set
-Stri
+Lo
 ng(1, 
-BotProperties.getChatName());%0A
+conversationId);%0A    
     
@@ -1821,32 +1821,36 @@
 uery();%0A        
+  
+  
 if (rs.next()) %7B
@@ -1842,32 +1842,36 @@
 f (rs.next()) %7B%0A
+    
             last
@@ -1892,26 +1892,18 @@
 .get
-Int(%22inbox_message
+Long(%22last
 _id%22
@@ -1909,34 +1909,40 @@
 %22);%0A        
-%7D%0A
+    %7D%0A  
         
 ps.close();%0A
@@ -1921,33 +1921,35 @@
     %7D%0A          
-p
+  r
 s.close();%0A     
@@ -1943,33 +1943,37 @@
 lose();%0A        
-r
+    p
 s.close();%0A    %7D
@@ -1959,32 +1959,110 @@
     ps.close();%0A
+        %7D catch (SQLException e) %7B%0A            e.printStackTrace();%0A        %7D%0A
     %7D%0A%0A    publi
@@ -2321,16 +2321,32 @@
 AND id %3E
+ ? AND author !=
  ?%22);%0A  
@@ -2403,19 +2403,20 @@
   ps.set
-Int
+Long
 (2, last
@@ -2423,24 +2423,87 @@
 MessageId);%0A
+            ps.setString(3, BotProperties.getSkypeUsername());%0A
             
@@ -2750,43 +2750,8 @@
 ();%0A
-            updateLastMessageId();%0A
     
@@ -2815,16 +2815,104 @@
 happens%0A
+            e.printStackTrace();%0A        %7D finally %7B%0A            updateLastMessageId();%0A
         

Change the way last message id is retrieved and filter out messages written by bot