var _chr = function(c) { return String.fromCharCode(c); };

function encode_col(col) { var s=""; for(++col; col; col=Math.floor((col-1)/26)) s = _chr(((col-1)%26) + 65) + s; return s; }
function encode_row(row) { return "" + (row + 1); }
function encode_cell(cell) { return encode_col(cell.c) + encode_row(cell.r); }

function decode_col(c) { var d = 0, i = 0; for(; i !== c.length; ++i) d = 26*d + c.charCodeAt(i) - 64; return d - 1; }
function decode_row(rowstr) { return Number(rowstr) - 1; }
function split_cell(cstr) { return cstr.replace(/(\$?[A-Z]*)(\$?[0-9]*)/,"$1,$2").split(","); }
function decode_cell(cstr) { var splt = split_cell(cstr); return { c:decode_col(splt[0]), r:decode_row(splt[1]) }; }
function decode_range(range) { var x =range.split(":").map(decode_cell); return {s:x[0],e:x[x.length-1]}; }
function encode_range(range) { return encode_cell(range.s) + ":" + encode_cell(range.e); }
/**
 * Convert a sheet into an array of objects where the column headers are keys.
 **/
function sheet_to_row_object_array(sheet){
    var val, rowObject, range, columnHeaders, emptyRow, endOfFile, C;
    var outSheet = [];
    if (sheet["!ref"]) {
        range = decode_range(sheet["!ref"]);

        columnHeaders = {};
        for (C = range.s.c; C <= range.e.c; ++C) {
            val = sheet[encode_cell({
                c: C,
                r: range.s.r+2
            })];
            if(val){
                switch(val.t) {
                    case 's': case 'str': columnHeaders[C] = val.v; break;
                    case 'n': columnHeaders[C] = val.v; break;
                }
            }
        }

        endOfFile = false;
        for (var R = range.s.r + 1; R <= range.e.r; ++R) {
            emptyRow = true;
            //Row number is recorded in the prototype
            //so that it doesn't appear when stringified.
            rowObject = Object.create({ __rowNum__ : R });
            for (C = range.s.c; C <= range.e.c; ++C) {
                val = sheet[encode_cell({
                    c: C,
                    r: R
                })];
                if(val !== undefined){
                    switch(val.t){
                        case 's': 
                        case 'str': 
                        case 'b': 
                        case 'n':
                            if(val.v !== undefined) {
                                if (val.v.toString().indexOf('EOF') == -1) {
                                    rowObject[columnHeaders[C]] = val.v;
                                    emptyRow = false;
                                }else {
                                    endOfFile = true;
                                }
                            }
                            break;
                        case 'e': break; /* throw */
                        default: throw 'unrecognized type ' + val.t;
                    }
                }else {
                    //rowObject[columnHeaders[C]]='';
                } 
            }
            if (endOfFile) break;
            if(!emptyRow) {
                outSheet.push(rowObject);
            }
        }
    }
    return outSheet;
}

function sheet_to_csv(sheet) {
    var stringify = function stringify(val) {
        switch(val.t){
            case 'n': return String(val.v);
            case 's': case 'str':
            if(typeof val.v === 'undefined') return "";
            return JSON.stringify(val.v);
            case 'b': return val.v ? "TRUE" : "FALSE";
            case 'e': return ""; /* throw out value in case of error */
            default: throw 'unrecognized type ' + val.t;
        }
    };
    var out = "";
    if(sheet["!ref"]) {
        var r = utils.decode_range(sheet["!ref"]);
        for(var R = r.s.r; R <= r.e.r; ++R) {
            var row = [];
            for(var C = r.s.c; C <= r.e.c; ++C) {
                var val = sheet[utils.encode_cell({c:C,r:R})];
                row.push(val ? stringify(val).replace(/\\r\\n/g,"\t").replace(/\\t/g,"\t").replace(/\\\\/g,"\\").replace("\\\"","\"\"").replace(/\"/gi, '').replace(/^\s+|\s+$/g,'') : "");
            }
            if (row.join(",").indexOf('EOF') == -1)
                out += row.join(",") + "\n";
        }
    }
    return out;
}

function get_formulae(ws) {
    var cmds = [];
    for(var y in ws) if(y[0] !=='!' && ws.hasOwnProperty(y)) {
        var x = ws[y];
        var val = "";
        if(x.f) val = x.f;
        else if(typeof x.v === 'number') val = x.v;
        else val = x.v;
        cmds.push(y + "=" + val);
    }
    return cmds;
}

var utils = {
    encode_col: encode_col,
    encode_row: encode_row,
    encode_cell: encode_cell,
    encode_range: encode_range,
    decode_col: decode_col,
    decode_row: decode_row,
    split_cell: split_cell,
    decode_cell: decode_cell,
    decode_range: decode_range,
    sheet_to_csv: sheet_to_csv,
    get_formulae: get_formulae,
    sheet_to_row_object_array: sheet_to_row_object_array
};

module.exports.utils = utils;