#!/usr/bin/env python3
"""
Weekly Ticket Report - Query from Bitable
Generates weekly report and uploads to Feishu Wiki
"""

import json
import os
import subprocess
import tempfile
from datetime import date, datetime, timedelta
from pathlib import Path

import requests

# Load environment variables from ~/.bashrc
bashrc = os.path.expanduser("~/.bashrc")
if os.path.exists(bashrc):
    with open(bashrc) as f:
        for line in f:
            line = line.strip()
            if line.startswith("export ") and "=" in line:
                rest = line.replace("export ", "", 1)
                if "=" in rest:
                    key = rest.split("=")[0].strip()
                    value = rest.split("=", 1)[1].strip().strip('"').strip("'")
                    os.environ.setdefault(key, value)

def resolve_repo_root() -> str:
    """Resolve the repository root without relying on a hardcoded machine path."""
    env_repo = os.environ.get("AI_CONFIG_REPO")
    repo_markers = ("cronjobs", "skills")

    if env_repo:
        env_path = Path(env_repo).expanduser().resolve()
        if all((env_path / marker).exists() for marker in repo_markers):
            return str(env_path)

    return str(Path(__file__).resolve().parents[3])


os.environ["AI_CONFIG_REPO"] = resolve_repo_root()

APP_ID = os.environ["CRONJOB_TEAM_WORKFLOW_APP_ID"]
APP_SECRET = os.environ["CRONJOB_TEAM_WORKFLOW_APP_SECRET"]
MINIMAX_API_KEY = os.environ.get("MINIMAX_API_KEY", "")
MINIMAX_MODEL = os.environ.get("MINIMAX_MODEL", "MiniMax-Text-01")

# Grafana API
GRAFANA_API_TOKEN = os.environ.get("DATASERVICE_GRAFANA_TOKEN", "")
GRAFANA_DATASOURCE_ID = 37

# Tenant filter (from Grafana dashboard variables)
TENANT_FILTER = "tenant_id NOT IN (1372813089194211283, 1372813089187791289, 1372813089189381281, 1372813089208161328, 1372813089188691290)"
ALIAS_TENANT_FILTER = "a.tenant_id NOT IN (1372813089194211283, 1372813089187791289, 1372813089189381281, 1372813089208161328, 1372813089188691290)"
INTERNAL_TENANTS = (
    "1372813089194211283",
    "1372813089187791289",
    "1372813089189381281",
    "1372813089208161328",
    "1372813089188691290",
)


def query_grafana(sql: str, days: int = 7) -> list:
    """Query Grafana datasource and return results"""
    if not GRAFANA_API_TOKEN:
        return []
    
    url = "https://grafana.oauth.prod.aws.tidbcloud.com/api/ds/query"
    headers = {
        "Authorization": f"Bearer {GRAFANA_API_TOKEN}",
        "Content-Type": "application/json"
    }
    
    # Replace variables in SQL
    sql = sql.replace("${non_internal_tenant:sql_condition}", TENANT_FILTER)
    sql = sql.replace("${alias_non_internal_tenant:sql_condition}", ALIAS_TENANT_FILTER)
    sql = sql.replace("$__timeFilter(created_at)", f"created_at >= DATE_SUB(NOW(), INTERVAL {days} DAY)")
    sql = sql.replace("$__timeFilter(updated_at)", f"updated_at >= DATE_SUB(NOW(), INTERVAL {days} DAY)")
    
    payload = {
        "queries": [{
            "refId": "A",
            "datasourceId": GRAFANA_DATASOURCE_ID,
            "rawSql": sql,
            "format": "table"
        }],
        "from": f"now-{days}d",
        "to": "now"
    }
    
    try:
        resp = requests.post(url, headers=headers, json=payload, timeout=30)
        data = resp.json()
        
        results = data.get("results", {}).get("A", {}).get("frames", [])
        if not results:
            return []
        values = results[0].get("data", {}).get("values", [])
        if not values:
            return []
        
        # Return all columns as a list of rows
        # Each column is in values[0], values[1], etc.
        num_rows = len(values[0]) if values else 0
        rows = []
        for i in range(num_rows):
            row = [values[j][i] if i < len(values[j]) else None for j in range(len(values))]
            rows.append(row)
        return rows
    except Exception as e:
        print(f"Error querying Grafana: {e}")
        return []


def get_scalar_value(result, default=0):
    """Extract the first scalar cell from Grafana row results."""
    if not result:
        return default

    first = result[0]
    if isinstance(first, list):
        if not first:
            return default
        first = first[0]

    if first is None:
        return default

    return first


def normalize_failed_reason(value):
    """Normalize nullable failure text for display."""
    if value is None:
        return ""
    return str(value).strip()


