from pprint import pprint

from app.models import db, Agent, Charge


def agents():
    sql_txt = """
            select a.id, a.firstname, a.secondname, sum(tc.charge_rate) as total_charge
            from agent as a left join
               ( select c.agent_id, c.charge_rate from charge c )
            tc
            on a.id = tc.agent_id
            group by a.id
            order by total_charge desc;
            """
    all_agents = db.session.execute(sql_txt).fetchall()

    return all_agents


def stacked_charges_by_agent(agent_id):
    all_charges = {}
    for (period_id, period_name) in db.session.execute("select id, name from period order by id"):
        charge_by_project_req = """select  p.name, c.charge_rate from project p left join
                                    charge c on p.id = c.project_id and agent_id = {} and period_id={}
                                        order by p.id""".format(agent_id, period_id)
        period_charge = {}
        for project_name, project_rate in db.session.execute(charge_by_project_req):
            period_charge[project_name] = project_rate
        all_charges[period_name] = period_charge

    pprint(all_charges)
    return all_charges


def charges_by_agent(agent_id):
    # all_charges = db.session.query(Charge).all()
    periods = db.session.execute("select name from period")
    sql_txt = "select p.name, sum(charge_rate)  from charge inner join period p on charge.period_id = p.id\
                where agent_id={} group by period_id order by p.name" \
        .format(agent_id)
    request = db.session.execute(sql_txt)
    all_charges = []
    charges_list = [(period, charge) for (period, charge) in request.fetchall()]
    charges_dict = dict(charges_list)
    for (p,) in periods:
        if p in charges_dict.keys():
            all_charges.append([p, charges_dict[p]])
        else:
            all_charges.append([p, 0])
    return all_charges