""" query_faults.py 查询储能设备告警记录，支持多条件过滤和时间粒度聚合统计。通过 HTTP API 访问数据库，无需安装第三方依赖。 Usage: python query_faults.py [--battery_id ] [--fault_code
]
                               [--fault_level ]
                               [--start_time ]
                               [--end_time   ]
                               [--granularity ]
                               [--limit <条数，默认100>]
"""

import sys
import json
import argparse
import urllib.request
import urllib.parse

DB_API_URL = "http://47.97.121.32:8080/VOLERTECH/app/api/oauth/getmysql"
DB_JDBC    = "snowy_cloud_mysql_copy"


def execute_sql(sql: str) -> list:
    """通过 HTTP API 执行 SQL，返回行列表（list of dict）"""
    data = {"sql": sql, "basejdbc": DB_JDBC}
    json_data = json.dumps(data).encode("utf-8")
    req = urllib.request.Request(
        DB_API_URL,
        data=json_data,
        headers={"Content-Type": "application/json"}
    )
    response = urllib.request.urlopen(req)
    result = json.loads(response.read().decode("utf-8"))

    if isinstance(result, list):
        return result
    if isinstance(result, dict):
        for key in ("data", "rows", "result", "records"):
            if key in result and isinstance(result[key], list):
                return result[key]
    return []


def parse_args():
    parser = argparse.ArgumentParser(description="查询储能设备告警记录")
    parser.add_argument("--battery_id",  type=int, help="电池组 ID")
    parser.add_argument("--fault_code",  type=str, help="故障代码，如 CELL_OV、CELL_OT、DEV_OFFLINE 等")
    parser.add_argument("--fault_level", type=str, choices=["WARNING", "CRITICAL"], help="故障级别")
    parser.add_argument("--start_time",  type=str, help="起始时间 YYYY-MM-DD 或 YYYY-MM-DD HH:MM:SS")
    parser.add_argument("--end_time",    type=str, help="结束时间 YYYY-MM-DD 或 YYYY-MM-DD HH:MM:SS")
    parser.add_argument("--granularity", type=str,
                        choices=["minute", "hour", "day", "month", "year"],
                        help="时间粒度聚合：minute/hour/day/month/year")
    parser.add_argument("--limit",       type=int, default=100, help="返回条数，默认 100")
    return parser.parse_args()


def normalize_time(time_str, end=False):
    """补全时间格式"""
    if not time_str:
        return None
    if len(time_str) == 10:
        return time_str + (" 23:59:59" if end else " 00:00:00")
    return time_str


def build_where(args):
    """构建 WHERE 子句和条件列表"""
    conditions = ["1=1"]
    if args.battery_id:
        conditions.append(f"r.battery_id = {int(args.battery_id)}")
    if args.fault_code:
        code = args.fault_code.upper().replace("'", "")
        conditions.append(f"r.fault_code = '{code}'")
    if args.fault_level:
        level = args.fault_level.upper().replace("'", "")
        conditions.append(f"r.fault_level = '{level}'")
    if args.start_time:
        t = normalize_time(args.start_time).replace("'", "")
        conditions.append(f"r.report_time >= '{t}'")
    if args.end_time:
        t = normalize_time(args.end_time, end=True).replace("'", "")
        conditions.append(f"r.report_time <= '{t}'")
    return " AND ".join(conditions)


def build_detail_sql(args):
    where = build_where(args)
    limit = int(args.limit)
    return f"""
        SELECT
            r.id,
            r.battery_id,
            b.name        AS battery_name,
            r.battery_monitor_id,
            r.fault_code,
            r.fault_name,
            r.fault_level,
            r.actual_value,
            r.threshold_value,
            r.report_time,
            r.create_time,
            s.root_cause,
            s.action_advice
        FROM tb_battery_fault_record r
        LEFT JOIN tb_battery b ON r.battery_id = b.id
        LEFT JOIN tb_battery_fault_strategy s ON r.fault_code = s.fault_code
        WHERE {where}
        ORDER BY r.report_time DESC
        LIMIT {limit}
    """


def build_aggregated_sql(args):
    granularity_map = {
        "minute": "DATE_FORMAT(r.report_time, '%Y-%m-%d %H:%i')",
        "hour":   "DATE_FORMAT(r.report_time, '%Y-%m-%d %H:00')",
        "day":    "DATE(r.report_time)",
        "month":  "DATE_FORMAT(r.report_time, '%Y-%m')",
        "year":   "YEAR(r.report_time)",
    }
    time_expr = granularity_map[args.granularity]
    where = build_where(args)
    limit = int(args.limit)
    return f"""
        SELECT
            {time_expr}                      AS time_bucket,
            r.fault_code,
            r.fault_name,
            r.fault_level,
            COUNT(*)                         AS fault_count,
            COUNT(DISTINCT r.battery_id)     AS affected_groups
        FROM tb_battery_fault_record r
        WHERE {where}
        GROUP BY time_bucket, r.fault_code, r.fault_name, r.fault_level
        ORDER BY time_bucket DESC, fault_count DESC
        LIMIT {limit}
    """


def main():
    args = parse_args()
    try:
        if args.granularity:
            sql = build_aggregated_sql(args)
            query_type = "aggregated"
        else:
            sql = build_detail_sql(args)
            query_type = "detail"

        rows = execute_sql(sql)

        result = {
            "status":     "success",
            "query_type": query_type,
            "total":      len(rows),
            "data":       rows,
            "message":    f"共查询到 {len(rows)} 条记录。" if rows else "未查询到符合条件的告警记录。",
        }
        print(json.dumps(result, ensure_ascii=False, indent=2))

    except Exception as e:
        result = {"status": "error", "message": f"查询失败: {e}"}
        print(json.dumps(result, ensure_ascii=False, indent=2))
        sys.exit(1)


if __name__ == "__main__":
    main()