def extract_dm_error_msg(*status_values):
    """Extract the first non-empty DM error message from JSON status fields."""
    for value in status_values:
        raw = normalize_failed_reason(value)
        if not raw:
            continue
        try:
            parsed = json.loads(raw)
        except Exception:
            continue
        error_msg = normalize_failed_reason(parsed.get("errorMsg")) if isinstance(parsed, dict) else ""
        if error_msg:
            return error_msg
    return ""


def format_table_cell(value, limit=None):
    """Format text safely for markdown tables."""
    text = "-" if value in (None, "") else str(value)
    text = text.replace("\r", " ").replace("\n", " ").replace("|", "\\|").strip()
    if limit and len(text) > limit:
        return text[:limit] + "..."
    return text


def normalize_scope_name(scope):
    """Normalize scope names for readable report output."""
    raw_scope = normalize_failed_reason(scope)
    if not raw_scope:
        return "Unknown"

    lowered = raw_scope.lower()
    if lowered == "lightning":
        return "Import"

    normalized = raw_scope.upper()
    if normalized == "IMPORT":
        return "Import"
    if normalized in {"BR", "DM", "CDC"}:
        return normalized

    return raw_scope


def normalize_summary_scope_name(scope):
    """Normalize scope names for the English summary block."""
    normalized = normalize_scope_name(scope)
    if normalized in {"Import", "BR", "DM", "CDC"}:
        return normalized.upper()
    return normalized


def format_rate(value):
    """Render success-rate style values consistently."""
    if value in (None, ""):
        return "-"
    return f"{value}%"


def build_count_parts(counts):
    """Render non-zero count breakdowns."""
    return ", ".join(f"{name}: {count}" for name, count in counts if count > 0)


def summarize_failed_jobs(failed_jobs):
    """Build per-component failed-job counts."""
    counts = {}
    for job in failed_jobs:
        component = normalize_scope_name(job.get("component", "Unknown"))
        counts[component] = counts.get(component, 0) + 1
    return sorted(counts.items(), key=lambda item: (-item[1], item[0]))


def build_jira_issue_link(issue_id):
    """Build Jira issue links for TCOC entries."""
    issue_key = normalize_failed_reason(issue_id)
    if not issue_key:
        return "-"
    return f"[{issue_key}](https://tidb.atlassian.net/browse/{issue_key})"


def week_time_range_ms(days=7):
    """Return the report week range in milliseconds for Grafana links."""
    end_date = date.today()
    start_date = end_date - timedelta(days=days - 1)
    start_dt = datetime.combine(start_date, datetime.min.time())
    end_dt = datetime.combine(end_date, datetime.max.time())
    return int(start_dt.timestamp() * 1000), int(end_dt.timestamp() * 1000)


def build_grafana_dashboard_links(days=7):
    """Build dashboard links constrained to the current report week."""
    start_ms, end_ms = week_time_range_ms(days)
    import_link = (
        "https://grafana.oauth.prod.aws.tidbcloud.com/d-sources/import-management"
        f"?orgId=1&from={start_ms}&to={end_ms}"
    )
    dm_link = (
        "https://grafana.oauth.prod.aws.tidbcloud.com/d/9sVoewCIz/global-control-user-metrics"
        f"?orgId=1&refresh=10s&from={start_ms}&to={end_ms}"
    )
    return import_link, dm_link


def get_import_stats(days: int = 7) -> dict:
    """Get Import stats from Grafana"""
    stats = {"total": 0, "completed": 0, "failed": 0, "precheck_failed": 0, "userdata_failed": 0, "server_failed": 0}
    tenant_filter = f"tenant_id NOT IN ({','.join(INTERNAL_TENANTS)}) AND "
    
    # Total
    sql = f'SELECT count(1) as Total FROM imports WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}server_tier="dedicated" AND status!="canceled"'
    result = query_grafana(sql, days)
    if result:
        stats["total"] = get_scalar_value(result)
    
    # Completed
    sql = f'SELECT count(1) as Completed FROM imports WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}server_tier="dedicated" AND status ="completed"'
    result = query_grafana(sql, days)
    if result:
        stats["completed"] = get_scalar_value(result)
    
    # Failed - Total
    sql = f'SELECT count(1) as Failed FROM imports WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}server_tier="dedicated" AND status ="failed"'
    result = query_grafana(sql, days)
    if result:
        stats["failed"] = get_scalar_value(result)
    
    # Failed - PreCheck
    sql = f'''SELECT count(*) as PreCheckFailed FROM imports WHERE {tenant_filter}server_tier="dedicated" AND status = "failed" AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND (message LIKE '%[Lightning:PreCheck:ErrCheckDataSource%' OR message LIKE '%[Lightning:PreCheck:ErrPreCheckFailed%')'''
    result = query_grafana(sql, days)
    if result:
        stats["precheck_failed"] = get_scalar_value(result)
    
    # Failed - UserData
    sql = f'''SELECT count(*) as UserDataFailed FROM imports WHERE {tenant_filter}server_tier="dedicated" AND status = "failed" AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND (message LIKE '%ErrChecksumMismatc%' OR message LIKE '%ErrRestoreTable%' OR message LIKE '%ErrEncodeKV%' OR message LIKE '%ErrSchemaNotExists%' OR message LIKE '%ErrCreateSchema%' OR message LIKE '%Data Too Long%' OR message LIKE '%Truncated incorrect%' OR message LIKE '%Incorrect datetime%' OR message LIKE '%contain data for all column%' OR message LIKE '%[parser:%' OR message LIKE '%[Lightning:Storage:ErrEmptySourceDir]%' OR message LIKE '%syntax error%' OR message LIKE '%server reject%' OR message LIKE '%cluster is deleted%')'''
    result = query_grafana(sql, days)
    if result:
        stats["userdata_failed"] = get_scalar_value(result)
    
    # Calculate success rate (Grafana style: 100 - failed/total)
    # This excludes running tasks from both success and failure
    if stats["total"] > 0:
        stats["success_rate"] = round(100 - (stats["failed"] / stats["total"]) * 100, 1)
    else:
        stats["success_rate"] = 0
    
    return stats


