import java.sql.Connection;
import java.sql.DatabaseMetaData;
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.Arrays;

public class URTsDb {
	String name;
	String url;
	Integer size;
	ArrayList<Rectification> rects;
	int finalSize;
	
	class Rectification{
		String rect;
		String lsg;
		
		Rectification(String rect, String lsg){
			this.rect = rect;
			this.lsg = lsg;
		}

	}

	URTsDb (String name, Integer size){
		this.name = name;
		this.size = size;
		this.rects = new ArrayList<Rectification>(size+10);
		this.url = "jdbc:sqlite:db";
		
		//connect to database
		try (Connection conn = DriverManager.getConnection(url)){
            if (conn != null) {
                DatabaseMetaData meta = conn.getMetaData();
                System.out.println("The driver name is " + meta.getDriverName());
                System.out.println("A new database has been created.");
            }
 
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.exit(1);
        }
		
		//check if tables already exist
		if (doesTableExist(name +"Rectifications")){
			dropRectificationTable(name);
		}
		
		//make necessary tables
		this.createRectificationTable(name);
		
		finalSize = 0;
	}
	
	boolean doesTableExist (String name){
		String sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='" + name + "';";
	        
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt  = conn.createStatement();
             ResultSet rs    = stmt.executeQuery(sql)){
            
        	if (rs.next()){
        		return true;
        	}
        	return false;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("exit on doesTableExist");
            System.exit(1);
        }
        return true;
	}
	
	void dropRectificationTable (String name){
		String sql1 = "DROP TABLE " + name + "Rectifications;";
		String sql2 = "DROP TABLE " + name + "NonURTs;";
		String sql3 = "DROP TABLE " + name + "URTs;";

		try (Connection conn = DriverManager.getConnection(url);
				Statement stmt1 = conn.createStatement();
           		Statement stmt2 = conn.createStatement();
				Statement stmt3 = conn.createStatement()) {
		    // create a new table
			stmt1.execute(sql1);
            stmt2.execute(sql2);
            stmt3.execute(sql3);
		} catch (SQLException e) {
		    System.out.println(e.getMessage());
		    System.out.println("error on drop rectification table");
		    System.exit(1);
		}
	}
	
	void createRectificationTable (String name){
		String sql1 = "CREATE TABLE " + name + "Rectifications(\n"
						+ "rect TEXT NOT NULL, \n"
						+ "skewGraph TEXT NOT NULL, \n"
						+ "UNIQUE (rect, skewGraph) ON CONFLICT IGNORE"
					+ ");";
		
		String sql2 ="CREATE TABLE "  + name + "NonURTs(rect TEXT PRIMARY KEY ON CONFLICT IGNORE); \n";
		String sql3	= "CREATE TABLE "  + name + "URTs(rect TEXT PRIMARY KEY ON CONFLICT IGNORE); \n";
		String sql4 = "CREATE INDEX idx_rect ON "+ name + "Rectifications (rect); \n";
		String sql5 = "CREATE INDEX idx_skewGraph ON "+ name + "Rectifications (skewGraph); \n";
		String sql6 = "CREATE INDEX idx_rect_skewGraph ON "+ name + "Rectifications (rect, skewGraph); \n";
				
		

		try (Connection conn = DriverManager.getConnection(url);
                Statement stmt1 = conn.createStatement();
           		Statement stmt2 = conn.createStatement();
				Statement stmt3 = conn.createStatement();
				Statement stmt4 = conn.createStatement();
				Statement stmt5 = conn.createStatement();
				Statement stmt6 = conn.createStatement();) {
            // create a new table
            stmt1.execute(sql1);
            stmt2.execute(sql2);
            stmt3.execute(sql3);
            stmt4.execute(sql4);
            stmt5.execute(sql5);
            stmt6.execute(sql6);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on create rectification table");
            System.exit(1);
        }
		
	}
	
	void add(String rect, String lsg){
		rects.add(new Rectification(rect, lsg));
		if (rects.size() > size){
			upload();
//			System.out.println("DELETED");
		}
		finalSize += 1;
	}
	
	void finishAdding(){
		upload();
		System.out.println("final size: " + finalSize);
	}
	
	void upload(){
//		System.out.println("uploading");
		String sql = "INSERT INTO " + name + "Rectifications(rect,skewGraph) VALUES(?,?)";
		try (Connection conn = DriverManager.getConnection(url);
			 PreparedStatement prep = conn.prepareStatement(sql);) {
			
			// set auto-commit mode to false
            conn.setAutoCommit(false);
            
            //iterate through
            for (Rectification rect: rects){
            	prep.setString(1, rect.rect);
            	prep.setString(2, rect.lsg);
            	prep.addBatch();
            }
            
            prep.executeBatch();
            conn.commit();
            this.rects = new ArrayList<Rectification>(size+10);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on upload");
            System.exit(1);
        }
//		System.out.println("finishedUploading");
	}
	
	void findURTs(){
        String sql1 = "INSERT INTO " + name + "NonURTs (rect) \n"
        		+ "SELECT rect FROM " + name+ "Rectifications AS si1 \n"
        		+ "JOIN (SELECT skewGraph FROM " + name+"Rectifications GROUP BY skewGraph HAVING COUNT(skewGraph) > 1) AS si2 \n"
				+ "ON si1.skewGraph = si2.skewGraph;"; 
        
        String sql2 = "INSERT INTO " + name + "URTs (rect) \n"
        			+ "SELECT rect FROM " + name + "Rectifications \n"
					+ "WHERE rect NOT IN (SELECT rect FROM " + name + "NonURTs);"; 

		try (Connection conn = DriverManager.getConnection(url);
				Statement stmt1 = conn.createStatement();
				Statement stmt2 = conn.createStatement();){
			
			conn.setAutoCommit(false);
			
			System.out.println("stmt1 start");
			stmt1.execute(sql1);
			System.out.println("stmt1 end");
			
			System.out.println("stmt2 start");
			stmt2.execute(sql2);
			System.out.println("stmt2 end");
			conn.commit();
		} catch (SQLException e) {
		  System.out.println(e.getMessage());
		  System.out.println("error in findURTs");
		  System.exit(1);
		}
	}
	
	void test(){
//		System.out.println("test");
//		String sql = "SELECT rect FROM " + name + "Rectifications \n"
//				+ "WHERE rect NOT IN (SELECT rect FROM " + name + "NonURTs);"; 
//		
//		try (Connection conn = DriverManager.getConnection(url);
//				 PreparedStatement prep = conn.prepareStatement(sql);) {
//				
//				ResultSet rs = prep.executeQuery();
//				while (rs.next()){
//					System.out.println(rs.getString("rect"));
//				}
//				System.out.println();
//			} catch (SQLException e) {
//	            System.out.println(e.getMessage());
//	            System.out.println("error on allNonURTs");
//	            System.exit(1);
//	        }
//		System.out.println();
	}
	
	boolean hasMinimalFillings (Graph g){
		String sql = "SELECT EXISTS(SELECT 1 FROM " + name + "URTs WHERE rect=? LIMIT 1);";
		try (Connection conn = DriverManager.getConnection(url);
			 PreparedStatement prep = conn.prepareStatement(sql);) {
			
			IdealIterator idealIterator = new IdealIterator(g);
			while(true){
				Ideal ideal = idealIterator.next();
				if (ideal == null) break;
				prep.setString(1, ideal.minimalFilling().toString());
				ResultSet rs =  prep.executeQuery();

				
				if (!rs.next() || rs.getBoolean("EXISTS(SELECT 1 FROM twoxtwoURTs WHERE rect=? LIMIT 1)")){
					System.out.println(Arrays.toString(ideal.labels));
					System.out.println(ideal.minimalFilling());
					return false;
				}
			}
			return true;
			
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on hasMinimalFillings");
            System.exit(1);
        }
		return false;
	}
	
	void allURTs(){
		String sql = "SELECT rect FROM " + name + "URTs;";
		System.out.println("all URTs");
		try (Connection conn = DriverManager.getConnection(url);
			 PreparedStatement prep = conn.prepareStatement(sql);) {
			
			ResultSet rs = prep.executeQuery();
			while (rs.next()){
				System.out.println(rs.getString("rect"));
			}
			System.out.println();
		} catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on allURTs");
            System.exit(1);
        }
	}
	
	void allNonURTs(){
		String sql = "SELECT rect FROM " + name + "NonURTs;";
		System.out.println("all NonURTs");
		try (Connection conn = DriverManager.getConnection(url);
			 PreparedStatement prep = conn.prepareStatement(sql);) {
			
			ResultSet rs = prep.executeQuery();
			while (rs.next()){
				System.out.println(rs.getString("rect"));
			}
			System.out.println();
		} catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on allNonURTs");
            System.exit(1);
        }
	}
	
	void allRects(){
		String sql = "SELECT rect, skewGraph FROM " + name + "Rectifications;";
		System.out.println("all Rectifications");
		try (Connection conn = DriverManager.getConnection(url);
			 PreparedStatement prep = conn.prepareStatement(sql);) {
			
			ResultSet rs = prep.executeQuery();
			while (rs.next()){
				System.out.println(rs.getString("rect") + " " + rs.getString("skewGraph"));
			}
			System.out.println();
		} catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("error on allRectifications");
            System.exit(1);
        }
	}
	
//	boolean containsAllURTs (ArrayList<String> list){
//		String sql = "SELECT EXISTS(SELECT 1 FROM " + name + "URTs WHERE rect=? LIMIT 1);";
//		try (Connection conn = DriverManager.getConnection(url);
//			 PreparedStatement prep = conn.prepareStatement(sql);) {
//			
//			// set auto-commit mode to false
//            conn.setAutoCommit(false);
//            
//            //iterate through
//            for (String rect: list){
//            	prep.setString(1, rect);
//            	prep.addBatch();
//            }
//            
//            prep.executeBatch();
//            conn.commit();
//            this.rects = new ArrayList<Rectification>(size+10);
//        } catch (SQLException e) {
//            System.out.println(e.getMessage());
//            System.out.println("error on upload");
//            System.exit(1);
//        }
//		return false;
//	}
}