package org.ibex.mail;

import org.ibex.util.*;
import org.ibex.io.Fountain;
import org.ibex.io.Stream;
import java.sql.Timestamp;
import java.sql.*;
import java.net.*;
import java.io.*;
import java.util.*;


public class SqliteMailbox extends Mailbox.Default implements MailTree {

    public MailTree slash(String name, boolean create) {
        String p = file.getAbsolutePath();
        if (p.endsWith(".sqlite")) p = p.substring(0, p.length()-".sqlite".length());
        return FileBasedMailbox.getFileBasedMailbox(p+"/"+name, create);
    }
    public String[]     children() { return new String[0]; }
    public void         rmdir(String subdir) { throw new RuntimeException("invalid"); }
    public void         rename(String subdir, MailTree newParent, String newName) { throw new RuntimeException("invalid"); }
    public Mailbox      getMailbox() { return this; }


    private Connection conn;
    private static final String columns  =
        "                                        messageid_,       from_,to_,date_,subject_,headers_,body_,flags_";
    private static final String[] indexedColumns = new String[] {
        "uid_",
        "messageid_",
        "flags_",
        /*
        "from_",
        "to_",
        "subject_",
        "date_"
        */
    };
    
    /**
     *  from http://www.sqlite.org/autoinc.html
     *  "If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT
     *   then a slightly different ROWID selection algorithm is
     *   used. The ROWID chosen for the new row is one larger than the
     *   largest ROWID that has ever before existed in that same
     *   table. If the table has never before contained any data, then
     *   a ROWID of 1 is used. If the table has previously held a row
     *   with the largest possible ROWID, then new INSERTs are not
     *   allowed and any attempt to insert a new row will fail with an
     *   SQLITE_FULL error.
     */
    private static final String columns_ =
        "uid_ INTEGER PRIMARY KEY AUTOINCREMENT, messageid_,from_,to_,date_,subject_,headers_,body_,flags_";

    private final int uidValidity;
    private final File file;
    private final SqliteDB db;

    public  int uidValidity()  { return uidValidity; }

    public String toString() { return file.getName(); }
    public SqliteMailbox(String filename) throws SQLException {
        ResultSet rs = null;
        try {
            this.file = new File(filename);
            this.db = new SqliteDB(filename);
            this.db.setFastButDangerous(false);  // actually changes from FULL to ON
            /*
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
            */
            conn = db.getConnection();
            conn.prepareStatement("create table if not exists uidvalidity (uidvalidity)").executeUpdate();
            try {
                rs = conn.prepareStatement("select uidvalidity from uidvalidity").executeQuery();
                if (!rs.next()) {
                    this.uidValidity = new Random().nextInt();
                    PreparedStatement ps = conn.prepareStatement("insert into uidvalidity (uidvalidity) values (?)");
                    ps.setInt(1, uidValidity);
                    ps.executeUpdate();
                } else {
                    this.uidValidity = rs.getInt(1);
                }
                conn.prepareStatement("create table if not exists 'mail' ("+columns_+")").executeUpdate();
                for(String name : indexedColumns)
                    conn.prepareStatement("create index if not exists "+name+"index on mail("+name+");").executeUpdate();
            } finally { db.close(rs); }
        }
        catch (SQLException e) { throw new RuntimeException(e); }
        //catch (ClassNotFoundException e) { throw new RuntimeException(e); }
    }