def get_br_stats(days: int = 7) -> dict:
    """Get BR (Backup) stats from Grafana"""
    stats = {"total": 0, "completed": 0, "failed": 0, "success_rate": 0}
    tenant_filter = f"tenant_id NOT IN ({','.join(INTERNAL_TENANTS)}) AND "
    
    # Total
    sql = f'SELECT count(1) as Total FROM backups WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}1=1'
    result = query_grafana(sql, days)
    if result:
        stats["total"] = get_scalar_value(result)
    
    # Completed (success + skipped)
    sql = f'SELECT count(1) as Completed FROM backups WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}status IN ("success", "skipped")'
    result = query_grafana(sql, days)
    if result:
        stats["completed"] = get_scalar_value(result)
    
    # Failed
    sql = f'SELECT count(1) as Failed FROM backups WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}status IN ("failed", "terminated")'
    result = query_grafana(sql, days)
    if result:
        stats["failed"] = get_scalar_value(result)
    
    # Success rate
    if stats["total"] > 0:
        stats["success_rate"] = round((stats["completed"] / stats["total"]) * 100, 1)
    
    return stats


def get_cdc_stats(days: int = 7) -> dict:
    """Get CDC stats from Grafana"""
    stats = {"total": 0, "running": 0}
    tenant_filter = f"tenant_id NOT IN ({','.join(INTERNAL_TENANTS)}) AND "
    
    # Total (last 7 days)
    sql = f'SELECT count(*) as Total FROM biz_changefeeds WHERE operation_status!="deleted" AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}1=1'
    result = query_grafana(sql, days)
    if result:
        stats["total"] = get_scalar_value(result)
    
    # Running (current)
    sql = f'SELECT count(*) as Running FROM biz_changefeeds WHERE operation_status!="deleted" AND {tenant_filter}1=1'
    result = query_grafana(sql, days)
    if result:
        stats["running"] = get_scalar_value(result)
    
    return stats


def get_dm_stats(days: int = 7) -> dict:
    """Get DM stats from Grafana"""
    stats = {"total": 0, "running": 0}
    tenant_filter = f"tenant_id NOT IN ({','.join(INTERNAL_TENANTS)}) AND "
    
    # Total
    sql = f'SELECT count(1) as Total FROM df_jobs WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND {tenant_filter}service_code="dm"'
    result = query_grafana(sql, days)
    if result:
        stats["total"] = get_scalar_value(result)
    
    # Running
    sql = f'SELECT count(1) as Running FROM df_jobs WHERE {tenant_filter}service_code="dm" AND phase NOT IN ("finished","deleted")'
    result = query_grafana(sql, days)
    if result:
        stats["running"] = get_scalar_value(result)
    
    return stats


