import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;

import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.index.IndexWriter;
import org.apache.lucene.document.Document;
import org.apache.lucene.document.Field;

public class IndexDatabase extends Thread {
	/* final variables */
	private static final String url = "jdbc:postgresql://exent.net/mb?user=mblookup&password=iem8Biex";
	private static final String query = "select albumartist.name as albumartist, albumartist.gid as albumartistid, albumartist.sortname as albumartistsortname, album.name as album, album.gid as albumid, cdtoc.discid, albumjoin.sequence as tracknum, track.name as track, track.gid as trackid, coalesce((coalesce(trackoffset[albumjoin.sequence + 1], leadoutoffset) - trackoffset[albumjoin.sequence]) / 0.075::real, track.length) as tracklength, coalesce((coalesce(trackoffset[albumjoin.sequence + 1], leadoutoffset) - trackoffset[albumjoin.sequence]) / 75::real, track.length / 1000) as tracklengthsec, artist.name as artist, artist.gid as artistid, artist.sortname as artistsortname from artist albumartist, album, track, artist, albumjoin left join album_cdtoc on (albumjoin.album = album_cdtoc.album) left join cdtoc on (album_cdtoc.cdtoc = cdtoc.id) where albumartist.id = album.artist and album.id = albumjoin.album and albumjoin.track = track.id and track.artist = artist.id";
	private static final int statements = 8;
	private static final int fetch = 5000;
	private static final int sleeptime = 666;

	/* variables for main() and the thread */
	private static IndexWriter iw;
	private static ResultSet[] rs;
	private static int querycounter = 0;
	private static int threadcounter = 0;
	private static boolean finished = false;
	private static int rowsparsed = 0;
	private static long twaittime = 0;
	private static long threadtime = 0;
	private static long qwaittime = 0;
	private static long querytime = 0;

	public static void main(String[] args) throws Exception {
		long totalstart = System.currentTimeMillis();
		System.out.println("Connecting to database...");
		Class.forName("org.postgresql.Driver");
		Connection db = DriverManager.getConnection(url);

		System.out.println("Creating index...");
		iw = new IndexWriter("index", new StandardAnalyzer(), true);

		System.out.println("Initiating transaction...");
		Statement st = db.createStatement();
		st.execute("begin");

		System.out.println("Creating cursor...");
		st.execute("declare lucenedata cursor for " + query);

		System.out.println("Preparing statements...");
		PreparedStatement[] ps = new PreparedStatement[statements];
		rs = new ResultSet[statements];
		for (int a = 0; a < statements; ++a)
			ps[a] = db.prepareStatement("fetch " + fetch + " from lucenedata");

		System.out.println("Fetching data...");
		IndexDatabase id = new IndexDatabase();
		System.out.println("Query time | Query wait | Thread time | Thread wait | Parsed rows | Buffer");
		while (!finished) {
			long start = System.currentTimeMillis();
			rs[querycounter] = ps[querycounter].executeQuery();
			querytime = System.currentTimeMillis() - start;
			querycounter = (++querycounter) % statements;
			printStatus();
			start = System.currentTimeMillis();
			while (getBuffer() == statements - 1)
				Thread.sleep(sleeptime);
			qwaittime = System.currentTimeMillis() - start;
			printStatus();
		}

		System.out.println();
		System.out.println("Closing cursor...");
		st.execute("close lucenedata");
		System.out.println("Commiting transaction...");
		st.execute("commit");
		System.out.println("Closing database connection...");
		for (int a = 0; a < statements; ++a)
			ps[a].close();
		st.close();
		db.close();
		long totaltime = System.currentTimeMillis() - totalstart;
		System.out.println("Tracks: " + rowsparsed);
		System.out.println("Execution time before optimization: " + totaltime + "ms");
		System.out.println("Tracks / sec before optimization: " + (rowsparsed / (totaltime / 1000)));
		System.out.println("Optimizing index...");
		iw.optimize();
		System.out.println("Closing index...");
		iw.close();
		System.out.println("Alerting user that the index is built...");
		System.out.println("The index is now built and ready for use ;)");
		totaltime = System.currentTimeMillis() - totalstart;
		System.out.println("Total execution time: " + totaltime + "ms");
		System.out.println("Tracks / sec: " + (rowsparsed / (totaltime / 1000)));
	}

	public static void printStatus() {
		System.out.print(String.format("\r%10d | %10d | %11d | %11d | %11d | %5.1f%%", querytime, qwaittime, threadtime, twaittime, rowsparsed, ((float)getBuffer() * 100 / (statements - 1))));
	}

	public static int getBuffer() {
		return (querycounter + statements - threadcounter) % statements;
	}

	public IndexDatabase() {
		start();
	}

	public void run() {
		try {
			while (!finished) {
				printStatus();
				long start = System.currentTimeMillis();
				while (getBuffer() == 0)
					Thread.sleep(sleeptime);
				twaittime = System.currentTimeMillis() - start;
				start = System.currentTimeMillis();
				int rpstart = rowsparsed;
				while (rs[threadcounter].next()) {
					iw.addDocument(createDocument(rs[threadcounter]));
					++rowsparsed;
				}
				rs[threadcounter].close();
				if (rpstart == rowsparsed || rowsparsed % fetch > 0)
					finished = true;
				threadcounter = (++threadcounter) % statements;
				threadtime = System.currentTimeMillis() - start;
				printStatus();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Document createDocument(ResultSet rs) throws Exception {
		Document doc = new Document();
		doc.add(new Field("albumartist", rs.getString("albumartist"), Field.Store.YES, Field.Index.TOKENIZED));
		doc.add(new Field("albumartistid", rs.getString("albumartistid"), Field.Store.YES, Field.Index.NO));
		doc.add(new Field("albumartistsortname", rs.getString("albumartistsortname"), Field.Store.YES, Field.Index.NO));
		doc.add(new Field("album", rs.getString("album"), Field.Store.YES, Field.Index.TOKENIZED));
		doc.add(new Field("albumid", rs.getString("albumid"), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("discid", rs.getString("discid"), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("tracknum", Integer.toString(rs.getInt("tracknum")), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("track", rs.getString("track"), Field.Store.YES, Field.Index.TOKENIZED));
		doc.add(new Field("trackid", rs.getString("trackid"), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("tracklength", Integer.toString(rs.getInt("tracklength")), Field.Store.YES, Field.Index.NO));
		doc.add(new Field("tracklengthsec", Integer.toString(rs.getInt("tracklengthsec")), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("artist", rs.getString("artist"), Field.Store.YES, Field.Index.TOKENIZED));
		doc.add(new Field("artistid", rs.getString("artistid"), Field.Store.YES, Field.Index.UN_TOKENIZED));
		doc.add(new Field("artistsortname", rs.getString("artistsortname"), Field.Store.YES, Field.Index.NO));
		doc.add(new Field("content", rs.getString("albumartist") + " " + rs.getString("albumartistsortname") + " " + rs.getString("album") + " " + Integer.toString(rs.getInt("tracknum")) + " " + rs.getString("track") + " " + rs.getString("artist") + " " + rs.getString("artistsortname"), Field.Store.YES, Field.Index.TOKENIZED));
		return doc;
	}
}