#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import json
import os
import re
import time
import urllib.request
import urllib.error

# ── 配置区（硬编码，不通过 key.json 管理）────────────────────
API_URL       = "http://47.97.121.32:8080/VOLERTECH/app/api/oauth/getmysql"
API_BASE_JDBC = "snowy_cloud_mysql_copy"
# ──────────────────────────────────────────────────────────────

SQL        = "SELECT ACCOUNT AS name, PHONE AS phone FROM SYS_USER WHERE DELETE_FLAG = 'NOT_DELETE' ORDER BY NAME"
CACHE_FILE = "/workspace/skills/sms-notifier/contacts_cache.json"


def execute_sql(sql: str) -> dict:
    payload = {"sql": sql, "basejdbc": API_BASE_JDBC}
    try:
        data = json.dumps(payload).encode('utf-8')
        req = urllib.request.Request(
            API_URL,
            data=data,
            headers={'Content-Type': 'application/json'},
            method='POST'
        )
        with urllib.request.urlopen(req, timeout=30) as resp:
            result = json.loads(resp.read().decode('utf-8'))
            return {"success": True, "data": result}
    except urllib.error.URLError as e:
        return {"success": False, "error": f"网络错误：{e.reason}"}
    except Exception as e:
        return {"success": False, "error": f"API 调用失败：{e}"}


def parse_rows(api_result: dict) -> list:
    if not api_result.get("success"):
        return []
    data = api_result.get("data", {})
    if isinstance(data, list):
        return data
    if isinstance(data, dict):
        for key in ["data", "result", "rows", "list", "records"]:
            if key in data and isinstance(data[key], list):
                return data[key]
        if "code" in data and data.get("code") in [200, "200", 0, "0"]:
            for key in ["data", "result", "rows", "list", "records"]:
                if key in data and isinstance(data[key], list):
                    return data[key]
    return []


def phone_status(v: str) -> str:
    s = str(v or '').strip()
    if not s:
        return 'empty'
    if re.fullmatch(r'1\d{10}', s):
        return 'plain_mobile'
    if re.fullmatch(r'[0-9a-fA-F]{16,64}', s):
        return 'encrypted_or_hashed'
    if '*' in s:
        return 'masked'
    return 'other'


def main():
    ts = int(time.time())
    api_result = execute_sql(SQL)
    if not api_result.get('success'):
        print(json.dumps({
            'status': 'error',
            'message': api_result.get('error', '联系人查询失败'),
            'sql': SQL,
            'timestamp': ts,
        }, ensure_ascii=False))
        return

    rows = parse_rows(api_result)
    contacts = []
    for row in rows:
        if not isinstance(row, dict):
            continue
        name = str(row.get('name') or row.get('ACCOUNT') or row.get('account') or '').strip()
        phone_raw = '' if row.get('phone', row.get('PHONE', row.get('mobile', row.get('MOBILE', '')))) is None else str(row.get('phone', row.get('PHONE', row.get('mobile', row.get('MOBILE', ''))))).strip()
        if not name:
            continue
        status = phone_status(phone_raw)
        contacts.append({
            'name': name,
            'phone': phone_raw,
            'phone_status': status,
            'sendable': status == 'plain_mobile'
        })

    os.makedirs(os.path.dirname(CACHE_FILE), exist_ok=True)
    with open(CACHE_FILE, 'w', encoding='utf-8') as f:
        json.dump([{'name': c['name'], 'phone': c['phone']} for c in contacts], f, ensure_ascii=False, indent=2)

    print(json.dumps({
        'status': 'need_contact_selection',
        'sql': SQL,
        'timestamp': ts,
        'count': len(contacts),
        'contacts': contacts
    }, ensure_ascii=False))


if __name__ == '__main__':
    main()