from flask import request, render_template
from sqlalchemy import func, cast, Float
from flask_login import login_required
from app.models import Task
from app import db


def dashboard_controller():

    selected_block = request.args.getlist('block[]', None)

    rate_col = cast(Task.rate, Float)
    qty_col = cast(Task.in_this_ra_bill_qty, Float)

    query = db.session.query(
        Task.block_name.label("block_name"),
        Task.village_name.label("village_name"),
        func.sum(cast(Task.boq_amount, Float)).label("total_boq_amount"),
        func.sum(cast(Task.previous_billing_amount, Float)).label("prev_billed_amount"),
        func.sum(cast(Task.variation_amount, Float)).label("total_variation_amount"),
        func.sum(cast(Task.cumulative_billed_qty, Float)).label("cumulative_billed_qty"),
        func.sum(
            cast(Task.cumulative_billed_qty, Float) *
            cast(Task.rate, Float)
        ).label("cumulative_billed_amount"),
        func.sum(qty_col).label("in_this_ra_bill_qty"),
        func.sum(rate_col * qty_col).label("to_be_claimed_amount")
    )

    if selected_block and "All" not in selected_block:
        query = query.filter(Task.block_name.in_(selected_block))

    query = query.group_by(Task.block_name, Task.village_name)
    villages = query.all()

    village_data = []
    for village in villages:
        village_data.append({
            "block_name": village.block_name,
            "village_name": village.village_name,
            "total_boq_amount": village.total_boq_amount or 0,
            "rate": "-",
            "prev_billed_amount": village.prev_billed_amount or 0,
            "total_variation_amount": village.total_variation_amount or 0,
            "cumulative_billed_qty": village.cumulative_billed_qty or 0,
            "cumulative_billed_amount": village.cumulative_billed_amount or 0,
            "in_this_ra_bill_qty": village.in_this_ra_bill_qty or 0,
            "to_be_claimed_amount": round(village.to_be_claimed_amount or 0, 2)
        })

    blocks = db.session.query(Task.block_name).distinct().all()
    block_list = ["All"] + [block[0] for block in blocks]

    return render_template(
        'index.html',
        villages=village_data,
        blocks=block_list,
        selected_block=selected_block
    )