    private HashMap<Integer,Integer> imapToUid = new HashMap<Integer,Integer>();
    private HashMap<Integer,Integer> uidToImap = new HashMap<Integer,Integer>();
    private boolean imapNumberCacheValid = false;
    public void updateImapNumberCache() throws SQLException {
        synchronized(this) {
            Log.warn(this+"", "rebuilding imapNumberCache...");
            imapToUid.clear();
            uidToImap.clear();
            PreparedStatement q = conn.prepareStatement("select uid_ from mail");
            ResultSet rs = q.executeQuery();
            try {
                int num = 1;
                while(rs.next()) {
                    imapToUid.put(num, rs.getInt(1));
                    uidToImap.put(rs.getInt(1), num);
                    num++;
                }
                imapNumberCacheValid = true;
            } finally { db.close(rs); }
        }
    }
    public int queryImapNumberCache(int uid) throws SQLException {
        synchronized(this) {
            if (!imapNumberCacheValid) updateImapNumberCache();
            Integer ret = uidToImap.get(uid);
            if (ret == null) return -1;
            return ret;
        }
    }
    public int queryUidForImapNum(int imapNumber) throws SQLException {
        synchronized(this) {
            if (!imapNumberCacheValid) updateImapNumberCache();
            Integer ret = imapToUid.get(imapNumber);
            if (ret == null) return -1;
            return ret;
        }
    }


    public int maxuid() { return uidNext(); }
    public int uidNext() {
        try {
            PreparedStatement q = conn.prepareStatement("select max(uid_) from mail");
            ResultSet rs = q.executeQuery();
            //if (!rs.next()) return -1;
            try {
                if (!rs.next()) throw new RuntimeException("select max(uid_) returned no rows!");
                return rs.getInt(1)+1;
            } finally { db.close(rs); }
        } catch (Exception e) { throw new RuntimeException(e); }
    }

