package generell;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

public class SQLFactory {
	
	private String sqlHost 		= Constant.SqlHost;
	private String connection 	= "jdbc:msql://"+sqlHost+":3306/licence";
	private TemporaryCache tCache;
	private int numberOfColumns = 0;
	private String conf 		= Constant.ConnectionConf;
	private int maxpremiumrequests;
	
	public int getLicenceStatus(String imei, int maxfreerequests){
        
		tCache.setStatus(1); // auf null setzen für scharfstellung
		
		try {
            Connection conn = DriverManager.getConnection(connection,Constant.SqlUser,Constant.SqlPassword);
            Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
           
            ResultSet rs = stmt.executeQuery("SELECT requestcount AS REQUESTS FROM licence WHERE imei = "+imei+" LIMIT 1;"); // requestcount==INT | imei==VARCHAR
            String requestcount = rs.getString("REQUESTS");
            
            ResultSetMetaData rsMetaData = rs.getMetaData();
            numberOfColumns = rsMetaData.getColumnCount();
                        
            int ReqfromSql = Integer.parseInt(requestcount);
            
            if (numberOfColumns <= 0 ) {
            	tCache.setStatus(1); // kein Eintrag für imei > zugriff erlauben
            	stmt.executeUpdate("INSERT INTO licence VALUES (1, "+imei+");");            	
            } else if ( ReqfromSql >= maxfreerequests ){
            	tCache.setStatus(0);
            } else if ( ReqfromSql < maxfreerequests ){
            	tCache.setStatus(1);
            } else {
            	tCache.setStatus(1); // auf null setzen für scharfstellung
            }
                        
            conn.close();
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
        
        return tCache.getStatus();
    }
	
	public int setLicenceStatus(String imei){
		
		int readFlag 	= 0;
		int sqlFlag 	= 0;
		int resultFlag 	= 0;
		
		try {
		 	String zeile = null;
			BufferedReader bufferedReader = new BufferedReader(new FileReader(conf));
			while ((zeile = bufferedReader.readLine()) != null) {
    				    			
    			if(zeile.startsWith("maxpremiumrequests")){
    				try{
    					maxpremiumrequests=Integer.parseInt((zeile.substring(zeile.indexOf("=")+1,zeile.length())).trim());
    					readFlag = 1;
    				}catch(NumberFormatException nfe){
    					System.out.println("maxpremiumrequests set to standart value: "+ maxpremiumrequests);
    				}
    				//System.out.println(sourceLimit);
    			}
    					    			  		    			
			} // while ((zeile = in.readLine()) != null) {
			bufferedReader.close();
	    } catch (IOException e) {
		    System.out.println("File not found take standart values!!");
	    }
		
		 try {
            Connection conn = DriverManager.getConnection(connection,Constant.SqlUser,Constant.SqlPassword);
    
            String query = "UPDATE licence SET requestcount = "+-maxpremiumrequests+" WHERE imei = "+imei;
            PreparedStatement preparedStmt = conn.prepareStatement(query);
            preparedStmt.executeUpdate();
            	                        
            conn.close();
            
            sqlFlag = 1;
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
		 
		 resultFlag = readFlag+sqlFlag;
		if ( resultFlag == 2 ){
			return 1;
		} else {
			return 0;
		}
	}
}