using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Collections;
using System.Collections.Generic;
using C1.Silverlight.Excel;
using C1.Silverlight.Binding;

namespace C1ExcelEvaluator
{
    public class ExcelCalcEngine : C1CalcEngine
    {
        C1XLBook _book;

        // ** ctor
        public ExcelCalcEngine()
        {
            // parse multi-cell range references (Sheet1!$A2:B$4)
            // and named ranges (Values.First_Hey)
            IdentifierChars = "!$:._";
        }

        // ** object model

        public object Evaluate(C1XLBook book, string expression)
        {
            _book = book;
            try
            {
                return Evaluate(expression);
            }
            finally
            {
                _book = null;
            }
        }

        /// <summary>
        /// Parses references to cell ranges.
        /// </summary>
        /// <param name="identifier">String representing a cell range (e.g. "A1" or "A1:B12".</param>
        /// <returns>A <see cref="CellRange"/> object that represents the given range.</returns>
        public override object GetExternalObject(string identifier)
        {
            if (_book != null)
            {
                var cells = identifier.Split(':');
                if (cells.Length == 1 || cells.Length == 2)
                {
                    var rng = new XLCellRange(_book, identifier);
                    return new CellRangeReference(_book, rng);
                }
            }

            // this doesn't look like a range
            return null;
        }

        // ** implementation
        class CellRangeReference : IValueObject, IEnumerable
        {
            C1XLBook _book;
            XLCellRange _rng;
            static Dictionary<XLCellRange, bool> _evaluating = new Dictionary<XLCellRange, bool>();

            public CellRangeReference(C1XLBook book, XLCellRange rng)
            {
                _book = book;
                _rng = rng;
            }

            // ** IValueObject
            public object GetValue()
            {
                return GetValue(_rng);
            }

            // ** IEnumerable
            public IEnumerator GetEnumerator()
            {
                for (int s = _rng.SheetFrom; s <= _rng.SheetTo; s++)
                {
                    for (int r = _rng.RowFrom; r <= _rng.RowTo; r++)
                    {
                        for (int c = _rng.ColumnFrom; c <= _rng.ColumnTo; c++)
                        {
                            yield return GetValue(new XLCellRange(_book.Sheets[s], r, r, c, c));
                        }
                    }
                }
            }

            // ** implementation
            object GetValue(XLCellRange rng)
            {
                // detect circular references
                if (_evaluating.ContainsKey(rng))
                {
                    throw new Exception("Circular Reference");
                }

                // track this range while evaluating it
                try
                {
                    _evaluating.Add(rng, true);
                    var sheet = _book.Sheets[Math.Max(0, rng.SheetFrom)];
                    return sheet[rng.RowFrom, rng.ColumnFrom].Evaluate(_book);
                }
                finally
                {
                    _evaluating.Remove(rng);
                }
            }
        }
    }

    public static class CellExtension
    {
        static ExcelCalcEngine _calcEngine = new ExcelCalcEngine();

        public static object Evaluate(this XLCell cell, C1XLBook book)
        {
            var formula = cell.Formula;
            return string.IsNullOrEmpty(formula)
                ? cell.Value
                : _calcEngine.Evaluate(book, formula);
        }
    }
}
