from app.models import db

def projects():
    """
    Build the list of all agents, with their charges summed up in one total
    :return:
    """
    sql_txt = """
            select p.id, p.name, sum(tc.charge_rate) as total_charge
            from project as p left join
               ( select c.project_id, c.charge_rate from charge c )
            tc
            on p.id = tc.project_id
            group by p.id
            order by total_charge desc;
            """
    all_projects = db.session.execute(sql_txt).fetchall()
    return all_projects

def agents():

    """
    Build the list of all agents, with their charges summed up in one total
    :return:
    """

    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 charges_by_project(project_id):
    """
    Build the flatten list of charges for one project, period by period
    :param project_id:
    :return:
    """

    req_sql = f"""
            select p.name as period_name,
                   a.firstname ||" "|| a.secondname as agent_name,
                   s.name as service_name,
                   c2.name as capacity_name,
                   c.charge_rate as charge_rate
            from charge as c
                     join period p on c.period_id = p.id
                     join project p2 on c.project_id = p2.id
                     join service s on c.service_id = s.id
                     join agent a on a.id = c.agent_id
                     join capacity c2 on c2.id = c.capacity_id
            where project_id = {project_id}
            order by c.period_id
            """

    req_res = db.session.execute(req_sql)
    results = list(req_res)
    # Remove comma
    nocomma_results = []
    for line in results:
        nocomma_line = [str(cell) for cell in line]
        nocomma_results.append(nocomma_line)

    headers = ["Periode", "Agent", "Service", "Fonction", "Charge"]

    nocomma_results.insert(0, headers)
    return nocomma_results

def charges_by_project_stacked(project_id, category="service"):
    """
    Build the list of charges for one project, period by period
    :param project_id: the project's id we want to return data for
    :param category: what dict to build for each period, 'service' or 'capacity' ?
    :return: a 2 dim table with header as first line and datas next, of the form
            period, category_0, category_1, ....., category_n,

            per_0, value_00, value_01, ....., value_0n,
            per_1, value_10, value_11, ....., value_1n,

                .
                .

            per_n, value_n0, value_n1, ....., value_nn,

    """

    if category == 'capacity':

        category_table = 'capacity'

        sql_req = """

        select sum(c.charge_rate)

        from capacity c1 left join
            charge c on c1.id = c.capacity_id and project_id = {} and period_id={}

            group by c1.id

        order by c1.id
        """

    elif category == 'service':

        category_table = 'service'

        sql_req = """

        select sum(c.charge_rate)

        from service s left join
            charge c on s.id = c.service_id and project_id = {} and period_id={}

            group by s.id

        order by s.id
        """
    else:
        raise ValueError("Waiting for 'service' or 'capacity'")

    categories_req = "select name from {} order by id".format(category_table)

    categories = [c for (c,) in db.session.execute(categories_req)]
    headers = ['period']+categories
    all_charges = [headers]
    for (period_id, period_name) in db.session.execute("select id, name from period order by id"):
        # build the request from the string previously set
        charge_by_categorie_req = sql_req.format(project_id, period_id)
        # build the charges line for the current period
        category_charges = [period_name]
        for (category_rate,) in db.session.execute(charge_by_categorie_req):

            category_rate = str(category_rate) if category_rate else '0'

            category_charges.append(category_rate)
        all_charges.append(category_charges)

    return all_charges

def charges_by_agent_tabled(agent_id):
    """
    Build the flatten list of charges for one agent, period by period
    :param agent_id:
    :return:
    """
    req_sql = f"""
            select p.name as period_name,
                   p2.name as project_name,
                   s.name as service_name,
                   c2.name as capacity_name,
                   c.charge_rate as charge_rate
            from charge as c
                     join period p on c.period_id = p.id
                     join project p2 on c.project_id = p2.id
                     join service s on c.service_id = s.id
                     join agent a on a.id = c.agent_id
                     join capacity c2 on c2.id = c.capacity_id
            where agent_id = {agent_id}
            order by c.period_id
            """
    req_res = db.session.execute(req_sql)
    results = list(req_res)
    # Remove comma
    nocomma_results = []
    for line in results:

        nocomma_line = [str(cell) for cell in line]

        nocomma_results.append(nocomma_line)

    headers = ["Periode", "Projet", "Service", "Fonction", "Charge"]

    nocomma_results.insert(0, headers)
    return nocomma_results


def charges_by_agent_stacked(agent_id):
    """
    Build the list of charges for one agent, period by period
    :param agent_id:
    :return:
    """

    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

    return all_charges

def charges_by_agent(agent_id):

    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