def analyze_risk(report_content):
    """Use MiniMax to analyze risk and generate summary"""
    if not MINIMAX_API_KEY:
        return None
    
    prompt = f"""Based on the following weekly report, generate a concise summary for quick team information presentation (in English, about 80 words, use markdown format with line breaks for key points).

Weekly report content:
{report_content}

Output the summary directly in markdown format (like **bold**, line breaks), no prefix or inner thoughts needed."""

    url = "https://api.minimax.io/v1/text/chatcompletion_v2"
    headers = {
        "Authorization": f"Bearer {MINIMAX_API_KEY}",
        "Content-Type": "application/json"
    }
    
    data = {
        "model": MINIMAX_MODEL,
        "messages": [
            {"role": "system", "content": "You are a helpful assistant."},
            {"role": "user", "content": prompt}
        ],
        "temperature": 0.7,
        "max_completion_tokens": 300
    }
    
    try:
        resp = requests.post(url, headers=headers, json=data, timeout=30)
        result = resp.json()
        if result.get("choices"):
            summary = result["choices"][0]["message"]["content"].strip()
            # Remove leading special characters and markdown code blocks
            while summary:
                # Remove ```markdown, ```, >, ❝, quotes, etc.
                if summary.startswith('```'):
                    # Find end of code block
                    end = summary.find('```', 3)
                    if end > 0:
                        summary = summary[end+3:].strip()
                    else:
                        summary = summary[3:].strip()
                elif summary[0] in '>❝❞"\'' or summary[0] in ' \t\n\r[':
                    summary = summary[1:].strip()
                else:
                    break
            # Also remove trailing garbage
            if '[/IGNORE]' in summary or '****' in summary:
                summary = summary.split('****')[0].split('[/IGNORE]')[0].strip()
            return summary
    except Exception as e:
        print(f"Warning: Failed to analyze risk: {e}")
    return None

ONCALL_APP_TOKEN = "DK5Ib7kmAadIoWsUV8tczRmwnkh"
ONCALL_TABLE_ID = "tblnZt7BJBTzYMvJ"

# Failed Jobs Bitable (from daily-oncall-metrics)
FAILED_JOBS_APP_TOKEN = os.environ.get("FAILED_JOBS_APP_TOKEN", "xxx")
FAILED_JOBS_TABLE_ID = os.environ.get("FAILED_JOBS_TABLE_ID", "tblxxx")

# Wiki configuration
WIKI_SPACE_ID = os.environ.get("CRONJOB_TEAM_WIKI_SPACE_ID", "7594383226739674063")
WIKI_PARENT_NODE = os.environ.get("CRONJOB_TEAM_WIKI_PARENT_NODE", "XRxkwkP3Ei8B10kgDTfcp0mGnte")
WIKI_BASE_URL = "https://pingcap.feishu.cn/wiki"

# Notification user
USER_ID = os.environ.get("CRONJOB_TEAM_WORKFLOW_NOTIFY_USER", "ou_a05710e0f6a4126170ba917d50a20300")
NOTIFY_CHAT_ID = os.environ.get("CRONJOB_TEAM_WORKFLOW_NOTIFY_CHAT_ID", "oc_2c586abb5204eca2ddf6ddf5de338bf6")
SKIP_SEND_NOTIFICATION = os.environ.get("SKIP_SEND_NOTIFICATION", "").lower() == "true"

# md2feishu script path
AI_CONFIG_REPO = os.environ["AI_CONFIG_REPO"]
MD2FEISHU_SCRIPT = os.path.join(AI_CONFIG_REPO, "skills/team-common/md2feishu/scripts/md2feishu.py")


def get_token():
    resp = requests.post(
        "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal",
        headers={"Content-Type": "application/json"},
        json={"app_id": APP_ID, "app_secret": APP_SECRET}
    )
    return resp.json().get("tenant_access_token")


def send_notification(msg):
    """Send notification via Feishu IM or group chat"""
    if SKIP_SEND_NOTIFICATION:
        print("SKIP_SEND_NOTIFICATION=true, skipping notification...")
        return

    token = get_token()
    
    # Prefer chat_id if set, otherwise use user open_id
    if NOTIFY_CHAT_ID:
        url = "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=chat_id"
        receive_id = NOTIFY_CHAT_ID
    else:
        url = "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=open_id"
        receive_id = USER_ID
    
    headers = {
        "Authorization": f"Bearer {token}",
        "Content-Type": "application/json"
    }
    
    # Use card format
    card_content = {
        "config": {"wide_screen_mode": True},
        "header": {
            "title": {"tag": "plain_text", "content": f"📊 {date.today().strftime('%Y-%m-%d')} Weekly Report"},
            "template": "blue"
        },
        "elements": [
            {
                "tag": "markdown",
                "content": msg
            }
        ]
    }
    
    data = {
        "receive_id": receive_id,
        "msg_type": "interactive",
        "content": json.dumps(card_content)
    }
    
    resp = requests.post(url, headers=headers, json=data)
    if resp.status_code == 200:
        result = resp.json()
        if result.get("code") == 0:
            print("Notification sent!")
        else:
            print(f"Failed to send notification: {result}")
    else:
        print(f"Failed to send notification: {resp.text}")


