package nntp.dur.bounceme.net;

import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.*;
import static java.lang.System.out;
import java.sql.*;

public class GnuNNTP {

    private static final Logger logger = Logger.getLogger(GnuNNTP.class.getName());
    private static Level level = Level.INFO;
    private static Properties props = new Properties();
    private static java.sql.Connection con = null;
    private static Message[] messages = null;

    public static void main(String[] args) throws Exception {
        props.load(GnuNNTP.class.getResourceAsStream("/nntp.properties"));
        nntp();
        mysql();
        con.close();
    }

    private static void nntp() throws Exception {
        Session session = Session.getDefaultInstance(props);
        session.setDebug(true);
        Store store = session.getStore(new URLName(props.getProperty("host")));
        store.connect();
        Folder root = store.getDefaultFolder();
        Folder folder = root.getFolder(props.getProperty("group"));
        folder.open(Folder.READ_ONLY);
        logger.log(level, "Folder {0} has {1} messages", new Object[]{folder.getName(), folder.getMessageCount()});
        messages = folder.getMessages();
        for (Message m : messages) {
            String s = m.getSubject();
            Enumeration h = m.getAllHeaders();
            while (h.hasMoreElements()) {
                String o = h.nextElement().toString();
            }
        }
        folder.close(false);
        store.close();
    }

    private static void mysql() throws Exception {
        String url = "jdbc:mysql://localhost:3306/java";
        con = DriverManager.getConnection(url, props.getProperty("jdbc.user"), props.getProperty("jdbc.password"));
        logger.log(level, "URL: {0}", url);
        logger.log(level, "Connection: {0}", con);
        try {
            down();
            logger.log(level, "dropped table");
        } catch (Exception exception) {
            logger.log(level, "no table to drop");
        }
        up();
        describe();
        fill();
        query();
    }

    private static void down() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("DROP TABLE messages");
    }

    private static void up() throws Exception {
        String sql = "create table messages("
                + "id int, "
                + "sender text, "
                + "subject text,"
                + "content text)";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.executeUpdate();
    }

    private static void describe() throws Exception {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("DESCRIBE messages");
        ResultSetMetaData md = rs.getMetaData();
        int col = md.getColumnCount();
        for (int i = 1; i <= col; i++) {
            String col_name = md.getColumnName(i);
            logger.log(level, col_name);
        }
    }

    private static void fill() throws Exception {
        String query = "insert into messages(id,sender,subject) values (?,?,?);";
        PreparedStatement pstm = con.prepareStatement(query);
        for (Message m : messages) {
            pstm.setInt(1, m.getMessageNumber());
            pstm.setString(2, m.getFrom()[0].toString());
            pstm.setString(3, m.getSubject());
            pstm.addBatch();
        }
        pstm.executeBatch();
    }

    private static void query() throws Exception {
        Statement stmt = con.createStatement();
        ResultSet result = stmt.executeQuery("select * from messages");
        out.println(result.getFetchSize());
        while (result.next()) {
            int id = result.getInt("id");
            String sender = result.getString("sender");
            String subject = result.getString("subject");
            out.println(id + "\t\t" + subject);
        }
        out.println("queried db");
    }
}