"""
Profiling tools for use with PyXLL.

This code accompanies the blog post
https://www.pyxll.com/blog/how-to-profile-python-code-in-excel
"""
from pyxll import xl_menu, xl_app, xlcAlert
from win32com.client import constants
from functools import wraps
from ctypes import c_int64, byref, windll
import win32clipboard
import line_profiler
import cProfile
import pstats
import math

try:
    from io import StringIO
except ImportError:
    from StringIO import StringIO


_active_cprofiler = None
_active_line_profiler = None


def _median(values):
    """Return the median of a list of values"""
    n = len(values)
    if n < 1:
        return 0.0
    if n % 2 == 1:
        return sorted(values)[n//2]
    return sum(sorted(values)[n//2-1:n//2+1])/2.0


@xl_menu("Time to Calculate", menu="Profiling Tools")
def time_calculation():
    """Recalculates the selected range and times how long it takes"""
    xl = xl_app()

    orig_calc_mode = xl.Calculation
    try:
        # switch Excel to manual calculation and disable screen updating
        xl.Calculation = constants.xlManual
        xl.ScreenUpdating = False

        # int64 variables used for timing
        start_time = c_int64()
        end_time = c_int64()

        # Get the current selection and its Calculate method (to avoid including the
        # method retrieval in the timing)
        selection = xl.Selection
        selection_Calculate = selection.Calculate

        # run the calculation a few times
        timings = []
        for i in range(100):
            # Time calling selection.Calculate() using the Windows high-resolution timers
            windll.Kernel32.QueryPerformanceCounter(byref(start_time))
            selection_Calculate()
            windll.Kernel32.QueryPerformanceCounter(byref(end_time))
            duration = float(end_time.value - start_time.value)
            timings.append(duration)
    finally:
        # restore the original calculation mode and screen updating
        xl.ScreenUpdating = True
        xl.Calculation = orig_calc_mode

    # calculate the mean and stddev
    mean = math.fsum(timings) / len(timings)
    median = _median(timings)
    stddev = (math.fsum([(x - mean) ** 2 for x in timings]) / len(timings)) ** 0.5
    best = min(timings)
    worst = max(timings)

    # convert to seconds
    freq = c_int64()
    windll.Kernel32.QueryPerformanceFrequency(byref(freq))
    mean /= freq.value
    median /= freq.value
    stddev /= freq.value
    best /= freq.value
    worst /= freq.value

    # copy the results to the clipboard
    data = [
        ["mean", mean],
        ["median", median],
        ["stddev", stddev],
        ["best", best],
        ["worst", worst]
    ]
    text = "\n".join(["\t".join(map(str, x)) for x in data])
    win32clipboard.OpenClipboard()
    win32clipboard.EmptyClipboard()
    win32clipboard.SetClipboardText(text)
    win32clipboard.CloseClipboard()

    # report the results
    xlcAlert(("%0.2f ms \xb1 %d \xb5s\n"
              "Median: %0.2f ms\n"
              "Best: %0.2f ms\n"
              "Worst: %0.2f ms\n"
              "(Copied to clipboard)") % (mean * 1000, stddev * 1000000, median * 1000, best * 1000, worst * 1000))


@xl_menu("Start", menu="Profiling Tools", sub_menu="cProfile")
def start_profiling():
    """Start the cProfile profiler"""
    global _active_cprofiler
    if _active_cprofiler is not None:
        _active_cprofiler.disable()
    _active_cprofiler = cProfile.Profile()

    xlcAlert("cProfiler Active\n"
             "Recalcuate the workbook and then stop the profiler\n"
             "to see the results.")

    _active_cprofiler.enable()


@xl_menu("Stop", menu="Profiling Tools", sub_menu="cProfile")
def stop_profiling():
    """Stop the cProfile profiler and print the results"""
    global _active_cprofiler
    if not _active_cprofiler:
        xlcAlert("No active profiler")
        return

    _active_cprofiler.disable()

    # print the profiler stats
    stream = StringIO()
    stats = pstats.Stats(_active_cprofiler, stream=stream).sort_stats("cumulative")
    stats.print_stats()

    # print the results to the log
    print(stream.getvalue())

    # and copy to the clipboard
    win32clipboard.OpenClipboard()
    win32clipboard.EmptyClipboard()
    win32clipboard.SetClipboardText(stream.getvalue())
    win32clipboard.CloseClipboard()

    _active_cprofiler = None

    xlcAlert("cProfiler Stopped\n"
             "Results have been written to the log and clipboard.")


# Current active line profiler
_active_line_profiler = None


def enable_line_profiler(func):
    """Decorator to switch on line profiling for a function.

    If using line_profiler from the command line, use the built-in @profile
    decorator instead of this one.
    """
    @wraps(func)
    def wrapper(*args, **kwargs):
        nonlocal func
        if _active_line_profiler:
            func = _active_line_profiler(func)
        return func(*args, **kwargs)
    return wrapper


@xl_menu("Start", menu="Profiling Tools", sub_menu="Line Profiler")
def start_line_profiler():
    """Start the line profiler"""
    global _active_line_profiler
    _active_line_profiler = line_profiler.LineProfiler()

    xlcAlert("Line Profiler Active\n"
             "Run the function you are interested in and then stop the profiler.\n"
             "Ensure you have decoratored the function with @enable_line_profiler.")


@xl_menu("Stop", menu="Profiling Tools", sub_menu="Line Profiler")
def stop_line_profiler():
    """Stops the line profiler and prints the results"""
    global _active_line_profiler
    if not _active_line_profiler:
        return

    stream = StringIO()
    _active_line_profiler.print_stats(stream=stream)
    _active_line_profiler = None

    # print the results to the log
    print(stream.getvalue())

    # and copy to the clipboard
    win32clipboard.OpenClipboard()
    win32clipboard.EmptyClipboard()
    win32clipboard.SetClipboardText(stream.getvalue())
    win32clipboard.CloseClipboard()

    xlcAlert("Line Profiler Stopped\n"
             "Results have been written to the log and clipboard.")