    public Mailbox.Iterator iterator() {
        Log.warn(this, "performance warning: called iterator() on entire mailbox");
        Log.printStackTrace(this, Log.WARN);
        return new SqliteJdbcIterator();
    }
    private String set(int[] set, String arg) {
        String whereClause = "";
        boolean needsOr = false;
        for(int i=0; i<set.length; i+=2) {
            if (needsOr) whereClause += " or ";
            whereClause += "(";
            whereClause += arg+">=" + set[i];
            whereClause += " and ";
            while(i+2 < set.length && set[i+2] == (set[i+1]+1)) i += 2;
            whereClause += arg+"<=" + set[i+1];
            whereClause += ")";
            needsOr = true;
        }
        return whereClause;
    }
    private String getWhereClause(Query q) throws UnsupportedQueryException {
        String op;
        switch(q.type) {
            case Query.NOT:      return "not ("+getWhereClause(q.q[0])+")";
            case Query.AND:      op = "and";
            case Query.OR:       op = "or";
                {
                    boolean add = false;
                    StringBuffer sb = new StringBuffer();
                    for(int i=0; i<q.q.length; i++) {
                        if (add) sb.append(" " + op);
                        sb.append(" (");
                        sb.append(getWhereClause(q.q[i]));
                        sb.append(")");
                        add = true;
                    }
                    return sb.toString();
                }
            case Query.ALL:      return "1=1";
            case Query.UID:      return set(q.set, "uid_");
            case Query.DELETED:  return "((flags_ & "+(Mailbox.Flag.DELETED)+")!=0)";
            case Query.SEEN:     return "((flags_ & "+(Mailbox.Flag.SEEN)+")!=0)";
            case Query.FLAGGED:  return "((flags_ & "+(Mailbox.Flag.FLAGGED)+")!=0)";
            case Query.DRAFT:    return "((flags_ & "+(Mailbox.Flag.DRAFT)+")!=0)";
            case Query.ANSWERED: return "((flags_ & "+(Mailbox.Flag.ANSWERED)+")!=0)";
            case Query.RECENT:   return "((flags_ & "+(Mailbox.Flag.RECENT)+")!=0)";
                /*
                public static final int SENT       = 5;
                public static final int ARRIVAL    = 6;
                public static final int HEADER     = 7;
                public static final int SIZE       = 8;
                public static final int BODY       = 9;
                public static final int FULL       = 10;
                public static final int IMAPNUM    = 11;
                */

            case Query.IMAPNUM: {
                try {
                    // translate queries in terms of imap numbers into queries in terms of uids
                    // RELIES ON THE FACT THAT UIDS ARE MONOTONICALLY INCREASING
                    int[] set = new int[q.set==null ? 2 : q.set.length];
                    if (q.set==null) { set[0] = q.min; set[1] = q.max; }
                    else System.arraycopy(q.set, 0, set, 0, q.set.length);
                    for(int i=0; i<set.length; i++) {
                        int uid = queryUidForImapNum(set[i]);
                        if (uid==-1) {
                            Log.info(SqliteMailbox.class, "PROBLEM => resorting to superclass: " + q);
                            throw new UnsupportedQueryException();
                        }
                        set[i] = uid;
                    }
                    return getWhereClause(Query.uid(set));
                } catch (SQLException e) {
                    Log.error(this, e);
                    Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
                    throw new UnsupportedQueryException();
                }
            }

            default: {
                Log.info(SqliteMailbox.class, "resorting to superclass: " + q);
                throw new UnsupportedQueryException();
            }
        }
    }
    private static class UnsupportedQueryException extends Exception { }
    public Mailbox.Iterator iterator(Query q) {
        try {
            String whereClause = getWhereClause(q);
            Log.info(this, "whereClause = " + whereClause);
            return new SqliteJdbcIterator("where "+whereClause+";");
        } catch (UnsupportedQueryException _) {
            return super.iterator(q);
        }
    }
    public int count(Query q) {
        try {
            String whereClause = getWhereClause(q);
            Log.info(this, "whereClause = " + whereClause);
            try {
                Log.warn("SQL", "select count(*) from mail where " + whereClause);
                ResultSet rs = conn.prepareStatement("select count(*) from mail where " + whereClause).executeQuery();
                try {
                    rs.next();
                    return rs.getInt(1);
                } finally { db.close(rs); }
            } catch (Exception e) { throw new RuntimeException(e); }
        } catch (UnsupportedQueryException _) {
            return super.count(q);
        }
    }
    public synchronized void insert(Message m, int flags) {
        try {

            /*
            PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where messageid_=?");
            query.setString(1, m.messageid);
            Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where messageid_="+m.messageid);
            ResultSet rs2 = query.executeQuery();
            if (rs2.next()) {
                Message m2 = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
                                                                     Fountain.Util.create("\r\n\r\n"),
                                                                     Fountain.Util.create(rs2.getString(2))));
                StringBuffer s1 = new StringBuffer();
                m.getBody().getStream().transcribe(s1);
                StringBuffer s2 = new StringBuffer();
                m2.getBody().getStream().transcribe(s2);
                if (!s1.toString().equals(s2.toString())) {
                    Log.error(this.toString(),
                              "attempt to insert two messages with identical messageid ("+m.messageid+") but different bodies:\n"+
                              "  (body length="+s1.length()+") "+m.summary()+"\n"+
                              "  (body length="+s2.length()+") "+m2.summary()+"\n");
                } else {
                    Log.warn(this.toString(),
                             "silently dropping duplicate insert() [messageids and bodies match]: " + m.summary());
                    return;
                }
            }
            */
            PreparedStatement add =
                conn.prepareStatement("insert "+/*"or replace "+*/"into 'mail' ("+columns+") values (?,?,?,?,?,?,?,?)");
            add.setString(1, m.messageid+"");
            add.setString(2, m.from+"");
            add.setString(3, m.to+"");
            add.setString(4, m.date+"");
            add.setString(5, m.subject+"");
            add.setString(6, SqliteDB.streamToString(m.headers.getStream()));
            add.setString(7, SqliteDB.streamToString(m.getBody().getStream()));
            add.setInt   (8, flags);
            add.executeUpdate();

            // FIXME: be smarter here?
            imapNumberCacheValid = false;
        } catch (Exception e) { throw new RuntimeException(e); }
    }

