import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * convert all categories in "Bookmarks Menu" to tags, include nested categories. <br/>
 * categories in "Bookmarks Toolbar" won't be handled. <br/>
 * change categories to lower case, and replace all white spaces to '_'. <br/>
 * <br/>
 * Dependencies:
 * <ol>
 * <li>Java 7, since try-with-resources statement is used, change it to try-finally if Java 7 is not
 * available</li>
 * <li>SQLite JDBC driver, see https://bitbucket.org/xerial/sqlite-jdbc</li>
 * </ol>
 * <br/>
 * <br/>
 * <b>NOTICE:</b><br/>
 * make sure places.sqlite is backed up before running this program
 * 
 * @author zenzhong8383
 */
public class FirefoxCategories2TagsConverter {

	public static void main(String[] args) throws Exception {
		if (args.length != 1) {
			usage();
			System.exit(1);
		}
		final String placesSqlitePath = args[0];
		if (new File(placesSqlitePath).isFile() == false) {
			System.out.println("file " + placesSqlitePath + " doesn't exist or not a file");
			usage();
			System.exit(1);
		}
		if (new File(placesSqlitePath).getName().equals("places.sqlite") == false) {
			System.out.println("file name must be places.sqlite");
			usage();
			System.exit(1);
		}
		System.out.println("start convertion, path=" + placesSqlitePath);
		Class.forName("org.sqlite.JDBC");
		FirefoxCategories2TagsConverter converter = new FirefoxCategories2TagsConverter();
		//		conv.mozBookmarksMetadata(placesSqlitePath);
		converter.convert(placesSqlitePath);
		System.out.println("end convertion");
	}

	private static void usage() {
		System.out.println("Usage:");
		System.out
				.println("*nix:\njava -cp .:/path/to/sqlite-jdbc-3.7.2.jar FirefoxCategories2TagsConverter /path/to/places.sqlite");
		System.out
				.println("Windows:\njava -cp .;/path/to/sqlite-jdbc-3.7.2.jar FirefoxCategories2TagsConverter /path/to/places.sqlite");
	}

