package bms.player.beatoraja.song;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Logger;

import bms.player.beatoraja.Validatable;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import bms.model.BMSModel;

import org.sqlite.SQLiteConfig.SynchronousMode;

/**
 * 楽曲情報データベースへのアクセスクラス
 * 
 * @author exch
 */
public class SongInformationAccessor {

	private SQLiteDataSource ds;

	private final ResultSetHandler<List<SongInformation>> songhandler = new BeanListHandler<SongInformation>(
			SongInformation.class);

	private final QueryRunner qr;

	private Connection conn;

	public SongInformationAccessor(String filepath) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		SQLiteConfig conf = new SQLiteConfig();
		conf.setSharedCache(true);
		conf.setSynchronous(SynchronousMode.OFF);
		// conf.setJournalMode(JournalMode.MEMORY);
		ds = new SQLiteDataSource(conf);
		ds.setUrl("jdbc:sqlite:" + filepath);
		qr = new QueryRunner(ds);
		createTable();
	}

	/**
	 * 楽曲データベースを初期テーブルを作成する。 すでに初期テーブルを作成している場合は何もしない。
	 */
	private void createTable() {
		try {
			// songテーブル作成(存在しない場合)
			if (qr.query("SELECT * FROM sqlite_master WHERE name = ? and type='table';", new MapListHandler(),
					"information").size() == 0) {
				qr.update("CREATE TABLE [information] (" + "[sha256] TEXT NOT NULL," + "[n] INTEGER," + "[ln] INTEGER,"
						+ "[s] INTEGER," + "[ls] INTEGER," + "[total] REAL," + "[density] REAL," + "[peakdensity] REAL," + "[enddensity] REAL," + "[distribution] TEXT," + "PRIMARY KEY(sha256));");
			}
			if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%peakdensity%'", new MapListHandler()).size() == 0) {
				qr.update("ALTER TABLE information ADD COLUMN peakdensity [REAL]");
			}
			if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%enddensity%'", new MapListHandler()).size() == 0) {
				qr.update("ALTER TABLE information ADD COLUMN enddensity [REAL]");
			}
			if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%mainbpm%'", new MapListHandler()).size() == 0) {
				qr.update("ALTER TABLE information ADD COLUMN mainbpm [REAL]");
			}
			if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%speedchange%'", new MapListHandler()).size() == 0) {
				qr.update("ALTER TABLE information ADD COLUMN speedchange [TEXT]");
			}
			if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%lanenotes%'", new MapListHandler()).size() == 0) {
				qr.update("ALTER TABLE information ADD COLUMN lanenotes [TEXT]");
			}

		} catch (SQLException e) {
			Logger.getGlobal().severe("楽曲データベース初期化中の例外:" + e.getMessage());
		}
	}

	public SongInformation[] getInformations(String sql) {
		try {
			List<SongInformation> m = Validatable.removeInvalidElements(qr.query("SELECT * FROM information WHERE " + sql, songhandler));
			return m.toArray(new SongInformation[m.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new SongInformation[0];		
	}

	public SongInformation getInformation(String sha256) {
		try {
			List<SongInformation> m = Validatable.removeInvalidElements(qr.query("SELECT * FROM information WHERE sha256 = ?", songhandler, sha256));
			if(m.size() > 0) {
				return m.get(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void getInformation(SongData[] songs) {
		try {
			StringBuilder str = new StringBuilder(songs.length * 64);
			for (SongData song : songs) {
				if(song.getSha256() != null) {
					if (str.length() > 0) {
						str.append(',');
					}
					str.append('\'').append(song.getSha256()).append('\'');
				}
			}

			List<SongInformation> infos = Validatable.removeInvalidElements(qr
					.query("SELECT * FROM information WHERE sha256 IN (" + str.toString() + ")", songhandler));
			for(SongData song : songs) {
				for(SongInformation info : infos) {
					if(info.getSha256().equals(song.getSha256())) {
						song.setInformation(info);
						break;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void startUpdate() {
		try {
			conn = ds.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			conn = null;
		}
	}

	public void update(BMSModel model) {
		SongInformation info = new SongInformation(model);
		try {
			qr.update(conn,
					"INSERT OR REPLACE INTO information (sha256, n, ln, s, ls, total, density, peakdensity, enddensity, mainbpm, distribution, speedchange, lanenotes)"
							+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?);",
					model.getSHA256(), info.getN(), info.getLn(), info.getS(), info.getLs(), info.getTotal(), info.getDensity(), info.getPeakdensity(), info.getEnddensity(), info.getMainbpm(), info.getDistribution(), info.getSpeedchange(), info.getLanenotes());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void endUpdate() {
		if (conn != null) {
			try {
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				}
				conn = null;
			}
		}
	}

}