package org.simantics.spreadsheet;

import java.util.ArrayList;
import java.util.List;

import org.simantics.databoard.binding.mutable.Variant;
import org.simantics.scl.runtime.tuple.Tuple;
import org.simantics.spreadsheet.solver.SpreadsheetCell;

public class Spreadsheets {

    public static final int SPREADSHEET_BTREE_SIZE = 100;
    public static final String CELL_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Cell";
    public static final String LINE_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Line";
    public static final String LINES_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Lines";

    public static boolean asBoolean(Object object) {
        if(object instanceof Boolean) return (Boolean)object;
        else if(object instanceof Number) return ((Number)object).doubleValue() != 0;
        else if(object instanceof Variant) return asBoolean(((Variant)object).getValue());
        else if(object instanceof String) {
            Double d = asDoubleWhereEmptyStringIsZero((String)object);
            if(d==null) return false;
            else return d != 0;
        }
        return false;
    }

    public static String asString(Object object) {
        if(object == null) return "";
        if(object instanceof String) return (String)object;
        if(object instanceof Number) {
            double dVal = ((Number)object).doubleValue();
            if(dVal == Math.floor(dVal)){
                return ""+((Number)object).intValue();
            } else {
                return object.toString();
            }
        }
        else if(object instanceof Variant) {
            Object o = ((Variant) object).getValue();
            if(o instanceof String) return (String)o;
            else if(o instanceof Number) asString((Number)o);
            else return o.toString();
        }
        return object.toString();
    }

    public static Double asDoubleWhereEmptyStringIsZero(Object object){
        if(object instanceof Number)
            return ((Number)object).doubleValue();
        else if(object instanceof String) {
            try {
                if(((String)object).isEmpty())
                    return 0.0;
                return Double.parseDouble((String)object);
            } catch (NumberFormatException e) {
                return null;
            }
        } else if(object instanceof Variant) {
            Object o = ((Variant) object).getValue();
            return asDoubleWhereEmptyStringIsZero(o);
        } else if (SpreadsheetCell.EMPTY == object) {
            return null;
        }
        return null;
    }

    public static double asNumber(Object object) {
        if(object instanceof Number) {
            return ((Number)object).doubleValue();
        } else if(object instanceof String) {
            try {
                String str = (String)object;
                if(str.isEmpty()) return 0;
                return Double.parseDouble((String)object);
            } catch (NumberFormatException e) {
                return 0;
            }
        } else if(object instanceof Variant) {
            Object o = ((Variant) object).getValue();
            return asNumber(o);
        } else if (SpreadsheetCell.EMPTY == object) {
            return 0.0;
        }

        return 0.0;

    }

    public static Number asValidNumber(Object object) {
        if(object instanceof Number) {
            return (Number)object;
        } else if(object instanceof String) {
            try {
                return Double.parseDouble((String)object);
            } catch (NumberFormatException e) {
                return null;
            }
        } else if(object instanceof Variant) {
            Object o = ((Variant) object).getValue();
            return asNumber(o);
        } else if (SpreadsheetCell.EMPTY == object) {
            return null;
        }

        return null;

    }

