import xlsxwriter
import io

from flask import render_template, make_response, current_app, redirect, url_for, request, flash, abort, send_file
from flask_login import login_required, current_user

from . import bp

from app.models import Agent, Project, Service, Capacity, Period, db, Company, AgentGrade, AgentStatus, AgentBap, \
    Charge, Category, Label, AgentResponsability, AgentSkill, EmploymentType
from app import db_mgr
from app.auth.routes import role_required


@bp.before_request
def site_login():
    try:
        if current_app.config['SITE_LOGIN'] \
                and not current_user.is_authenticated:
            return redirect(url_for('auth.login'))
    except KeyError:
        # no such config, juste ignore
        pass


@bp.before_request
def catch_all_route():
    current_app.logger.debug(f"{request.method} {request.path}")


@bp.route('/')
def index():
    return render_template('index.html', subtitle="Page d'accueil")


@bp.route('/services')
@role_required('service')
def services():
    # get services list
    all_services = Service.query.order_by(Service.name).all()
    num_services = len(all_services)
    # pass to template
    return render_template('services.html', subtitle="Liste des services ({})".format(num_services),
                           services=all_services)


@bp.route('/projects')
@role_required('project')
def projects():
    # get projects list
    all_projects = db_mgr.projects()
    num_projects = len(all_projects)
    # pass to template
    return render_template('projects.html', subtitle="Liste des projets ({})".format(num_projects),
                           projects=all_projects)


@bp.route('/projects/stats')
@role_required('project')
def projects_stats():
    num_projects = len(Project.query.all())
    this_categories = Category.query.all()
    return render_template('projects_stats.html', subtitle="Statistiques des projets ({})".format(num_projects),
                           categories=this_categories)


@bp.route('/agents')
@role_required('project')
def agents():
    # get agents list
    all_agents = db_mgr.agents()
    num_agents = len(all_agents)
    # pass to template
    return render_template('agents.html', subtitle="Liste des agents ({})".format(num_agents),
                           agents=all_agents)


@bp.route('/agents/stats')
@role_required('project')
def agents_stats():
    num_agents = len(Agent.query.all())
    all_periods = Period.query.order_by(Period.name).all()
    return render_template('agents_stats.html', subtitle="Statistiques des agents ({})".format(num_agents),
                           periods=all_periods)


@bp.route('/agents/responsabilities')
@role_required('project')
def responsabilities():
    num_resp = len(AgentResponsability.query.all())
    all_resp = AgentResponsability.query.order_by(AgentResponsability.name).all()
    return render_template('responsabilities.html', subtitle="Liste des Responsabilités ({})".format(num_resp),
                           responsabilities=all_resp)


@bp.route('/employments')
@login_required
def employments():
    # get employments list
    all_employments = EmploymentType.query.order_by(EmploymentType.name).all()
    num_employments = len(all_employments)
    # pass to template
    return render_template('employments.html', subtitle="Liste des Emplois Types ({})".format(num_employments),
                           employments=all_employments)

@bp.route('/skills')
@login_required
def skills():
    # get skills list
    all_skills = AgentSkill.query.order_by(AgentSkill.name).all()
    num_skills = len(all_skills)
    # pass to template
    return render_template('skills.html', subtitle="Liste des fonctions ({})".format(num_skills),
                           skills=all_skills)


@bp.route('/capacities')
@login_required
def capacities():
    # get capacities list
    all_capacities = Capacity.query.order_by(Capacity.name).all()
    num_capacities = len(all_capacities)
    # pass to template
    return render_template('capacities.html', subtitle="Liste des fonctions ({})".format(num_capacities),
                           capacities=all_capacities)


@bp.route('/labels')
@login_required
def labels():
    # get labels list
    all_labels = Label.query.order_by(Label.name).all()
    num_labels = len(all_labels)
    # pass to template
    return render_template('labels.html', subtitle="Liste des labels ({})".format(num_labels),
                           labels=all_labels)


@bp.route('/label/<label_id>')
@role_required('admin')
def label(label_id):
    # get label
    this_label = Label.query.get(int(label_id))
    # pass to template
    return render_template('label.html', subtitle=f"{this_label.name}",
                           label=this_label)


