package org.simantics.spreadsheet.graph.formula;

import org.simantics.databoard.binding.mutable.Variant;
import org.simantics.spreadsheet.graph.CellFormulaFunction;
import org.simantics.spreadsheet.graph.CellValueVisitor;
import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
import org.simantics.spreadsheet.graph.parser.ast.AstArgList;

public class VlookupFormulaFunction implements CellFormulaFunction<Object> {

    @Override
    public Object evaluate(CellValueVisitor visitor, AstArgList args) {
        if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();

        Object lookupValue = args.values.get(0).accept(visitor);
        boolean lookupIsString = lookupValue instanceof String;

        FormulaError2 error = FormulaError2.forObject(lookupValue);
        if(error!=null) return error.getString();
        
        if(lookupValue instanceof Variant){
        	Object varVal = ((Variant)lookupValue).getValue();
        	Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);
        	if(dVal==null) {
        		lookupValue = varVal.toString();
        		lookupIsString = true;
        	}
        	else lookupValue = dVal;
        }

        
        SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);

        boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;

        boolean valueExists = false;
		if(array.values.length != 0){
			for(int i = 0; i < array.getHeight(); i++){
				if(lookupIsString){
					Object obj = array.get(i,0);
					if(obj instanceof Variant)
						obj = ((Variant)obj).getValue();
					if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){
						obj = obj.toString();
						String valStr = ((String)obj).toLowerCase();
						if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
							valueExists = true;
							break;
						}
					}
				}
				else if(lookupValue instanceof Number){
					Double d = ((Number)lookupValue).doubleValue();
					Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));
					if(d2!=null && d>=(d2.doubleValue())){
						valueExists = true;
						break;
					}
				}
			}
//			if(lookupIsString){
//				Object obj = array.values[0];
//				if(obj instanceof Variant)
//					obj = ((Variant)obj).getValue();
//				if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
//					return FormulaError2.NA.getString();
//				obj = obj.toString();
//				String valStr = ((String)obj).toLowerCase();
//				if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
//					return FormulaError2.NA.getString();
//			}
//			else if(lookupValue instanceof Number){
//				Double d = ((Number)lookupValue).doubleValue();
//				Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
//				if(d2==null || d<(d2.doubleValue()))
//					return FormulaError2.NA.getString();
//			}
		}
		if(!valueExists) return FormulaError2.NA.getString();
		
		Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
		if(colIndexN==null) return FormulaError2.REF.getString();
		int colIndexNum = colIndexN.intValue();
        if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();
        
        if (approximateMatch) {
        	Integer rowPosOfLargestValSmallerThanLookUp = null;
        	if(lookupIsString){
        		String ref = lookupValue.toString().toLowerCase();
        		String previousValue = null;
        		String largestValSmallerThanLookUp = null;
        		for(int i = 0; i < array.getHeight(); i++){
        			Object val = array.get(i, 0);
        			if(val instanceof Variant)
        				val = ((Variant)val).getValue();
        			String currValue = val.toString().toLowerCase();
        			if(currValue.compareTo(ref)>0){
        				if(i==0)
        					return array.get(i, colIndexNum-1);
        				else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
        					return array.get(i, colIndexNum-1);
        				else 
        					return array.get(i - 1, colIndexNum - 1);
        			}
        			if(rowPosOfLargestValSmallerThanLookUp==null){
        				rowPosOfLargestValSmallerThanLookUp = i;
        				largestValSmallerThanLookUp = currValue;
        			} else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
        				rowPosOfLargestValSmallerThanLookUp = i;
        				largestValSmallerThanLookUp = currValue;
        			}
        			previousValue = currValue;
        		}
        		
        	} else {
        		double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
            	double lastDiff = Double.MIN_VALUE;
            	Double largestValSmallerThanLookUp = null;
            	for (int i = 0; i < array.getHeight(); i++) {
                	Object value = array.get(i, 0);
                	double n = SpreadsheetGraphUtils.asNumber(value);
                	double diff = n - ref;
                	if (diff > 0) {
                    	if (i == 0)
                        	return array.get(i, colIndexNum - 1);
                    	else if (diff < -lastDiff)
                        	return array.get(i, colIndexNum - 1);
                    	else
                        	return array.get(i - 1, colIndexNum - 1);
                	}
        			if(rowPosOfLargestValSmallerThanLookUp==null){
        				rowPosOfLargestValSmallerThanLookUp = i;
        				largestValSmallerThanLookUp = n;
        			} else if(largestValSmallerThanLookUp < n){
        				rowPosOfLargestValSmallerThanLookUp = i;
        				largestValSmallerThanLookUp = n;
        			}
                	lastDiff = diff;
            	}
        	}
        	
        	if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
        
        } else {
            for (int i = 0; i < array.getHeight(); i++) {
                Object value = array.get(i, 0);
                if(value instanceof Variant){
                	if(lookupIsString)
                		value = ((Variant)value).getValue().toString();
                	else 
                		value = SpreadsheetGraphUtils.asNumber(value);
                }
                if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
                    return array.get(i, colIndexNum - 1);
                }
            }
        }

        return FormulaError2.NA.getString();
    }

}