"""
現在地とチーム基準: sales_quarter_plan 테이블 접근 + 1s 목표치 조회.
"""
from typing import Dict, Any, List, Optional
import mysql.connector
from app.core import logger
from app.core.config import get_db_config
from app.models.sales_sheet_module import get_sales_sheet_by_session
from app.models.general_module import get_username

A_DB_CONFIG = get_db_config("Sales_A_DB")

# 분기 계획 대상 6개 카테고리 (2,3,4,5,6,8)
QUARTER_PLAN_CATEGORIES = (2, 3, 4, 5, 6, 8)
CATEGORY_NAMES = {
    2: "架電数",
    3: "繋がるための行動",
    4: "アポ",
    5: "訪問",
    6: "見積",
    8: "受注",
}


def get_quarter_targets(session_id: str) -> Dict[str, Any]:
    """
    1s, quarter, record_type=target 인 목표치만 조회.
    category IN (2,3,4,5,6,8).
    Returns: { "targets": {"2": 680, ...}, "category_names": {"2": "架電数", ...} }
    """
    rows = get_sales_sheet_by_session(session_id, "1s")
    quarter_targets = [
        r for r in rows
        if r.get("period_type") == "quarter"
        and r.get("record_type") == "target"
        and r.get("category") in QUARTER_PLAN_CATEGORIES
    ]
    targets = {}
    for r in quarter_targets:
        cat = r.get("category")
        if cat is None:
            continue
        try:
            val = float(r.get("value") or 0)
        except (TypeError, ValueError):
            val = 0
        targets[str(cat)] = int(val) if val == int(val) else round(val, 1)
    category_names = {str(c): CATEGORY_NAMES.get(c, f"項目{c}") for c in QUARTER_PLAN_CATEGORIES}
    return {"targets": targets, "category_names": category_names}


def get_quarter_targets_and_results(session_id: str) -> Dict[str, Any]:
    """
    1s, quarter の target + result を sales_sheet_data から取得（フォーム事前表示・02/19データ用）.
    Returns: { "targets": {"2": 680, ...}, "results": {"2": 400, ...}, "category_names": {...}, "data_date": "02/19" }
    """
    rows = get_sales_sheet_by_session(session_id, "1s")
    quarter_rows = [r for r in rows if r.get("period_type") == "quarter" and r.get("category") in QUARTER_PLAN_CATEGORIES]
    targets = {}
    results = {}
    for r in quarter_rows:
        cat = r.get("category")
        if cat is None:
            continue
        try:
            val = float(r.get("value") or 0)
        except (TypeError, ValueError):
            val = 0
        key = str(cat)
        if r.get("record_type") == "target":
            targets[key] = int(val) if val == int(val) else round(val, 1)
        elif r.get("record_type") == "result":
            results[key] = int(val) if val == int(val) else round(val, 1)
    category_names = {str(c): CATEGORY_NAMES.get(c, f"項目{c}") for c in QUARTER_PLAN_CATEGORIES}
    return {
        "targets": targets,
        "results": results,
        "category_names": category_names,
        "data_date": "02/19",
    }


def get_user_achievement_summary(session_id: str) -> Dict[str, Any]:
    """
    1s quarter の result/target で 개인 달성율만 계산（팀 없이）.
    Returns: { "by_category": { "2": { "target": 680, "result": 400, "rate": 58.8 }, ... }, "categories_order": [...], "username": ... }
    """
    info = get_quarter_targets_and_results(session_id)
    targets = info.get("targets") or {}
    results = info.get("results") or {}
    categories_order = [str(c) for c in QUARTER_PLAN_CATEGORIES]
    by_category = {}
    for cat_str in categories_order:
        target = targets.get(cat_str) or 0
        result = results.get(cat_str) or 0
        rate = round((result / target * 100), 1) if target and target > 0 else 0
        by_category[cat_str] = {
            "target": target,
            "result": result,
            "rate": rate,
            "category_name": CATEGORY_NAMES.get(int(cat_str), f"項目{cat_str}"),
        }
    username = get_username(session_id) or "あなた"
    return {"by_category": by_category, "categories_order": categories_order, "username": username}


def save_quarter_plan(session_id: str, period_type: str, plans: Dict[str, Any]) -> int:
    """
    sales_quarter_plan 에 INSERT or ON DUPLICATE KEY UPDATE.
    plans: { "2": 400, "3": 70, ... }
    """
    if not session_id or not plans:
        return 0
    period_type = period_type or "quarter"
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SET time_zone = '+09:00'")
        sql = """
            INSERT INTO sales_quarter_plan (session_id, period_type, category, planned_value)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE planned_value = VALUES(planned_value), updated_at = CURRENT_TIMESTAMP
        """
        count = 0
        for cat_str, val in plans.items():
            try:
                cat = int(cat_str)
            except (TypeError, ValueError):
                continue
            if cat not in QUARTER_PLAN_CATEGORIES:
                continue
            try:
                v = float(val) if val is not None else None
            except (TypeError, ValueError):
                v = None
            if v is None or v < 0:
                continue
            cursor.execute(sql, (session_id, period_type, cat, v))
            count += 1
        conn.commit()
        cursor.close()
        conn.close()
        return count
    except Exception as e:
        logger.error(f"sales_quarter_plan save error: {e}")
        raise