@bp.route('/category/<category_id>')
@role_required('admin')
def category(category_id):
    # get category
    this_category = Category.query.get(int(category_id))
    # pass to template
    return render_template('category.html', subtitle=f"{this_category.name}",
                           category=this_category)


@bp.route('/categories')
@login_required
def categories():
    # get categories list
    all_categories = Category.query.order_by(Category.name).all()
    num_categories = len(all_categories)
    # pass to template
    return render_template('categories.html', subtitle="Liste des categories ({})".format(num_categories),
                           categories=all_categories)


@bp.route('/periods')
@login_required
def periods():
    # get categories list
    all_periods = Period.query.order_by(Period.name).all()
    num_periods = len(all_periods)
    # pass to template
    return render_template('periods.html', subtitle="Liste des périodes ({})".format(num_periods),
                           periods=all_periods)


@bp.route('/project/<project_id>')
@role_required('project')
def project(project_id):
    # TODO: am i the project manager ?
    this_project = Project.query.get(int(project_id))
    charges_table = db_mgr.charges_by_project(project_id)
    return render_template('project.html',
                           project=this_project.to_struct(),
                           charges=charges_table,
                           subtitle="{}".format(this_project.name))


@bp.route('/agent/<agent_id>')
@role_required('agent')
def agent(agent_id):
    # TODO: am i the agent, the service or project manager , or the admin ?
    this_agent = Agent.query.get(int(agent_id))
    charges_table = db_mgr.charges_by_agent_tabled(agent_id)
    return render_template('agent.html',
                           agent=this_agent,
                           charges=charges_table,
                           subtitle=f"{this_agent.fullname}")


# - - - - - - - - - - - - - - - - - - - -  FORMS  - - - - - - - - - - - - - - - - - - - - #

updated_message = "mis à jour"
added_message = "ajouté"


@bp.route('/project/<project_id>/delete', methods=('POST', 'GET'))
@role_required('admin')
def project_delete(project_id=None):
    this_project = Project.query.get(int(project_id))
    flash("Suppression du Projet pas encore implémentée", 'warning')
    # flash(f" {this_project.name} effacé")
    return redirect(url_for('main.project', project_id=this_project.id))


@bp.route('/agent/<agent_id>/delete', methods=('POST', 'GET'))
@role_required('admin')
def agent_delete(agent_id=None):
    this_agent = Agent.query.get(int(agent_id))
    flash("Suppression de l'Agent pas encore implémentée", 'warning')
    # flash(f"Agent {this_agent.name} effacé")
    return redirect(url_for('main.agent', agent_id=this_agent.id))


@bp.route('/category/<category_id>/delete', methods=('POST', 'GET'))
@role_required('admin')
def category_delete(category_id=None):
    flash("Suppression de la Catégorie pas encore implémentée", 'warning')
    # this_category = Category.query.get(int(category_id))
    # flash(f"Category {this_category.name} effacé")
    return redirect(url_for('main.categories'))


@bp.route('/label/<label_id>/delete', methods=('POST', 'GET'))
@role_required('admin')
def label_delete(label_id=None):
    flash("Suppression du Label pas encore implémentée", 'warning')
    # this_label = Label.query.get(int(label_id))
    # flash(f"Label {this_label.name} effacé")
    return redirect(url_for('main.labels'))


@bp.route('/label/create', methods=('POST', 'GET'))
@bp.route('/label/<label_id>/edit', methods=('POST', 'GET'))
@role_required('admin')
def label_edit(label_id=None):
    if request.method == 'GET':
        if label_id:
            this_label = Label.query.get(int(label_id))
        else:
            this_label = Label()
        label_struct = this_label.to_struct()
        all_categories = Category.query.order_by(Category.name).all()
        return render_template('label_form.html', label=label_struct, categories=all_categories)
    elif request.method == 'POST':
        try:
            label_id = request.form['label_id']
        except KeyError:
            label_id = None
        if label_id:
            # then update existing
            this_label = Label.query.get(int(label_id))
            done_string = updated_message + "."
        else:
            # or create from scratch
            this_label = Label()
            done_string = added_message + "."
        # fill orm with form and write to db
        this_label.from_request(request)
        db.session.add(this_label)
        db.session.commit()
        # we're done
        flash(f"Label {this_label.name} (#{this_label.id}) " + done_string)
        return redirect(url_for('main.labels'))


