import os
import sys
from datetime import datetime
from sqlalchemy.sql.expression import column
import xlsxwriter
import click
from sqlalchemy import select, and_, func
from planning_system.db import DB
from planning_system.db.schema.views import get_view_class
from planning_system.db.schema.tables.finance_structure import \
    ShortCodeMap, cc_group, cost_centre, directorate, cc_group_config, summary_code


@click.command()
@click.argument("acad_year", type=int)
@click.argument("set_cat_id", type=str)
@click.option("--governors", is_flag=True, help="Include governor's budget adjustments.")
@click.option("--open", "-o", "open_file", is_flag=True, help="Open file created.")
@click.option("--ccgroup", "-c", type=str, help="Filter for a specific cost centre group ID.")
@click.pass_obj
def export(config, acad_year, set_cat_id, governors, open_file, ccgroup):
    """
    Exports the most recent instances of all cost centres
    in the given ACAD_YEAR and SET_CAT_ID as
    an xlsx file in the parent directory.

    This command is the intended means to extract data which can be passed to
    the Finance service. The file created includes
    a complete list of short code mappings,
    a list of the cost centres used,
    a list of the summary code mappings,
    and the super-level balances for checking.

    The data is provided by the view :ref:`v_mri_finance_export`.
    """
    with DB(config.uri, config.echo) as db:
        s = db.session()
        view = get_view_class("v_mri_finance_export", db.engine)

        # Define where conditions at start as they're reused
        conditions = [view.acad_year == acad_year,
                      view.set_cat_id == set_cat_id]
        if ccgroup is not None:
            # Additional dynamic where
            group_cc = select(cc_group_config.costc) \
                .where(cc_group_config.cc_group_id == ccgroup)
            group_cc = [r[0] for r in s.execute(group_cc).all()]
            conditions.append(view.costc.in_(group_cc))

        # Get core data
        view_query = select(view.acad_year, view.set_cat_id, view.costc, view.account,
                            view.period, view.short_code, view.amount,
                            view.costc_description, view.summary_description, view.super_description)\
            .where(and_(*conditions)) \
            .order_by(view.costc, view.account, view.period)
        if not governors:
            view_query = view_query.where(view.governors == 0)
        rows = s.execute(view_query).all()
        if governors:
            g_flag = "_GOV"
        else:
            g_flag = ""
        rows = [d._asdict() for d in rows]

        # Get shortcode mapping used
        mapping_query = select(ShortCodeMap.costc, ShortCodeMap.account, ShortCodeMap.short_code) \
            .order_by(ShortCodeMap.costc, ShortCodeMap.account)
        mapping = _query_to_dict(mapping_query, s)

        # Costcentres used
        view_costc = select(view.costc) \
            .where(and_(*conditions)) \
            .group_by(view.costc).subquery()
        costc_query = select(cost_centre.costc, cost_centre.description,
                             directorate.description, directorate.director_name) \
            .join(directorate, isouter=False) \
            .join(view_costc, view_costc.c.costc == cost_centre.costc, isouter=False) \
            .order_by(directorate.directorate_id, cost_centre.costc)
        costcs = _query_to_dict(costc_query, s)
        # Summary code mapping
        sc_map = select(view.account, view.summary_description) \
            .where(and_(*conditions)) \
            .group_by(view.account, view.summary_description) \
            .order_by(view.account)
        sc_map = _query_to_dict(sc_map, s)
        # Balances
        bal = select(view.super_description, func.sum(view.amount).label("total")) \
            .where(and_(*conditions)) \
            .group_by(view.super_description)
        bal = _query_to_dict(bal, s)
        # Create output
        if ccgroup is not None:
            group_name = s.execute(select(cc_group)
                                   .where(cc_group.cc_group_id == ccgroup)) \
                          .one()[0].description
            group_name = "_" + group_name
        else:
            group_name = ""
        filename = f"Finance_Export_{acad_year}_{set_cat_id}{g_flag}" + \
            group_name + \
            f"_{datetime.today().strftime('%Y%m%d%H%m%S')}.xlsx"
        filepath = os.path.expanduser('~\\documents\\') + filename
        wb = xlsxwriter.Workbook(filepath)
        # Write main data sheet
        wsdata = wb.add_worksheet("Data")
        _write_to_sheet(wsdata, rows)
        # Write short code map
        wsmap = wb.add_worksheet("Mapping")
        col_offset = _write_to_sheet(
            wsmap, mapping, note="Complete short code mapping:")
        # Write costc
        col_offset = _write_to_sheet(
            wsmap, costcs, column_offset=col_offset, note="Cost centres used:")
        # Write summary code
        col_offset = _write_to_sheet(
            wsmap, sc_map, column_offset=col_offset, note="Summary description mapping used:")
        # Write super balance
        col_offset = _write_to_sheet(
            wsmap, bal, column_offset=col_offset, note="Top-level balance check:")

        wb.close()
        if open_file:
            os.startfile(filepath)


def _query_to_dict(query, session):
    return [row._asdict() for row in session.execute(query).all()]


def _write_to_sheet(ws, data, row_offset=0, column_offset=0, note=None):
    col_index = 0
    if note != None:
        ws.write(row_offset, column_offset, note)
        row_offset += 1
    for header in data[0].keys():
        ws.write(row_offset, col_index + column_offset, header)
        col_index += 1
    for row_index, row in enumerate(data):
        for col_index, v in enumerate(row.values()):
            ws.write(row_index + 1 + row_offset, col_index+column_offset, v)
    return column_offset + len(data[0]) + 2