def query_records(days=7):
    token = get_token()
    end_date = date.today()
    start_date = end_date - timedelta(days=days)
    start_ts = int(datetime.combine(start_date, datetime.min.time()).timestamp() * 1000)
    end_ts = int(datetime.combine(end_date, datetime.max.time()).timestamp() * 1000)
    
    url = f"https://open.feishu.cn/open-apis/bitable/v1/apps/{ONCALL_APP_TOKEN}/tables/{ONCALL_TABLE_ID}/records"
    headers = {"Authorization": f"Bearer {token}"}
    
    records = []
    page_token = None
    
    while True:
        params = {"page_size": 100}
        if page_token:
            params["page_token"] = page_token
        resp = requests.get(url, headers=headers, params=params)
        data = resp.json()
        
        if data.get("code") != 0:
            break
        
        for record in data.get("data", {}).get("items", []):
            fields = record.get("fields", {})
            created_at = fields.get("CreatedAt", 0)
            if start_ts <= created_at <= end_ts:
                records.append(fields)
        
        page_token = data.get("data", {}).get("page_token")
        if not page_token:
            break
    
    return records


def query_failed_jobs_from_grafana(days=7):
    """Query failed Import/DM jobs from Grafana (same as daily-oncall-metrics failed_job_sync)"""
    if not GRAFANA_API_TOKEN:
        print("Warning: DATASERVICE_GRAFANA_TOKEN not set, skipping failed jobs query")
        return []
    
    from datetime import date, timedelta
    end_date = date.today()
    start_date = end_date - timedelta(days=days)
    
    # Internal tenants to exclude
    INTERNAL_TENANTS = (
        "1372813089194211283",
        "1372813089187791289",
        "1372813089189381281",
        "1372813089208161328",
        "1372813089188691290",
    )
    
    failed_jobs = []
    
    # Query failed Import jobs
    start_str = start_date.strftime("%Y-%m-%d")
    end_str = (end_date + timedelta(days=1)).strftime("%Y-%m-%d")
    date_filter = f"AND created_at >= '{start_str}' AND created_at < '{end_str}'"
    
    sql = f"""SELECT CAST(cluster_id AS CHAR), CAST(id AS CHAR), message, created_at, CAST(tenant_id AS CHAR), CAST(project_id AS CHAR) FROM imports WHERE status = 'failed' {date_filter} AND tenant_id NOT IN ({','.join(INTERNAL_TENANTS)}) ORDER BY created_at DESC"""
    
    rows = query_grafana(sql, days)
    # rows is now a list of lists: [[cluster, id, message, created_at, tenant_id, project_id], ...]
    if rows:
        for row in rows:
            if len(row) >= 3:
                failed_jobs.append({
                    "job_id": str(row[1]) if row[1] else "",
                    "component": "Import",
                    "cluster": str(row[0]) if row[0] else "",
                    "failed_reason": str(row[2])[:200] if row[2] else "",
                })
    
    print(f"Found {len(failed_jobs)} failed Import jobs")
    
    # Query failed DM jobs (use same SQL as failed_job_sync)
    dm_sql = f"""
    SELECT *
    FROM (
        SELECT
            a.id,
            IF(
                a.error_message = ""
                && (JSON_EXTRACT(c.dump_status, '$.errorMsg') IS NULL OR JSON_TYPE(JSON_EXTRACT(c.dump_status, '$.errorMsg')) = 'NULL')
                && (JSON_EXTRACT(c.load_status, '$.errorMsg') IS NULL OR JSON_TYPE(JSON_EXTRACT(c.load_status, '$.errorMsg')) = 'NULL')
                && (JSON_EXTRACT(c.sync_status, '$.errorMsg') IS NULL OR JSON_TYPE(JSON_EXTRACT(c.sync_status, '$.errorMsg')) = 'NULL'),
                'Normal',
                'Error'
            ) AS status,
            concat(a.cluster_id, '') as cluster_id,
            concat(a.project_id, '') as project_id,
            concat(a.tenant_id, '') as tenant_id,
            a.created_at,
            a.error_message,
            c.dump_status,
            c.load_status,
            c.sync_status
        FROM df_jobs a
        JOIN dm_jobs b ON a.id = b.id
        JOIN dm_job_sources c ON a.id = c.job_id
        WHERE
            a.service_code = 'dm'
            AND a.created_at >= '{start_str}' AND a.created_at < '{end_str}'
    ) t
    WHERE status = 'Error'
    ORDER BY id DESC
    LIMIT 100
    """
    
    dm_rows = query_grafana(dm_sql, days)
    # DM columns: 0=id, 1=status, 2=cluster_id, 3=project_id, 4=tenant_id,
    # 5=created_at, 6=error_message, 7=dump_status, 8=load_status, 9=sync_status
    if dm_rows:
        for row in dm_rows:
            if len(row) >= 2 and str(row[1]) == "Error":  # Only include rows with status=Error
                error_message = normalize_failed_reason(row[6] if len(row) > 6 else "")
                if not error_message:
                    error_message = extract_dm_error_msg(
                        row[7] if len(row) > 7 else "",
                        row[8] if len(row) > 8 else "",
                        row[9] if len(row) > 9 else "",
                    )

                failed_jobs.append({
                    "job_id": str(row[0]) if row[0] else "",
                    "component": "DM",
                    "cluster": str(row[2]) if len(row) > 2 and row[2] else "",
                    "failed_reason": error_message or "DM error",
                })
    
    print(f"Found {len(failed_jobs)} total failed jobs (Import + DM)")
    return failed_jobs