@bp.route('/category/create', methods=('POST', 'GET'))
@bp.route('/category/<category_id>/edit', methods=('POST', 'GET'))
@role_required('admin')
def category_edit(category_id=None):
    if request.method == 'GET':
        if category_id:
            this_category = Category.query.get(int(category_id))
        else:
            this_category = Category()
        category_struct = this_category.to_struct()
        all_labels = Label.query.order_by(Label.name).all()
        return render_template('category_form.html', category=category_struct, labels=all_labels)
    elif request.method == 'POST':
        try:
            category_id = request.form['category_id']
        except KeyError:
            category_id = None
        if category_id:
            # then update existing
            this_category = Category.query.get(int(category_id))
            done_string = updated_message + "."
        else:
            # or create from scratch
            this_category = Category()
            done_string = "ajoutée."
        # fill orm with form and write to db
        this_category.from_request(request)
        db.session.add(this_category)
        db.session.commit()
        # we're done
        flash(f"Categorie {this_category.name} (#{this_category.id}) " + done_string)
        return redirect(url_for('main.category', category_id=this_category.id))


@bp.route('/project/create', methods=('POST', 'GET'))
@bp.route('/project/<project_id>/edit', methods=('POST', 'GET'))
@role_required('service')
def project_edit(project_id=None):
    if request.method == 'GET':
        if project_id:
            this_project = Project.query.get(int(project_id))
        else:
            this_project = Project()
        project_struct = this_project.to_struct()
        all_categories = db_mgr.category_labels()
        return render_template('project_form.html', project=project_struct,
                               categories=all_categories)
    elif request.method == 'POST':
        try:
            project_id = request.form['project_id']
        except KeyError:
            project_id = None
        if project_id:
            # then update existing
            this_project = Project.query.get(int(project_id))
            done_string = updated_message + "."
        else:
            # check name doesnt exist yet
            existing_project = Project.query.filter(Project.name == request.form.get('name')).one_or_none()
            if existing_project:
                flash(f"Le projet >{existing_project.name}< existe déjà.", 'warning')
                return redirect(url_for('main.project_edit'))
            # or create from scratch
            this_project = Project()
            done_string = added_message + "."
        # fill orm with form and write to db
        this_project.from_request(request)
        db.session.add(this_project)
        db.session.commit()
        # we're done
        flash(f"Project {this_project.name} (#{this_project.id}) " + done_string)
        return redirect(url_for('main.project', project_id=this_project.id))


@bp.route('/agent/create', methods=('POST', 'GET'))
@bp.route('/agent/<agent_id>/edit', methods=('POST', 'GET'))
@role_required('service')
def agent_edit(agent_id=None):
    # Make the form, filled with existing agent if updating
    #
    if request.method == 'GET':
        companies = Company.query.all()
        grades = AgentGrade.query.all()
        statuses = AgentStatus.query.all()
        baps = AgentBap.query.all()
        if agent_id:
            this_agent = Agent.query.get(int(agent_id))
        else:
            this_agent = Agent()
        # export to structure for jinja display
        agent_struct = this_agent.to_struct()
        return render_template('agent_form.html', agent=agent_struct,
                               companies=companies,
                               statuses=statuses,
                               baps=baps,
                               grades=grades)
    # Or submit for db writing
    #
    elif request.method == 'POST':
        try:
            agent_id = request.form['agent_id']
        except KeyError:
            agent_id = None
        if agent_id:
            # then update existing
            this_agent = Agent.query.get(int(agent_id))
            done_string = updated_message + "."
        else:
            # or create from scratch
            this_agent = Agent()
            done_string = added_message + "."
        # fill orm with form and write to db
        this_agent.from_request(request)
        db.session.add(this_agent)
        db.session.commit()
        # we're done
        flash(f"Agent {this_agent.fullname} (#{this_agent.id}) " + done_string)
        return redirect(url_for('main.agent', agent_id=this_agent.id))


