package org.sillyjirafe.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Crawl_StateDB 
{
	String URL = new String();
	String insertSql = "INSERT OR IGNORE INTO crawlstates (bind_name, last_page, rnd) VALUES (?, ?, ?)";
	Connection connection;
		
	public Crawl_StateDB(String BDstr) throws SQLException
	{
		URL = BDstr;
		connection = DriverManager.getConnection(URL);
	}
		
	public void LinkToDB() throws SQLException
	{
		try (Statement stmt = connection.createStatement()) 
		{
			stmt.execute("""
					CREATE TABLE IF NOT EXISTS crawlstates (
	                bind_name TEXT PRIMARY KEY,
                    last_page INTEGER NOT NULL DEFAULT 1,
                    rnd TEXT NOT NULL DEFAULT 'a'
					)
	            """);
	    }
	}

	public void AddEllement(String bind, int lastpage) throws SQLException
	{
		try (PreparedStatement ps = connection.prepareStatement(insertSql)) 
		{
			ps.setString(1, bind);
			ps.setInt(2, lastpage);
	        ps.executeUpdate();
	        System.out.printf("%s is now page %d \n", bind, lastpage);
	    }
	}
	
	public int getPage(String bindName) throws SQLException {
	    String sql = "SELECT last_page FROM crawlstates WHERE bind_name = ?";
	    
	    try (PreparedStatement ps = connection.prepareStatement(sql)) {
	        ps.setString(1, bindName);
	        ResultSet rs = ps.executeQuery();
	        
	        if (rs.next()) {
	            return rs.getInt("last_page");
	        } else {
	            return 1;
	        }
	    }
	}
	
	public String getRND(String bindName) throws SQLException {
	    String sql = "SELECT rnd FROM crawlstates WHERE bind_name = ?";
	    
	    try (PreparedStatement ps = connection.prepareStatement(sql)) {
	        ps.setString(1, bindName);
	        ResultSet rs = ps.executeQuery();
	        
	        if (rs.next()) {
	            return rs.getString("rnd");
	        } else {
	            return "a";
	        }
	    }
	}
	public void updatePage(String bindName, int maxPage) throws SQLException {
	    int pageActuelle = getPage(bindName);
	    String rndActuel = getRND(bindName);
	    
	    int nouvellePage;
	    String nouveauRnd;
	    
	    if (pageActuelle >= maxPage) {
	        nouvellePage = 1;
	        nouveauRnd = NextLetter(rndActuel);
	    } else {
	        nouvellePage = pageActuelle + 1;
	        nouveauRnd = rndActuel;
	    }
	    
	    String sql = """
	        INSERT INTO crawlstates (bind_name, last_page, rnd) VALUES (?, ?, ?)
	        ON CONFLICT(bind_name) DO UPDATE SET last_page = ?, rnd = ?
	    """;
	    try (PreparedStatement ps = connection.prepareStatement(sql)) {
	        ps.setString(1, bindName);
	        ps.setInt(2, nouvellePage);
	        ps.setString(3, nouveauRnd);
	        ps.setInt(4, nouvellePage);
	        ps.setString(5, nouveauRnd);
	        ps.executeUpdate();
	    }
	}
	public static String NextLetter(String actual) {
		char[] lettres = actual.toCharArray();
	    int i = lettres.length - 1;
	    
	    while (i >= 0) {
	        if (lettres[i] < 'z') {
	            lettres[i]++;
	            return new String(lettres);
	        }
	        lettres[i] = 'a';
	        i--;
	    }
	    
	    return "a" + new String(lettres);
	}
		
	public void Close() throws SQLException
	{
		if (connection != null) 
		{
			connection.close();
		}
	}
}