    private class SqliteJdbcIterator implements Mailbox.Iterator {
        // could be more efficient in a ton of ways
        private ResultSet rs;
        private int count  = 0;
        private int flags;
        private Message m  = null;
        private int uid = -1;
        private String whereClause;
        public SqliteJdbcIterator() { this(""); }
        public SqliteJdbcIterator(String whereClause) {
            try {
                /*
                if (whereClause.equals(""))
                    Log.warn(this, "performance warning: empty whereClause");
                */
                this.whereClause = whereClause;
                Log.warn("SQL", "select messageid_,uid_,flags_ from 'mail' "+whereClause);
                PreparedStatement query = conn.prepareStatement("select messageid_,uid_,flags_ from 'mail' "+whereClause);
                rs = query.executeQuery();
            } catch (Exception e) { throw new RuntimeException(e); }
        }
        public Headers head()   {
            if (m != null) return m.headers;
            try {
                PreparedStatement query = conn.prepareStatement("select headers_,flags_ from 'mail' where uid_=?");
                query.setString(1, rs.getString(2));
                Log.warn("SQL", "select headers_,flags_ from 'mail' where uid_="+rs.getString(2));

                ResultSet rs2 = query.executeQuery();
                if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
                flags = rs2.getInt(2);
                return new Headers(Fountain.Util.create(rs2.getString(1)));
            } catch (Exception e) { throw new RuntimeException(e); }
        }
        public Message cur()    {
            try {
                if (m!=null) return m;
                PreparedStatement query = conn.prepareStatement("select headers_,body_,flags_ from 'mail' where uid_=?");
                query.setString(1, rs.getString(2));
                Log.warn("SQL", "select headers_,body_,flags_ from 'mail' where uid_="+rs.getString(2));

                ResultSet rs2 = query.executeQuery();
                if (!rs2.next()) { Log.error("XXX", "should not happen"); return null; }
                m = Message.newMessage(Fountain.Util.concat(Fountain.Util.create(rs2.getString(1)),
                                                            Fountain.Util.create("\r\n\r\n"),
                                                            Fountain.Util.create(rs2.getString(2))));
                flags = rs2.getInt(3);
                return m;
            } catch (Exception e) { throw new RuntimeException(e); }
        }
        public int     getFlags()   {
            try { return rs.getInt("flags_"); } catch (Exception e) { throw new RuntimeException(e); }
        }
        public void    setFlags(int flags) {
            try {
                int oldflags = rs.getInt("flags_");
                if (oldflags==flags) return;
                Log.info(this, "setflags (old="+oldflags+")" + "update mail set flags_="+(flags)+" where uid_="+uid()+"");
                if ((flags & Mailbox.Flag.DELETED) != 0) Log.printStackTrace("deletion", Log.WARN);
                PreparedStatement update = conn.prepareStatement("update mail set flags_=? where uid_=?");
                update.setInt(1, flags);
                update.setInt(2, uid());
                update.executeUpdate();
            } catch (Exception e) { throw new RuntimeException(e); }
        }
        public boolean next()       {
            try { m = null; uid = -1; count++;
            boolean ret = rs.next();
            return ret;
            } catch (Exception e) { throw new RuntimeException(e); } }
        public int     uid()        {
            if (uid == -1)
                try { uid = rs.getInt("uid_"); } catch (Exception e) { throw new RuntimeException(e); }
            return uid;
        }
        public int     imapNumber() {
            if ("".equals(whereClause)) return count;
            try { return queryImapNumberCache(uid()); } catch (SQLException s) { throw new RuntimeException(s); }
        }
        public int     nntpNumber() { return uid(); }
        public void    delete()     {
            try {
                Log.error("sqlite", "actually deleting message "+uid()+" "+head().get("subject"));
                Log.printStackTrace("sqlite", Log.ERROR);

                PreparedStatement update = conn.prepareStatement("delete from mail where uid_=?");
                update.setInt(1, uid());
                update.executeUpdate();

                // FIXME: be smarter here?
                imapNumberCacheValid = false;

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


}