    public static boolean matchCriteria(Object value, Object criteria) {
        if(value==null || criteria==null) return false;

        if(value instanceof Variant){
            Double dVal = asDoubleWhereEmptyStringIsZero(value);
            if(dVal==null) value = ((Variant)value).getValue();
            else value = dVal;
        }
        if(criteria instanceof Variant){
            Double dVal = asDoubleWhereEmptyStringIsZero(criteria);
            if(dVal==null) criteria = ((Variant)criteria).getValue();
            else criteria = dVal;
        }

        if(criteria instanceof Number && value instanceof Number) {
            Number nc = (asNumber(criteria));
            Number nv = (asNumber(value));
            return nc.equals(nv);
        }
        if(criteria instanceof String){
            boolean nums = false;
            Object valueObj = null;
            if(value instanceof Number){
                valueObj = ((Number)value).doubleValue();
                nums = true;
            }
            else valueObj = value.toString();

            String sc = criteria.toString();
            if(sc.length() >= 3){
                String oper = sc.substring(0, 2);
                String criteriaStr = sc.substring(2);
                Double criteriaNum = null;
                try {
                    criteriaNum = Double.parseDouble(criteriaStr);
                    if(oper.equals("<>")){
                        if(!nums) return true;
                    }
                    else if(!nums) return false;
                    nums = true;
                } catch (NumberFormatException e){
                    if(oper.equals("<>")){
                        if(nums) return true;
                    }
                    else if(nums) return false;
                    nums = false;
                }

                if(oper.equals(">=")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) >= 0 ;
                    else return ((Number)valueObj).doubleValue() >= criteriaNum;
                } else if(oper.equals("<=")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) <= 0 ;
                    else return ((Number)valueObj).doubleValue() <= criteriaNum;
                } else if(oper.equals("<>")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) != 0 ;
                    else return ((Number)valueObj).doubleValue() != criteriaNum;
                }
            }
            if(sc.length() >= 2){
                String oper = sc.substring(0, 1);
                String criteriaStr = sc.substring(1);
                Double criteriaNum = null;

                try {
                    criteriaNum = Double.parseDouble(criteriaStr);
                    if(!nums) return false;
                    nums = true;
                } catch (NumberFormatException e){
                    if(nums) return false;
                    nums = false;
                }
                if(oper.equals("<")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) < 0;
                    else return ((Number)valueObj).doubleValue() < criteriaNum;
                } else if(oper.equals(">")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) > 0;
                    else return ((Number)valueObj).doubleValue() > criteriaNum;
                } else if(oper.equals("=")){
                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) == 0;
                    else return ((Number)valueObj).doubleValue() == criteriaNum;
                }
            }
            return sc.equals(valueObj);
        }
        else if (criteria instanceof Number){
            return false;
        }
        throw new IllegalStateException();
    }

    public static boolean excelEquals(Object left, Object right) {
        if(left instanceof String) {
            if(right instanceof String) {
                return ((String) left).toLowerCase().equals(((String) right).toLowerCase());
            }
        }
        return left.equals(right);
    }


    //  1 kirjain, 'A' + column
    //  2 kirjainta, 'A' + column % 26 , 'A' + int((column-26)/26)
    //  3 kirjainta 'A' + column % 26 , 'A' + int(column-(26*(26+1)) / 26) % 26

    //    0              26
    //    26             26 + 26*26
    //    26 + 26*26     26 + 26*26 + 26*26*26

    public static String columnName(int column, int current, int limit, int chars) {

        if(column < limit) {

            char[] buf = new char[chars];
            column -= current;
            for(int i=chars-1;i>=0;i--) {
                char rem = (char)(column % 26);
                column = (column / 26);
                buf[i] = (char)('A' + rem); 
            }
            return new String(buf);

        } else return columnName(column, limit, 26*(limit+1), chars+1);

    }

    public static String columnName(int column) {
        return columnName(column, 0, 26, 1);
    }    

    public static String cellName(int row, int column) {

        String result = columnName(column);
        result += (row+1);
        return result;

    }    

    public static String offset(String location, int rowOffset, int columnOffset) {

        Range range = decodeCellAbsolute(location);
        String result = cellName(range.startRow + rowOffset, range.startColumn + columnOffset); 
        //      System.err.println("offset " + location + "(" + rowOffset + " " + columnOffset + ") = >" + result);
        return result;

    }

    public static Range decodeCellAbsolute(String identifier) {
        long l = decodeCellCoded(identifier);
        int row = (int)(l & 0xffffffff) - 1;
        int column = (int)((l>>32) & 0xffffffff);
        return new Range(row, row, column, column);
    }

    public static Range decodePossibleCellAbsolute(String identifier) {
        try {
            return decodeCellAbsolute(identifier);
        } catch (CellParseException e) {
            return null;
        }
    }

    public static long decodeCellCoded(String identifier) {

        //        System.out.println("decodecellabsolute " + identifier);

        int row = 0;
        int column = 0;

//      identifier.

        int position = 0;

        // We skip $ here
        if(identifier.charAt(position) == '$') position++;

        int length = identifier.length();

        while(position < length) {
            char b = identifier.charAt(position);
            if(b >= 'A' && b <= 'Z') column = column * 26 + (b-'A' + 1);
            else break;
            position++;
        }

        // We skip $ here
        if(position < length)
            if(identifier.charAt(position) == '$')
                position++;

        while(position < length) {
            char b = identifier.charAt(position);
            if(b >= '0' && b <= '9'){
                row = row * 10 + b-'0';
            }
            else if(b=='-' && position < (length-1)){//identify use of full row range here.
                position++;
                char b2 = identifier.charAt(position);
                if(b2=='1'){
                    row = 0;
                    position++;
                    break;
                }
            }
            else {
                break;
            }
            position++;
        }

        if(position == length) {

            // We need to be able to express -1 in row => report row + 1 here
            column--;
            //            System.err.println("ra " + identifier + " => " + row + " " + column);
            return row + (((long)column)<<32);

        } else {

            throw new CellParseException("Cell identifier '" + identifier + "' is not a valid cell reference.");

        }

    }

    public static Range decodeCellRelative(String identifier, int row, int column) {

        int offset = Integer.valueOf(identifier.substring(1).trim());
        //        System.out.println("offset=" + offset);

        if(identifier.startsWith("L") || identifier.startsWith("l")) {
            return new Range(row, row, column-offset, column-offset);
        } else if(identifier.startsWith("R") || identifier.startsWith("r")) {
            return new Range(row, row, column+offset, column+offset);
        } else if(identifier.startsWith("U") || identifier.startsWith("u")) {
            return new Range(row-offset, row-offset, column, column);
        } else if(identifier.startsWith("D") || identifier.startsWith("d")) {
            return new Range(row+offset, row+offset, column, column);
        } else {
            throw new CellParseException("Relative cell syntax must begin with L|R|U|D.");
        }

    }

    public static Range decodeCell(String identifier, int row, int column) {

        if(identifier.startsWith("_")) {
            return decodeCellRelative(identifier.substring(1), row, column);
        } else {
            return decodeCellAbsolute(identifier);
        }

    }

    public static Range decodeReference(String identifier, int row, int column) {
        if(!identifier.startsWith("&")) throw new CellParseException("A reference cell was expected.");
        return decodeRange(identifier.substring(1), row, column);
    }

    public static List<Range> decodeRanges(String ranges){
        String[] splitted = ranges.split(",");
        List<Range> result = new ArrayList<>();
        for(String split : splitted){
            result.add(decodeRange(split));
        }
        return result;
    }

    public static Object extract(Object object, int row, int column) {
        if(object instanceof List) {
            @SuppressWarnings("rawtypes")
            List list = (List)object;
            if(list.size() <= row) return null;
            Object item = list.get(row);
            if(item instanceof Tuple) {
                Tuple tuple = (Tuple)item;
                if(tuple.length() <= column) return null;
                return tuple.get(column);
            }
        }
        return null;
    }



    public static int startRow(List<Range> ranges){
        int s = -1;
        for(Range r : ranges){
            if(r.startRow<s || s==-1){
                s = r.startRow;
            }
        }
        return s;
    }

    public static int startColumn(List<Range> ranges){
        int s = -1;
        for(Range r : ranges){
            if(r.startColumn<s || s==-1){
                s = r.startColumn;
            }
        }
        return s;
    }

    public static int amountOfRows(Range r){
        int endRow = -2;
        int startRow = -2;
        if(r.isFullRows()){
            return Range.MAXROWSPEC;
        }
        if(endRow == -2 && startRow == -2){
            endRow = r.endRow;
            startRow = r.startRow;
        }
        if(r.startRow<startRow){
            startRow = r.startRow;
        }
        if(r.endRow>endRow){
            endRow = r.endRow;
        }
        return endRow - startRow +1;
    }

    public static int amountOfColumns(Range r){
        int endColumn = -2;
        int startColumn = -2;
        if(r.isFullColumns()){
            return Range.MAXCOLUMNSPEC;
        }
        if(endColumn == -2 && startColumn == -2){
            endColumn = r.endColumn; 
            startColumn = r.startColumn;
        }
        if(r.startColumn<startColumn){
            startColumn = r.startColumn;
        }
        if(r.endColumn>endColumn){
            endColumn = r.endColumn;
        }
        return endColumn - startColumn +1;
    }

    public static Range decodeRange(String rangeOrCell) {
        if(rangeOrCell.isEmpty()) return fullRange();
        return decodeRange(rangeOrCell, 0, 0);
    }

    public static Range fullRange() {
        return new Range(0, -1, 0, -1);
    }

    public static Range decodeRange(String rangeOrCell, int row, int column) {

        String[] parts = rangeOrCell.split(":");
        if(parts.length == 1) {

            return decodeCell(rangeOrCell, row, column);

        } else if (parts.length == 2) {

            Range from = decodeCell(parts[0].trim(), row, column);
            //            System.out.println("decodefrom=" + from);
            Range to = decodeCell(parts[1].trim(), row, column);
            //            System.out.println("decodeto=" + to);
            return Range.combine(from, to);

        } else {

            throw new CellParseException("The reference cell syntax was invalid. At most 1 occurrence of ':' is expected.");

        }

    }

}