#!/usr/bin/env python3
"""Read-only source/central/API comparison. Credentials stay in local config."""
import json
from datetime import datetime
from decimal import Decimal
from pathlib import Path
from urllib.request import urlopen
from zoneinfo import ZoneInfo

import pymysql


ROOT = Path('/Users/lqt/work/zhct/zhctprompt')
OUTPUT = Path(__file__).resolve().parent
DAY = '2026-10-20'


def encode(value):
    if isinstance(value, Decimal):
        return str(value)
    if hasattr(value, 'isoformat'):
        return value.isoformat(sep=' ') if isinstance(value, datetime) else value.isoformat()
    raise TypeError(type(value).__name__)


def connect(config, database=None):
    connection = pymysql.connect(
        host=config.get('external_host') or config['host'],
        port=int(config.get('port') or 3306),
        user=config['username'], password=config['password'],
        database=database or config['database'], charset='utf8mb4',
        connect_timeout=6, read_timeout=35, write_timeout=10,
        cursorclass=pymysql.cursors.DictCursor,
    )
    with connection.cursor() as cursor:
        cursor.execute('START TRANSACTION READ ONLY')
    return connection


def read_api():
    with urlopen('https://zhct.yyangpt.cn/api/dataCenterDashboard', timeout=30) as response:
        return json.load(response)['data']


def main():
    catalog = json.loads((ROOT / 'config/local/zhct-db-credentials.json').read_text())
    configs = [c for c in catalog['connections'] if c['role'] == 'main']
    api = read_api()
    date_from, date_to = api['filters']['date_from'], api['filters']['date_to']
    source_rows = []
    for config in configs:
        code = config['config_dir'].split('/')[-1]
        result = {'project_code': code, 'project_name': config['project_name']}
        connection = None
        try:
            connection = connect(config)
            with connection.cursor() as cursor:
                cursor.execute('SELECT COUNT(*) AS user_count FROM ydy_staff')
                result.update(cursor.fetchone())
                cursor.execute('SELECT COUNT(*) AS order_count, COALESCE(SUM(total_price),0) AS order_amount FROM ydy_meal_order WHERE meal_date BETWEEN %s AND %s', (date_from, date_to))
                result.update(cursor.fetchone())
                cursor.execute('SHOW COLUMNS FROM ydy_meal_order')
                columns = {r['Field'] for r in cursor.fetchall()}
                identity = next((c for c in ('staff_uuid', 'user_id', 'user_uuid') if c in columns), None)
                dau = f"COUNT(DISTINCT NULLIF(NULLIF(CAST(`{identity}` AS CHAR), ''), '0'))" if identity else '0'
                cursor.execute(f'SELECT COUNT(*) AS order_count, COALESCE(SUM(total_price),0) AS order_amount, {dau} AS active_user_count FROM ydy_meal_order WHERE meal_date=%s', (DAY,))
                result['today'] = cursor.fetchone()
                result['identity_column'] = identity
                result['status'] = 'success'
        except Exception as error:
            result['status'] = 'failed'
            result['error_code'] = error.args[0] if error.args else type(error).__name__
        finally:
            if connection:
                connection.rollback()
                connection.close()
        source_rows.append(result)
        print(json.dumps(result, ensure_ascii=False, default=encode), flush=True)

    central_config = next(c for c in configs if c['config_dir'] == 'zhct')
    central = connect(central_config, 'data_center')
    try:
        with central.cursor() as cursor:
            cursor.execute('SELECT project_code, sync_status, last_success_at, last_error FROM dc_project ORDER BY project_code')
            projects = cursor.fetchall()
            cursor.execute('SELECT id, run_type, date_from, date_to, status, project_total, project_success, project_failed, started_at, finished_at FROM dc_sync_run ORDER BY id DESC LIMIT 4')
            runs = cursor.fetchall()
            cursor.execute('SELECT project_code, SUM(order_count) AS order_count, SUM(order_amount) AS order_amount FROM dc_daily_meal_stats WHERE stat_date BETWEEN %s AND %s GROUP BY project_code', (date_from, date_to))
            facts = cursor.fetchall()
            cursor.execute('SELECT project_code, SUM(order_count) AS order_count, SUM(order_amount) AS order_amount, SUM(active_user_count) AS active_user_count FROM dc_daily_project_stats WHERE stat_date=%s GROUP BY project_code', (DAY,))
            daily = cursor.fetchall()
    finally:
        central.rollback()
        central.close()

    report = {
        'checked_at': datetime.now(ZoneInfo('Asia/Shanghai')),
        'date_range': [date_from, date_to],
        'metric': 'COUNT(*) / SUM(total_price); no payment/order/deletion status filters',
        'source_projects': source_rows,
        'central_projects': projects, 'central_runs': runs,
        'central_facts': facts, 'central_today': daily,
        'api': read_api(),
    }
    (OUTPUT / 'comparison.json').write_text(json.dumps(report, ensure_ascii=False, indent=2, default=encode))
    print('Saved comparison.json', flush=True)


if __name__ == '__main__':
    main()