#!/usr/bin/env python3 """ Jira Ticket Sync Syncs TCOC and O11Y Critical Alert tickets from Jira to Bitable """ import os import sys import requests from datetime import datetime, date, timedelta from base import ( get_bitable_records, list_bitable_records, add_to_bitable, load_env_from_bashrc ) # Load env load_env_from_bashrc() # Re-initialize these after loading env ONCALL_RECORDS_APP_TOKEN = os.environ.get("ONCALL_RECORDS_APP_TOKEN") ONCALL_RECORDS_TABLE_ID = os.environ.get("ONCALL_RECORDS_TABLE_ID") # Jira config JIRA_USER = os.environ.get("JIRA_USER") JIRA_API_TOKEN = os.environ.get("JIRA_API_TOKEN") JIRA_BASE_URL = os.environ.get("JIRA_BASE_URL", "https://tidb.atlassian.net") # Bitable ONCALL_RECORDS_APP_TOKEN = os.environ.get("ONCALL_RECORDS_APP_TOKEN") ONCALL_RECORDS_TABLE_ID = os.environ.get("ONCALL_RECORDS_TABLE_ID") JIRA_BASE_URL = "https://tidb.atlassian.net" TCOC_COMPONENTS = ("BR", "Cloud DM", "Cloud Import", "TiCDC") ALERT_COMPONENTS = ("br", "import-controller", "tcidm-regional", "ticdc", "ticdc-kernel") ALERT_ENGINEER_LEADER_EXCLUSION = "623304991c09d2007014ce4f" # Scope mapping SCOPE_MAP = { "BR": "BR", "Cloud Import": "Import", "Cloud DM": "DM", "TiCDC": "CDC", "Dataservice": "Dataservice", "br": "BR", "import-controller": "Import", "tcidm-regional": "DM", "ticdc": "CDC", "ticdc-kernel": "CDC", } CLOSED_STATUSES = { "done", "closed", "resolved", "completed", "cancelled", "canceled", } def parse_jira_datetime(jira_date_str: str) -> int: """Parse Jira datetime string and return milliseconds timestamp""" if not jira_date_str: return 0 # Format: 2026-03-05T21:16:31.015+0800 try: # Remove timezone for simplicity and parse if '+' in jira_date_str: jira_date_str = jira_date_str.split('+')[0] elif 'Z' in jira_date_str: jira_date_str = jira_date_str.replace('Z', '') dt = datetime.strptime(jira_date_str, "%Y-%m-%dT%H:%M:%S.%f") return int(dt.timestamp() * 1000) except Exception: # Ignore parse errors return 0 def query_jira(jql: str, fields: str = "key,summary,status,priority,components,created,assignee,resolution") -> dict: """Query Jira API""" if not JIRA_USER or not JIRA_API_TOKEN: print("Error: JIRA_USER or JIRA_API_TOKEN not set") return {"issues": [], "total": 0} url = f"{JIRA_BASE_URL}/rest/api/3/search/jql" params = { "jql": jql, "maxResults": 500, "fields": fields } try: resp = requests.get( url, params=params, auth=(JIRA_USER, JIRA_API_TOKEN), timeout=30 ) data = resp.json() return { "issues": data.get("issues", []), "total": data.get("total", 0) } except Exception as e: print(f"Error querying Jira: {e}") return {"issues": [], "total": 0} def query_jira_issues_by_keys(keys: list) -> list: """Query Jira issues by key in chunks.""" all_issues = [] for i in range(0, len(keys), 100): chunk = keys[i:i + 100] quoted_keys = ",".join(f"'{key}'" for key in chunk) result = query_jira(f"issuekey IN ({quoted_keys})") all_issues.extend(result.get("issues", [])) return all_issues def get_open_tcoc_keys_from_bitable() -> list: """Return existing TCOC keys in Bitable that are not in a terminal status.""" records = list_bitable_records( ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, ["Category", "Link", "Status"] ) keys = [] for record in records: fields = record.get("fields", {}) if fields.get("Category") != "TCOC": continue link_field = fields.get("Link") or {} key = link_field.get("text", "") if isinstance(link_field, dict) else "" if not key: continue status = (fields.get("Status") or "").strip().lower() if status in CLOSED_STATUSES: continue keys.append(key) return keys def process_tcoc_issues(issues: list, date_str: str = None): """Process TCOC issues and add to Bitable""" print(f"\nProcessing {len(issues)} TCOC tickets...") # Get date for Oncall Cycle if date_str: year = int(date_str[:4]) month = int(date_str[4:6]) day = int(date_str[6:8]) target_date = date(year, month, day) else: target_date = date.today() - timedelta(days=1) # Calculate Oncall Cycle (last Friday) days_since_friday = (target_date.weekday() - 4) % 7 if days_since_friday == 0: days_since_friday = 7 oncall_cycle_date = target_date - timedelta(days=days_since_friday) oncall_cycle = oncall_cycle_date.strftime("%Y%m%d") # Get existing records (include Owner and Status for comparison) existing = get_bitable_records(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, ["Link", "Assignee", "Status"]) added = 0 updated = 0 for issue in issues: key = issue["key"] fields = issue["fields"] # Build unique key unique_key = key + "|" # Parse fields summary = fields.get("summary", "")[:200] status = fields.get("status", {}) status_name = status.get("name", "Unknown") if isinstance(status, dict) else "Unknown" priority = fields.get("priority", {}) priority_name = priority.get("name", "") if isinstance(priority, dict) else "" components = fields.get("components", []) component = components[0].get("name", "") if components else "" scope = SCOPE_MAP.get(component, component) created = fields.get("created", "") created_ts = parse_jira_datetime(created) # Get assignee (Owner) assignee = fields.get("assignee", {}) owner_name = assignee.get("displayName", "") if assignee else "" # Check if exists and needs update if unique_key in existing: record_id = existing[unique_key] # Get existing Owner and Status from Bitable from base import get_bitable_record existing_record = get_bitable_record(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record_id) existing_owner = existing_record.get("Assignee", "") if existing_record else "" existing_status = existing_record.get("Status", "") if existing_record else "" # Update if Owner or Status changed if existing_owner != owner_name or existing_status != status_name: update_fields = {} if existing_owner != owner_name: update_fields["Assignee"] = owner_name if existing_status != status_name: update_fields["Status"] = status_name if update_fields: from base import update_bitable_record if update_bitable_record(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record_id, update_fields): print(f"UPDATED: {key} (Owner: {existing_owner}->{owner_name}, Status: {existing_status}->{status_name})") updated += 1 else: print(f"UPDATE FAILED: {key}") continue print(f"SKIP: {key} already exists") continue # Build record record = { "Category": "TCOC", "Title": summary, "Link": {"text": key, "link": f"{JIRA_BASE_URL}/browse/{key}"}, "Scope": scope, "Status": status_name, "Priority": priority_name, "Oncall Cycle": oncall_cycle, "CreatedAt": created_ts, } if owner_name: record["Assignee"] = owner_name if add_to_bitable(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record): print(f"ADDED: {key}") added += 1 existing[unique_key] = True return {"added": added, "updated": updated} def query_tcoc(date_str: str = None) -> list: """Query TCOC tickets for a specific date""" print("\n=== Querying TCOC Tickets ===") # Calculate date if date_str: year = int(date_str[:4]) month = int(date_str[4:6]) day = int(date_str[6:8]) target_date = date(year, month, day) else: target_date = date.today() - timedelta(days=1) start_date = target_date.strftime("%Y-%m-%d") end_date = (target_date + timedelta(days=1)).strftime("%Y-%m-%d") components = ", ".join(f"'{component}'" for component in TCOC_COMPONENTS) jql = ( f"project=TCOC AND created>={start_date} AND created<{end_date} " f"AND component IN ({components})" ) result = query_jira(jql) issues = result.get("issues", []) print(f"Found {len(issues)} TCOC tickets for {start_date}") return issues def query_open_tcoc_for_status_sync() -> list: """Query open TCOC tickets already tracked in Bitable so status changes keep syncing.""" print("\n=== Querying Existing Open TCOC Tickets For Status Sync ===") open_keys = get_open_tcoc_keys_from_bitable() if not open_keys: print("Found 0 open TCOC tickets in Bitable") return [] print(f"Found {len(open_keys)} open TCOC tickets in Bitable") issues = query_jira_issues_by_keys(open_keys) print(f"Fetched {len(issues)} Jira issues for existing open TCOC tickets") return issues def query_alerts(date_str: str = None) -> list: """Query O11Y Critical Alert tickets for a specific date""" print("\n=== Querying O11Y Critical Alerts ===") # Calculate date if date_str: year = int(date_str[:4]) month = int(date_str[4:6]) day = int(date_str[6:8]) target_date = date(year, month, day) else: target_date = date.today() - timedelta(days=1) start_date = target_date.strftime("%Y-%m-%d") end_date = (target_date + timedelta(days=1)).strftime("%Y-%m-%d") components = ", ".join(ALERT_COMPONENTS) jql = ( f"project=O11Y AND priority=Critical AND component IN ({components}) " f'AND type=Task AND "Engineer Leader" != {ALERT_ENGINEER_LEADER_EXCLUSION} ' f"AND created>={start_date} AND created<{end_date}" ) result = query_jira(jql) issues = result.get("issues", []) print(f"Found {len(issues)} O11Y alerts for {start_date}") return issues def process_alert_issues(issues: list, date_str: str = None): """Process O11Y Alert issues and add to Bitable""" from base import get_bitable_record, update_bitable_record print(f"\nProcessing {len(issues)} O11Y alerts...") # Get date for Oncall Cycle if date_str: year = int(date_str[:4]) month = int(date_str[4:6]) day = int(date_str[6:8]) target_date = date(year, month, day) else: target_date = date.today() - timedelta(days=1) # Calculate Oncall Cycle (last Friday) days_since_friday = (target_date.weekday() - 4) % 7 if days_since_friday == 0: days_since_friday = 7 oncall_cycle_date = target_date - timedelta(days=days_since_friday) oncall_cycle = oncall_cycle_date.strftime("%Y%m%d") # Get existing records existing = get_bitable_records(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, ["Link"]) added = 0 updated = 0 for issue in issues: key = issue["key"] fields = issue["fields"] # Build unique key unique_key = key + "|" # Parse fields first (needed for update check) summary = fields.get("summary", "")[:200] status = fields.get("status", {}) status_name = status.get("name", "Unknown") if isinstance(status, dict) else "Unknown" assignee = fields.get("assignee", {}) owner_name = assignee.get("displayName", "") if assignee else "" # Check if exists and needs update if unique_key in existing: record_id = existing[unique_key] # Get existing Owner and Status from Bitable existing_record = get_bitable_record(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record_id) existing_owner = existing_record.get("Assignee", "") if existing_record else "" existing_status = existing_record.get("Status", "") if existing_record else "" # Update if Owner or Status changed if existing_owner != owner_name or existing_status != status_name: update_fields = {} if existing_owner != owner_name: update_fields["Assignee"] = owner_name if existing_status != status_name: update_fields["Status"] = status_name if update_fields: if update_bitable_record(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record_id, update_fields): print(f"UPDATED: {key} (Owner: {existing_owner}->{owner_name}, Status: {existing_status}->{status_name})") updated += 1 else: print(f"UPDATE FAILED: {key}") continue print(f"SKIP: {key} already exists") continue # Build record (for new records) components = fields.get("components", []) component = components[0].get("name", "") if components else "" scope = SCOPE_MAP.get(component, component) created = fields.get("created", "") created_ts = parse_jira_datetime(created) # Build record record = { "Category": "ALERT", "Title": summary, "Link": {"text": key, "link": f"{JIRA_BASE_URL}/browse/{key}"}, "Scope": scope, "Status": status_name, "Oncall Cycle": oncall_cycle, "CreatedAt": created_ts, } if owner_name: record["Assignee"] = owner_name if add_to_bitable(ONCALL_RECORDS_APP_TOKEN, ONCALL_RECORDS_TABLE_ID, record): print(f"ADDED: {key}") added += 1 existing[unique_key] = True return {"added": added, "updated": updated} def sync(date_str: str = None): """Main sync function""" print("\n" + "=" * 50) print("Jira Ticket Sync" + (f" - {date_str}" if date_str else "")) print("=" * 50) # Query TCOC tcoc_new_issues = query_tcoc(date_str) open_tcoc_issues = query_open_tcoc_for_status_sync() tcoc_by_key = {issue["key"]: issue for issue in tcoc_new_issues} for issue in open_tcoc_issues: tcoc_by_key[issue["key"]] = issue tcoc_issues_to_process = list(tcoc_by_key.values()) tcoc_added = process_tcoc_issues(tcoc_issues_to_process, date_str) # Query Alerts alert_issues = query_alerts(date_str) alert_added = process_alert_issues(alert_issues, date_str) print("\n=== SUMMARY ===") print(f"TCOC: {len(tcoc_new_issues)}") print(f"Alert: {len(alert_issues)}") print("=== END ===") return { "tcoc": len(tcoc_new_issues), "tcoc_added": tcoc_added.get("added", 0), "tcoc_updated": tcoc_added.get("updated", 0), "alert": len(alert_issues), "alert_added": alert_added.get("added", 0), "alert_updated": alert_added.get("updated", 0) } if __name__ == "__main__": date_arg = sys.argv[1] if len(sys.argv) > 1 else None sync(date_arg)