def generate_summary_section(records, import_stats=None, br_stats=None, cdc_stats=None, dm_stats=None):
    """Generate localized and English summary sections for the report."""
    # TCOC statistics
    tcoc = [r for r in records if r.get("Category") == "TCOC" or (r.get("Link", {}).get("text", "").startswith("TCOC-"))]
    
    # Alert statistics
    alert = [r for r in records if r.get("Category") == "ALERT" or (r.get("Link", {}).get("text", "").startswith("O11Y-"))]
    
    # Group TCOC by scope
    tcoc_by_scope = {}
    for r in tcoc:
        scope = normalize_summary_scope_name(r.get("Scope", "Unknown"))
        tcoc_by_scope[scope] = tcoc_by_scope.get(scope, 0) + 1
    
    # Group Alert by scope
    alert_by_scope = {}
    for r in alert:
        scope = normalize_summary_scope_name(r.get("Scope", "Unknown"))
        alert_by_scope[scope] = alert_by_scope.get(scope, 0) + 1
    
    # Build summary text in the requested format
    lines = []
    lines.append("## 汇总")
    lines.append("")
    lines.append("- **任务运行数据**")
    # Use Grafana stats if available
    if import_stats:
        imp = import_stats
        lines.append(f"  - Import 新增 {imp.get('total', 0)} 任务，失败 {imp.get('failed', 0)} 任务，成功率 {imp.get('success_rate', 0)}%")
    else:
        lines.append("  - Import 新增 0 任务，失败 0 任务，成功率 0%")
    if br_stats:
        br = br_stats
        lines.append(f"  - BR 新增 {br.get('total', 0)} 任务，失败 {br.get('failed', 0)} 任务，成功率 {br.get('success_rate', 0)}%")
    else:
        lines.append("  - BR 新增 0 任务，失败 0 任务，成功率 0%")
    if cdc_stats:
        cdc = cdc_stats
        lines.append(f"  - CDC 新增 {cdc.get('total', 0)} 任务，持续运行 {cdc.get('running', 0)} 个")
    if dm_stats:
        dm = dm_stats
        lines.append(f"  - DM 新增 {dm.get('total', 0)} 任务，持续运行 {dm.get('running', 0)} 个")
    lines.append("")
    lines.append("- **风险情况**")
    
    # TCOC summary - sort by count descending, merge to single line
    tcoc_items = [(k, v) for k, v in tcoc_by_scope.items() if v > 0]
    tcoc_items.sort(key=lambda x: -x[1])  # Sort by count descending
    tcoc_parts = [f"{k}: {v}" for k, v in tcoc_items]
    if tcoc_parts:
        lines.append(f"  - TCOC 共计 {len(tcoc)} 个 ({', '.join(tcoc_parts)})")
    else:
        lines.append(f"  - TCOC 共计 {len(tcoc)} 个")
    
    # Alert summary - sort by count descending, merge to single line
    alert_items = [(k, v) for k, v in alert_by_scope.items() if v > 0]
    alert_items.sort(key=lambda x: -x[1])  # Sort by count descending
    alert_parts = [f"{k}: {v}" for k, v in alert_items]
    if alert_parts:
        lines.append(f"  - Critical Alert 共 {len(alert)} 个 ({', '.join(alert_parts)})")
    else:
        lines.append(f"  - Critical Alert 共 {len(alert)} 个")
    
    # Generate English summary
    en_lines = []
    en_lines.append("## Summary")
    en_lines.append("")
    en_lines.append("- **Job Stats**")
    if import_stats:
        imp = import_stats
        en_lines.append(f"  - Import: {imp.get('total', 0)} new, {imp.get('failed', 0)} failed ({imp.get('precheck_failed', 0)} PreCheck, {imp.get('userdata_failed', 0)} user data), success rate {imp.get('success_rate', 0)}%")
    if br_stats:
        br = br_stats
        en_lines.append(f"  - BR: {br.get('total', 0)} new, {br.get('failed', 0)} failed, success rate {br.get('success_rate', 0)}%")
    if cdc_stats:
        cdc = cdc_stats
        en_lines.append(f"  - CDC: {cdc.get('total', 0)} new, {cdc.get('running', 0)} running")
    if dm_stats:
        dm = dm_stats
        en_lines.append(f"  - DM: {dm.get('total', 0)} new, {dm.get('running', 0)} running")
    en_lines.append("")
    en_lines.append("- **Risk Status**")
    
    if tcoc_parts:
        en_lines.append(f"  - TCOC: {len(tcoc)} total ({', '.join(tcoc_parts)})")
    else:
        en_lines.append(f"  - TCOC: {len(tcoc)} total")
    
    if alert_parts:
        en_lines.append(f"  - Critical Alert: {len(alert)} total ({', '.join(alert_parts)})")
    else:
        en_lines.append(f"  - Critical Alert: {len(alert)} total")
    
    english_summary = "\n".join(en_lines)
    
    return "\n".join(lines), english_summary


