# report_generator.py

from datetime import datetime

from join_generator import generate_multi_join_sql


# ==========================================================
# REPORT DIMENSIONS
# ==========================================================

REPORT_DIMENSIONS = {
    "district": ("agri_district", "district_name"),
    "state": ("agri_state", "state_name"),
    "subdivision": ("agri_subdivision", "subdiv_name"),
    "crop": ("crop_name", "name"),
    "crop_type": ("crop_type", "name"),
    "crop_variety": ("crop_variety", "name"),
    "farmer": ("agri_users", "userName"),
    "gender": ("agri_users", "gender"),
    "registration_date": ("agri_users", "CreateDateTime"),
}


# ==========================================================
# REPORT METRICS
# ==========================================================

REPORT_METRICS = {
    "total_land": {
        "table": "agri_land_user_rels",
        "column": "land_size",
        "aggregation": "SUM"
    },

    "total_google_area": {
        "table": "agri_land_user_rels",
        "column": "google_area",
        "aggregation": "SUM"
    },

    "avg_land": {
        "table": "agri_land_user_rels",
        "column": "land_size",
        "aggregation": "AVG"
    },

    "farmer_count": {
        "table": "agri_users",
        "column": "id",
        "aggregation": "COUNT"
    }
}


# ==========================================================
# REPORT GENERATOR
# ==========================================================

def generate_report_sql(
        metric: str,
        dimension: str,
        filters: dict = None,
        limit: int = None,
        order_desc: bool = True
):
    """
    Example:

    generate_report_sql(
        metric="total_land",
        dimension="district"
    )
    """

    if metric not in REPORT_METRICS:
        raise ValueError(
            f"Unknown metric '{metric}'. "
            f"Available: {list(REPORT_METRICS.keys())}"
        )

    if dimension not in REPORT_DIMENSIONS:
        raise ValueError(
            f"Unknown dimension '{dimension}'. "
            f"Available: {list(REPORT_DIMENSIONS.keys())}"
        )

    metric_def = REPORT_METRICS[metric]
    dimension_def = REPORT_DIMENSIONS[dimension]

    metric_table = metric_def["table"]
    metric_column = metric_def["column"]
    aggregation = metric_def["aggregation"]

    dimension_table = dimension_def[0]
    dimension_column = dimension_def[1]

    source = metric_table

    targets = []

    if dimension_table != source:
        targets.append(dimension_table)

    column_filters = {
        dimension_table: [dimension_column],
        metric_table: [metric_column]
    }

    aggregations = {
        metric_table: {
            metric_column: aggregation
        }
    }

    group_by = [
        (dimension_table, dimension_column)
    ]

    order_by = [
        (
            metric_table,
            metric_column,
            "DESC" if order_desc else "ASC"
        )
    ]

    sql = generate_multi_join_sql(
        source=source,
        targets=targets,
        column_filters=column_filters,
        where_filters=filters or {},
        order_by=order_by,
        limit=limit,
        group_by=group_by,
        aggregations=aggregations
    )

    return {
        "report_type": "dynamic_report",
        "metric": metric,
        "dimension": dimension,
        "generated_at": datetime.utcnow().isoformat(),
        "tables_used": list(
            set([metric_table, dimension_table])
        ),
        "sql": sql
    }


# ==========================================================
# PREDEFINED REPORTS
# ==========================================================

def total_land_by_district():

    return generate_report_sql(
        metric="total_land",
        dimension="district"
    )


def total_land_by_state():

    return generate_report_sql(
        metric="total_land",
        dimension="state"
    )


def farmer_count_by_district():

    return generate_report_sql(
        metric="farmer_count",
        dimension="district"
    )


def farmer_count_by_crop():

    return generate_report_sql(
        metric="farmer_count",
        dimension="crop"
    )