package me.blackphreak.CommandHandling.Handlers;

import me.blackphreak.CommandHandling.AbstractCommandHandler;
import me.blackphreak.Database;
import me.blackphreak.Debug.Debug;
import me.blackphreak.Student.AbstractStudent;
import me.blackphreak.Student.Types.LocalStudent;
import me.blackphreak.Student.Types.OverseaStudent;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import static me.blackphreak.Lib.promptQuestion;
import static me.blackphreak.Lib.tryParseUInt;

public class SearchStudentCommand extends AbstractCommandHandler {
	private List<String> colNames = new ArrayList<>();
	private HashMap</*colName*/String, /*searchCondition*/String> selectedCols;
	
	public SearchStudentCommand(String desc) {
		super(desc);
		
		Database db = Database.getInstance();
		ResultSet rs = db.query("PRAGMA table_info(Student);");
		try {
			while ( rs.next() ) {
				String col = rs.getString("name");
				colNames.add(col);
			}
		} catch (SQLException e) {
			db.close();
		}
	}
	
	@Override
	public boolean handle() {
		boolean nextFlag;
		do
		{
			nextFlag = selectColumnMenu();
			
			if (!nextFlag)
				System.out.println("Please select at least one column to search.");
		} while (!nextFlag);
		
		
		conditionEntryMenu();
		
		queryAndPrintMatchedStudent();
		return true;
	}
	
	private boolean selectColumnMenu() {
		String inp;
		
		System.out.println("Search student by (multiple):");
		
		AtomicInteger i = new AtomicInteger();
		// print keys only (display col name)
		colNames.forEach(colName -> System.out.println(String.format(
				"  %2s. %s",
				i.incrementAndGet() + "",
				colName
		)));
		
		System.out.println("\n** use comma to separate columns **");
		inp = promptQuestion(String.format(
				"Select search column(s) (1 - %d): ",
				colNames.size()
		));
		
		// input validation & parsing
		List<String> tmp_selectedCols = new ArrayList<>(Arrays.asList(
				inp.replaceAll("\\s+", "")
						.split(",")));
		
		// remove duplicated & validate column options
		selectedCols = new HashMap<>();
		tmp_selectedCols.stream()
				.distinct()
				.filter(targetCol ->
						!targetCol.isBlank()
								&& !targetCol.isEmpty()
								&& targetCol.matches("\\d+")
				)
				.sorted()
				.forEach(targetCol ->
						selectedCols.put(colNames.get(
								tryParseUInt(targetCol)-1
						), ""));
		
		return selectedCols.size() > 0;
	}
	
	private void conditionEntryMenu() {
		selectedCols.keySet().forEach(col -> {
			System.out.println("\n** use \"_\" as a wildcard character **");
			System.out.println("** use \"%\" to represent any wildcard character(s) **");
			System.out.println("** leave it blank to not consider as a condition **");
			
			String inp = promptQuestion(String.format(
					"Search \"%s\" with: ",
					col
			));
			
			if (!inp.isEmpty() && !inp.isBlank())
				selectedCols.put(col, inp);
			else
				selectedCols.remove(col);
		});
	}
	
	private void queryAndPrintMatchedStudent() {
		// build SQL statement
		StringBuilder sql = new StringBuilder("SELECT * FROM Student WHERE ");
		AtomicInteger i = new AtomicInteger();
		selectedCols.forEach((k, v) -> {
			if (i.getAndIncrement() > 0) {
				sql.append(" OR ");
			}
			
			sql.append("`").append(k).append("`");
			sql.append(" LIKE (\"").append(v).append("\")");
		});
		sql.append(";");
		
		// do search
		HashMap</*StudentID*/String, AbstractStudent> students = new HashMap<>();
		ResultSet rs = Database.getInstance().query(sql.toString());
		try {
			do {
				// read cols
				String StudentID = rs.getString("StudentID");
				String HKID = rs.getString("HKID");
				String VISA = rs.getString("VISA");
				String Nationality = rs.getString("Nationality");
				String ChtName = rs.getString("ChtName");
				String EngName = rs.getString("EngName");
				String Address = rs.getString("Address");
				String MobileNum = rs.getString("MobileNum");
				
				AbstractStudent stu;
				if (VISA != null && !VISA.isBlank() && !VISA.isEmpty())
				{
					// oversea stu.
					stu = new OverseaStudent(VISA);
				}
				else if (HKID != null && !HKID.isEmpty() && !HKID.isBlank()) {
					// local stu.
					stu = new LocalStudent(HKID);
				}
				else {
					Debug.warn("Unknown student type.");
					continue;
				}
				
				stu.assign(StudentID, ChtName, EngName, Address, MobileNum, Nationality);
				students.put(StudentID, stu);
			} while (rs.next());
		}
		catch (SQLException e) {
			Debug.err("Failed to search. Error:");
			e.printStackTrace();
			return;
		}
		
		System.out.println(String.format(
				"\nResults:\n+ %-13s | %-13s | %-13s | %-20s | %-30s | %-10s | %s",
				"StudentID",
				"HKID/VISA",
				"Nationality",
				"ChtName",
				"EngName",
				"Mobile",
				"Address"
		));
		//                + id            + hkid/visa     + nationality   +
		System.out.print("+---------------+---------------+---------------+");
		//                   ChtName              + EngName                        + mobile     + addr
		System.out.println("----------------------+--------------------------------+------------+-->");
		
		students.forEach((k, v) -> System.out.println(
				String.format("+ %-13s | %-13s | %-13s | %-"+(20-v.getChtName().length())+"s | %-30s | %-10s | %s",
						k,
						(v instanceof LocalStudent)
								? ((LocalStudent)v).getHKID()
								: ((OverseaStudent)v).getVisa(),
						v.getNationality(),
						v.getChtName(),
						v.getEngName(),
						v.getMobileNumber(),
						v.getHomeAddress()
				)
		));
		
		//                + id            + hkid/visa     + nationality   +
		System.out.print("+---------------+---------------+---------------+");
		//                   ChtName              + EngName                        + mobile     + addr
		System.out.println("----------------------+--------------------------------+------------+-->");
		
	}
	
	private void undo() {
	
	}
}