def generate_report(records):
    end_date = date.today()
    start_date = end_date - timedelta(days=6)
    week_label = f"{start_date.strftime('%Y-%m-%d')} ~ {end_date.strftime('%Y-%m-%d')}"
    
    tcoc = [r for r in records if r.get("Category") == "TCOC" or (r.get("Link", {}).get("text", "").startswith("TCOC-"))]
    alert = [r for r in records if r.get("Category") == "ALERT" or (r.get("Link", {}).get("text", "").startswith("O11Y-"))]
    
    # Group by Type + Category for Summary
    summary_groups = {}
    # TCOC entries by category
    for r in tcoc:
        scope = normalize_scope_name(r.get("Scope", "Unknown"))
        key = ("TCOC", scope)
        summary_groups[key] = summary_groups.get(key, 0) + 1
    # Alert entries by category
    for r in alert:
        category = normalize_scope_name(r.get("Scope", "Unknown"))
        key = ("Critical Alert", category)
        summary_groups[key] = summary_groups.get(key, 0) + 1
    
    # Get Grafana stats
    print("Fetching Grafana stats...")
    import_stats = get_import_stats(7)
    br_stats = get_br_stats(7)
    cdc_stats = get_cdc_stats(7)
    dm_stats = get_dm_stats(7)
    print(f"Import: {import_stats}")
    print(f"BR: {br_stats}")
    print(f"CDC: {cdc_stats}")
    print(f"DM: {dm_stats}")
    
    # Get failed jobs from Bitable
    print("Fetching failed jobs from Bitable...")
    failed_jobs = query_failed_jobs_from_grafana(7)
    
    _, english_summary = generate_summary_section(records, import_stats, br_stats, cdc_stats, dm_stats)
    _, dm_dashboard_link = build_grafana_dashboard_links(7)

    report = f"""# TCOC&O11Y Weekly Report

**Week:** {week_label}

{english_summary}

## TCOC Details

| ID | Category | Status | Created At | Summary |
|----|----------|--------|------------|---------|
"""
    tcoc_status_priority = {
        "Todo": 0,
        "Under Investigation": 1,
        "In Progress": 2,
        "In Delivery": 3,
        "ESCALATE TO L3": 4,
        "Resolved": 5,
        "Done": 6,
    }
    sorted_tcoc = sorted(
        tcoc,
        key=lambda record: (
            tcoc_status_priority.get(record.get("Status", "Unknown"), 99),
            -(record.get("CreatedAt", 0) or 0),
        ),
    )
    for r in sorted_tcoc:
        link = r.get("Link", {})
        title = r.get("Title", "")
        status = r.get("Status", "Unknown")
        ts = r.get("CreatedAt", 0)
        created = datetime.fromtimestamp(ts/1000).strftime("%Y-%m-%d") if ts else "-"
        link_text = link.get("text", "-") if isinstance(link, dict) else "-"
        scope = normalize_scope_name(r.get("Scope", "Unknown"))
        report += f"| {build_jira_issue_link(link_text)} | {scope} | {status} | {created} | {format_table_cell(title, limit=120)} |\n"

    report += """
## O11Y Alert Details

| Count | Category | Alert Summary |
|-------|----------|---------------|
"""
    # Group alerts by summary and count
    alert_groups = {}
    for r in alert:
        title = r.get("Title", "Unknown")
        category = normalize_scope_name(r.get("Scope", "Unknown"))
        key = (category, title)
        alert_groups[key] = alert_groups.get(key, 0) + 1
    
    # Sort by Num (count) descending
    for (category, title), num in sorted(alert_groups.items(), key=lambda item: (-item[1], item[0][0], item[0][1])):
        report += f"| {num} | {category} | {format_table_cell(title, limit=72)} |\n"
    
    # Add failed tasks table at the end (from failed_jobs Bitable)
    if failed_jobs:
        report += "\n\n## Failed Tasks Details\n\n"
        report += f"- [Dashboard - This Week]({dm_dashboard_link})\n\n"
        report += "| Job ID | Component | Cluster | Failed Reason |\n"
        report += "|--------|-----------|---------|---------------|\n"
        for job in failed_jobs[:20]:  # Limit to 20
            job_id = format_table_cell(job.get("job_id", "-"))
            component = format_table_cell(normalize_scope_name(job.get("component", "-")))
            cluster = format_table_cell(job.get("cluster", "-"), limit=60)
            reason = format_table_cell(job.get("failed_reason", "-"), limit=180)
            report += f"| {job_id} | {component} | {cluster} | {reason} |\n"
        
        if len(failed_jobs) > 20:
            report += f"\n*... and {len(failed_jobs) - 20} more failed tasks*\n"

    report += f"""

---
*Generated on {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}*
"""
    
    return report, import_stats, br_stats, cdc_stats, dm_stats, failed_jobs


