package edu.orangecoastcollege.cs272.mnguyen168.ic09;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;

public class QuizCreator {
	static int nextId;

	public static void populateFromDB() {

		try {
			Class.forName("org.sqlite.JDBC");
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Connection connection = null;

		try {
			connection = DriverManager.getConnection("jdbc:sqlite:cs272.db");
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(30);
			stmt.executeUpdate("DROP TABLE IF EXISTS question");
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS question (id INTEGER PRIMARY KEY, question Text)");
			ResultSet rs = stmt.executeQuery("SELECT * FROM question");

			while (rs.next()) {
				int id = rs.getInt("id");
				if (id + 1 > nextId)
					nextId = id + 1;
				String question = rs.getString("question");
				String choice_a = rs.getString("choice_a");
				String choice_b = rs.getString("choice_b");
				String choice_c = rs.getString("choice_c");
				String choice_d = rs.getString("choice_d");
				String answer = rs.getString("answer");
				String blank = rs.getString("blank");

				System.out.println(id);
				System.out.println(question);
				System.out.println(choice_a);
				System.out.println(choice_b);
				System.out.println(choice_c);
				System.out.println(choice_d);
				System.out.println(answer);
				System.out.println(blank);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void addToDB(int id, String question, String choice_a, String choice_b, String choice_c,
			String choice_d, String answer, String blank) {

		try {
			Class.forName("org.sqlite.JDBC");
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Connection connection = null;

		try {
			connection = DriverManager.getConnection("jdbc:sqlite:cs272.db");
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(30);
			stmt.executeUpdate("INSERT INTO question VALUES(" + id + ", '" + question + ", '" +
					choice_a + ", '" + choice_b + ", '" + choice_c + ", '" + choice_d + ", '" +
					answer + ", '" + blank + "')");

			connection.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {

		populateFromDB();
		try {
			Scanner fileScanner = new Scanner(new File("Quiz.txt"));
			while (fileScanner.hasNextLine()) {
				String idString = fileScanner.nextLine();
				idString = idString.substring(0, idString.indexOf('.'));
				int id = Integer.parseInt(idString);

				String question = fileScanner.nextLine();
				String choice_a = fileScanner.nextLine();
				String choice_b = fileScanner.nextLine();
				String choice_c = fileScanner.nextLine();
				String choice_d = fileScanner.nextLine();
				String answer = fileScanner.nextLine();
				String blank = fileScanner.nextLine();

				addToDB(id, question, choice_a, choice_b, choice_c, choice_d, answer, blank);
			}
			fileScanner.close();
		}
		catch (IOException e) {
			System.out.println("File does not exist.");
		}
	}

}