@bp.route('/charge/add', methods=('POST', 'GET'))
@role_required('service')
def charge_add():
    if request.method == 'GET':
        try:
            this_agent = Agent.query.get(int(request.args['agent_id']))
        except KeyError:
            this_agent = None
        try:
            this_project = Project.query.get(int(request.args['project_id']))
        except KeyError:
            this_project = None
        this_agents = Agent.query.order_by(Agent.firstname).all()
        this_projects = Project.query.order_by(Project.name).all()
        this_services = Service.query.order_by(Service.name).all()
        this_periods = Period.query.order_by(Period.id).all()
        this_capacities = Capacity.query.order_by(Capacity.name).all()
        return render_template('charge_form.html', subtitle="Affecter un agent",
                               agent=this_agent,
                               project=this_project,
                               projects=this_projects,
                               services=this_services,
                               periods=this_periods,
                               capacities=this_capacities,
                               agents=this_agents)
    elif request.method == 'POST':
        this_agent = Agent.query.get(request.form.get('agent_id'))
        this_charge = Charge()
        this_charge.from_request(request)
        db.session.add(this_charge)
        db.session.commit()
        flash(f"Nouvelle charge pour l'agent {this_agent.fullname}")
        return redirect(url_for('main.agent', agent_id=this_agent.id))


# - - - - - - - - - - - - - - - - - - - -  REST API - - - - - - - - - - - - - - - - - - - -

def array_to_any(array, format='csv'):
    if format == 'csv':
        exporter = array_to_csv
    elif format == 'xls':
        exporter = array_to_xls
    else:
        abort(404)
    return exporter(array)


def array_to_xls(array):
    output = io.BytesIO()
    # Create a workbook and add a worksheet.
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet = workbook.add_worksheet()

    # Iterate over the data and write it out row by row, col by col
    row = 0
    for line in array:
        col = 0
        for cell in line:
            worksheet.write(row, col, cell)
            col += 1
        row += 1

    workbook.close()

    output.seek(0)

    try:
        file_to_return = send_file(output, as_attachment=True,
                                   mimetype='application/vnd.ms-excel',
                                   attachment_filename='charge.xlsx')
    except FileNotFoundError:
        abort(404)

    return file_to_return


def array_to_csv(array, sep=','):
    csv_table = []
    for line in array:
        line = [cell.replace(sep, "-") for cell in line]
        line_string = sep.join(line)
        csv_table.append(line_string)
    resp = make_response("\n".join(csv_table))
    resp.headers['Content-Type'] = 'text/plain;charset=utf8'
    return resp


@bp.route('/charge/project/<project_id>/<sort_key>/<any_format>')
@bp.route('/charge/project/<project_id>/<sort_key>')
@role_required('project')
def rest_charge_project(project_id, sort_key, any_format='csv'):
    return array_to_any(db_mgr.charges_by_project_stacked(project_id, sort_key), any_format)


@bp.route('/charge/agent/<agent_id>/<any_format>')
@bp.route('/charge/agent/<agent_id>')
@role_required('service')
def rest_charge_agent(agent_id, any_format='csv'):
    return array_to_any(db_mgr.charges_by_agent_stacked(agent_id), any_format)


@bp.route('/charge/labels/<category_id>/<any_format>')
@bp.route('/charge/labels/<category_id>')
@role_required('project')
def rest_labels_stats(category_id, any_format='csv'):
    return array_to_any(db_mgr.charges_by_labels_stacked(category_id), any_format)


@bp.route('/charge/projects/<any_format>')
@bp.route('/charge/projects')
@role_required('project')
def rest_projects_stats(any_format='csv'):
    return array_to_any(db_mgr.charges_for_projects_stacked(), any_format)


@bp.route('/count/agents/by_status/<period_id>/<any_format>')
@bp.route('/count/agents/by_status/<period_id>')
@role_required('project')
def rest_agents_status_count(period_id, any_format='csv'):
    return array_to_any(db_mgr.count_agents_by_status(period_id), any_format)