def create_wiki_node(title):
    """Create wiki node and return doc_token and wiki_token"""
    token = get_token()
    
    resp = requests.post(
        f"https://open.feishu.cn/open-apis/wiki/v2/spaces/{WIKI_SPACE_ID}/nodes",
        json={
            "obj_type": "docx",
            "parent_node_token": WIKI_PARENT_NODE,
            "node_type": "origin",
            "title": title,
        },
        headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
    )
    result = resp.json()
    if result.get("code") != 0:
        raise Exception(f"创建Wiki节点失败: {result}")
    
    node = result["data"]["node"]
    return node["obj_token"], node["node_token"]


def upload_report(content):
    """Upload report to Wiki using md2feishu"""
    if not os.path.exists(MD2FEISHU_SCRIPT):
        raise FileNotFoundError(f"md2feishu script not found: {MD2FEISHU_SCRIPT}")

    # Create wiki node first
    title = f"TCOC&O11Y Weekly Report {date.today().strftime('%Y%m%d')}"
    print(f"Creating wiki node: {title}")
    doc_token, wiki_token = create_wiki_node(title)
    wiki_url = f"{WIKI_BASE_URL}/{wiki_token}"
    print(f"Wiki created: {wiki_url}")
    
    # Write content to file (save locally for debugging)
    output_dir = os.path.expanduser("~/.openclaw/workspace-defaults/openclaw-team/skill-scripts")
    os.makedirs(output_dir, exist_ok=True)
    md_file = os.path.join(output_dir, f"weekly_report_{date.today().strftime('%Y%m%d')}.md")
    with open(md_file, 'w') as f:
        f.write(content)
    print(f"Report saved to: {md_file}")
    
    # Write content using md2feishu
    with tempfile.NamedTemporaryFile(mode='w', suffix='.md', delete=False) as f:
        f.write(content)
        temp_file = f.name
    
    try:
        cmd = [
            "/usr/bin/python3", MD2FEISHU_SCRIPT,
            "--app-id", APP_ID,
            "--app-secret", APP_SECRET,
            "--file", temp_file,
            "--title", title,
            "--doc-id", doc_token
        ]
        result = subprocess.run(cmd, capture_output=True, text=True, timeout=600, env=os.environ)
        
        if result.returncode != 0:
            raise Exception(f"上传失败: {result.stderr}")
        
        if result.stderr:
            print(f"Debug: {result.stderr}")
        
        print("Content uploaded with formatting!")
        return wiki_url
    finally:
        if os.path.exists(temp_file):
            os.remove(temp_file)


if __name__ == "__main__":
    print("=== Weekly Ticket Report ===")
    print(f"Date: {date.today()}")
    
    # Check if should skip upload
    skip_upload = os.environ.get("SKIP_UPLOAD_FEISHU_DOC", "").lower() == "true"
    print(f"\nSkip upload: {skip_upload}")
    
    print("\nQuerying Bitable...")
    records = query_records(days=7)
    print(f"Found {len(records)} records")
    
    print("\nGenerating report...")
    report, import_stats, br_stats, cdc_stats, dm_stats, failed_jobs = generate_report(records)
    
    # Generate summary for notification
    summary_section, english_summary = generate_summary_section(records, import_stats, br_stats, cdc_stats, dm_stats)
    
    print(report[:500])
    
    # Analyze risk with MiniMax (commented out for now)
    # print("\nAnalyzing risk...")
    # risk_summary = analyze_risk(report)
    risk_summary = None
    # if risk_summary:
    #     print(f"Risk summary: {risk_summary}")
    #     # Add summary at the beginning of the report (as blockquote)
    #     report = f"> {risk_summary}\n\n---\n\n{report}"
    
    print("\nCreating Feishu document...")
    
    if skip_upload:
        print("SKIP_UPLOAD_FEISHU_DOC=true, skipping upload...")
        # Still generate wiki link for notification
        title = f"TCOC&O11Y Weekly Report {date.today().strftime('%Y%m%d')}"
        wiki_url = f"{WIKI_BASE_URL}/(skipped)"
        doc_url = wiki_url
    else:
        doc_url = upload_report(report)
    
    if doc_url:
        print(f"\n📄 Document: {doc_url}")
        # Send notification with summary
        # Convert markdown summary to text
        summary_text = summary_section.replace("## ", "").replace("### ", "- ").replace("\n", "\n")
        msg = f"""{summary_text}

📄 文档: {doc_url}"""
        send_notification(msg)
    else:
        print("\n❌ Failed to create document")
    
    print("\nDone!")