def get_quarter_plan_summary(session_id: str, period_type: str = "quarter") -> Dict[str, Any]:
    """
    target(1s) + planned(sales_quarter_plan) 으로 카테고리별 계획 달성률 계산.
    Returns: { "by_category": { "2": { "target": 680, "planned": 400, "rate": 58.8 }, ... } }
    """
    info = get_quarter_targets(session_id)
    targets = info.get("targets") or {}
    if not targets:
        return {"by_category": {}}
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SET time_zone = '+09:00'")
        cursor.execute(
            """
            SELECT category, planned_value
            FROM sales_quarter_plan
            WHERE session_id = %s AND period_type = %s AND category IN (2,3,4,5,6,8)
            """,
            (session_id, period_type),
        )
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
    except Exception as e:
        logger.error(f"sales_quarter_plan get error: {e}")
        return {"by_category": {}}
    planned_map = {str(r["category"]): float(r["planned_value"] or 0) for r in rows}
    by_category = {}
    for cat_str, target in targets.items():
        planned = planned_map.get(cat_str, 0)
        rate = round((planned / target * 100), 1) if target and target > 0 else 0
        by_category[cat_str] = {
            "target": target,
            "planned": planned,
            "rate": rate,
        }
    return {"by_category": by_category}


def get_team_quarter_rates(session_id: str) -> Dict[str, Dict[str, Any]]:
    """
    2s(팀 시트)에서 quarter, category in (2,3,4,5,6,8) 의 target/result 조회 후 팀 달성율 계산.
    Returns: { "2": { "target": 700, "result": 500, "rate": 71.4 }, ... }
    """
    rows = get_sales_sheet_by_session(session_id, "2s")
    quarter_rows = [r for r in rows if r.get("period_type") == "quarter" and r.get("category") in QUARTER_PLAN_CATEGORIES]
    by_cat = {}
    for r in quarter_rows:
        cat = r.get("category")
        if cat is None:
            continue
        try:
            val = float(r.get("value") or 0)
        except (TypeError, ValueError):
            val = 0
        key = str(cat)
        if key not in by_cat:
            by_cat[key] = {"target": None, "result": None}
        if r.get("record_type") == "target":
            by_cat[key]["target"] = val
        elif r.get("record_type") == "result":
            by_cat[key]["result"] = val
    out = {}
    for cat_str, v in by_cat.items():
        target = v.get("target") or 0
        result = v.get("result") or 0
        rate = round((result / target * 100), 1) if target and target > 0 else 0
        out[cat_str] = {"target": target, "result": result, "rate": rate}
    return out


def get_comparison_data(session_id: str, period_type: str = "quarter") -> Dict[str, Any]:
    """
    ユーザー達成率(1s result) vs チーム達成率 비교.
    Returns: {
      "by_category": {
        "2": {
          "category_name": "架電数",
          "user_target": 680, "user_result": 400, "user_rate": 58.8,
          "team_target": 700, "team_result": 500, "team_rate": 71.4,
          "diff": -12.6,   // user_rate - team_rate
          "user_result_minus_team": -100  // チームより〇〇件多く/少なく
        }, ...
      },
      "categories_order": ["2","3","4","5","6","8"],
      "username": "田中"
    }
    """
    user_summary = get_user_achievement_summary(session_id)
    team_rates = get_team_quarter_rates(session_id)
    by_cat = user_summary.get("by_category") or {}
    categories_order = user_summary.get("categories_order") or [str(c) for c in QUARTER_PLAN_CATEGORIES]
    result = {}
    for cat_str in categories_order:
        u = by_cat.get(cat_str) or {}
        t = team_rates.get(cat_str) or {}
        user_rate = u.get("rate") or 0
        user_result = u.get("result") or 0
        team_rate = t.get("rate") or 0
        team_result = t.get("result") or 0
        diff = round(user_rate - team_rate, 1)
        user_result_minus_team = round((user_result or 0) - (team_result or 0), 0)
        result[cat_str] = {
            "category_name": CATEGORY_NAMES.get(int(cat_str), f"項目{cat_str}"),
            "user_target": u.get("target"),
            "user_result": u.get("result"),
            "user_rate": user_rate,
            "team_target": t.get("target"),
            "team_result": t.get("result"),
            "team_rate": team_rate,
            "diff": diff,
            "user_result_minus_team": int(user_result_minus_team),
        }
    username = get_username(session_id) or "あなた"
    return {"by_category": result, "categories_order": categories_order, "username": username}