	private void convert(final String placesSqlitePath) throws SQLException {
		Connection con = DriverManager.getConnection("jdbc:sqlite:" + placesSqlitePath);
		try {
			con.setAutoCommit(false);

			// getCategoryList;
			// replace ' ' to '_' for category name;
			// change categories to tags;
			final List<Bookmark> categoryList = getCategoryList(con);
			System.out.println("categoryList.size=" + categoryList.size());
			String sql = "update moz_bookmarks set title=replace(title, ' ', '_'), parent=4 where id=?";
			for (Bookmark category : categoryList) {
				try (PreparedStatement pstmt = con.prepareStatement(sql);) {
					pstmt.setInt(1, category.id);
					pstmt.executeUpdate();
				}
			}

			// getCategoriedBookmarkList;
			// clone categoriedBookmarks and put them to "Bookmarks Menu", and also update position;
			// set categoriedBookmarks' title to NULL;
			final List<Bookmark> categoriedBookmarkList = getCategoriedBookmarkList(con, categoryList);
			System.out.println("categoriedBookmarkList.size=" + categoriedBookmarkList.size());
			int bkmkId = queryInt(con, "select max(id) from moz_bookmarks");
			int bkmkPosition = queryInt(con,
					"select max(position) from moz_bookmarks where type=1 and parent=2");
			sql = "insert into moz_bookmarks(id, type, fk, parent, position, title, keyword_id, folder_type, dateAdded, lastModified, guid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			for (Bookmark bkmk : categoriedBookmarkList) {
				try (PreparedStatement pstmt = con.prepareStatement(sql);) {
					int idx = 1;
					pstmt.setInt(idx++, ++bkmkId);
					pstmt.setInt(idx++, bkmk.type);
					pstmt.setInt(idx++, bkmk.fk);
					pstmt.setInt(idx++, 2);
					pstmt.setInt(idx++, ++bkmkPosition);
					pstmt.setString(idx++, bkmk.title);
					pstmt.setInt(idx++, bkmk.keyword_id);
					pstmt.setString(idx++, bkmk.folder_type);
					pstmt.setLong(idx++, bkmk.dateAdded);
					pstmt.setLong(idx++, bkmk.lastModified);
					pstmt.setString(idx++, randomString(16));
					pstmt.executeUpdate();
				}
			}
			sql = "update moz_bookmarks set title=NULL where id=?";
			for (Bookmark bkmk : categoriedBookmarkList) {
				try (PreparedStatement pstmt = con.prepareStatement(sql);) {
					pstmt.setInt(1, bkmk.id);
					pstmt.executeUpdate();
				}
			}

			con.commit();
		}
		catch (Throwable e) {
			con.rollback();
			throw e;
		}
		finally {
			con.close();
		}
	}

	private static final class Bookmark {
		private Integer id;
		private Integer type;
		private Integer fk;
		private Integer parent;
		//		private Integer position;
		private String title;
		private Integer keyword_id;
		private String folder_type;
		private Long dateAdded;
		private Long lastModified;
		//		private String guid;
	}

	private List<Bookmark> queryBookmarkList(Connection con, String sql) throws SQLException {
		List<Bookmark> result = new ArrayList<>();
		try (Statement stmt = con.createStatement();) {
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Bookmark bkmk = new Bookmark();
				bkmk.id = rs.getInt("id");
				bkmk.type = rs.getInt("type");
				bkmk.fk = rs.getInt("fk");
				bkmk.parent = rs.getInt("parent");
				//				bkmk.position = rs.getInt("position");
				bkmk.title = rs.getString("title");
				bkmk.keyword_id = rs.getInt("keyword_id");
				bkmk.folder_type = rs.getString("folder_type");
				bkmk.dateAdded = rs.getLong("dateAdded");
				bkmk.lastModified = rs.getLong("lastModified");
				//				bkmk.guid = rs.getString("guid");
				result.add(bkmk);
			}
		}
		return result;
	}

	private int queryInt(Connection con, String sql) throws SQLException {
		try (Statement stmt = con.createStatement();) {
			return stmt.executeQuery(sql).getInt(1);
		}
	}

	private List<Bookmark> getCategoryList(Connection con) throws SQLException {
		List<Bookmark> result = new ArrayList<>();
		// type=2 means container;
		// include Bookmarks Menu, Bookmarks Toolbar, Tags, Unsorted Bookmarks, History, Downloads, All Bookmarks
		String sql = "select * from moz_bookmarks where type=2";
		Map<Integer, List<Bookmark>> parentChildrenMap = new HashMap<>();
		{
			List<Bookmark> bkmkList = queryBookmarkList(con, sql);
			for (Bookmark bkmk : bkmkList) {
				if (bkmk.parent == null)
					continue;
				List<Bookmark> list = parentChildrenMap.get(bkmk.parent);
				if (list == null) {
					list = new ArrayList<>();
					list.add(bkmk);
					parentChildrenMap.put(bkmk.parent, list);
				}
				else
					list.add(bkmk);
			}
			bkmkList.clear();
			bkmkList = null;
		}
		{
			LinkedList<Integer> menuCategoryFIFOQueue = new LinkedList<>();
			// id=2 means Bookmarks Menu
			menuCategoryFIFOQueue.addFirst(2);
			Integer parent;
			while ((parent = menuCategoryFIFOQueue.pollLast()) != null) {
				List<Bookmark> list = parentChildrenMap.get(parent);
				if (list == null)
					continue;
				for (Bookmark bkmk : list) {
					// type=2 means container
					if (bkmk.type.intValue() == 2) {
						result.add(bkmk);
						menuCategoryFIFOQueue.addFirst(bkmk.id);
					}
				}
			}
		}
		return result;
	}

	private List<Bookmark> getCategoriedBookmarkList(Connection con, List<Bookmark> categoryList)
			throws SQLException {
		List<Bookmark> result = new ArrayList<>();
		// type=1 means places
		String sql = "select * from moz_bookmarks where type=1";
		HashSet<Integer> categorySet = new HashSet<>(categoryList.size() << 2, 0.25F);
		for (Bookmark category : categoryList) {
			categorySet.add(category.id);
		}
		List<Bookmark> bkmkList = queryBookmarkList(con, sql);
		for (Bookmark bkmk : bkmkList) {
			if (categorySet.contains(bkmk.parent))
				result.add(bkmk);
		}
		return result;
	}

	private static final char[] RND_STR_ELEMS = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
			.toCharArray();
	private static final Random RNG = new Random();

	private static String randomString(final int strLen) {
		char[] chs = new char[strLen];
		for (int i = 0; i < strLen; i++) {
			chs[i] = RND_STR_ELEMS[RNG.nextInt(RND_STR_ELEMS.length)];
		}
		return new String(chs);
	}

	/*
	 * SQLite is not type strict, can't determine accurate column type by ResultSetMetaData,
	 * dateAdded and lastModified should be long.
	name=id, type=4
	name=type, type=4
	name=fk, type=0
	name=parent, type=4
	name=position, type=4
	name=title, type=12
	name=keyword_id, type=0
	name=folder_type, type=0
	name=dateAdded, type=4
	name=lastModified, type=4
	 */
	private void mozBookmarksMetadata(final String placesSqlitePath) throws SQLException {
		String sql = "select * from moz_bookmarks limit 1";
		try (Connection con = DriverManager.getConnection("jdbc:sqlite:" + placesSqlitePath);) {
			try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {
				ResultSetMetaData md = rs.getMetaData();
				for (int column = 1, cnt = md.getColumnCount(); column < cnt; column++) {
					System.out.println(String.format("name=%s, type=%s", md.getColumnLabel(column),
							md.getColumnType(column)));
				}
			}
		}
	}

}
