import pymysql
from pathlib import Path

planned = {
'ado_intg_system','ado_intg_connector','ado_intg_field_mapping','ado_intg_code_mapping','ado_intg_sync_checkpoint','ado_intg_event_raw',
'ado_md_customer','ado_md_supplier','ado_md_item','ado_md_work_center','ado_md_process_route','ado_md_process_route_step','ado_md_calendar',
'ado_s1_sales_order','ado_s1_sales_order_line','ado_s1_delivery_promise','ado_s1_delivery_change',
'ado_s2_mps_plan','ado_s2_work_order','ado_s2_work_order_op','ado_s2_capacity_snapshot',
'ado_s3_mrp_result','ado_s3_material_kit_status','ado_s3_supplier_delivery_perf',
'ado_s4_purchase_order','ado_s4_purchase_order_line','ado_s4_receipt','ado_s4_return',
'ado_s5_inventory_balance','ado_s5_inventory_txn','ado_s5_iqc_result',
'ado_s6_report_work','ado_s6_ipqc_result','ado_s6_equipment_oee',
'ado_s7_fg_inbound','ado_s7_fg_outbound','ado_s7_oqc_result','ado_s7_trace_serial',
'ado_s8_rule_model','ado_s8_rule_condition','ado_s8_rule_scope','ado_s8_alert_model','ado_s8_alert_channel','ado_s8_alert_record',
'ado_s8_incident','ado_s8_incident_dispatch','ado_s8_incident_action','ado_s8_incident_verify','ado_s8_rule_eval_log','ado_s8_rule_hit_fact',
'ado_s9_kpi_def','ado_s9_kpi_relation','ado_s9_kpi_page_binding','ado_s9_kpi_formula','ado_s9_kpi_value_l1_day','ado_s9_kpi_value_l2_day','ado_s9_kpi_value_l3_day',
'ado_s9_dashboard_snapshot','ado_s9_drilldown_cache','ado_s9_calc_task','ado_s9_calc_log','ado_s9_chatbi_semantic','ado_s9_chatbi_query_log',
'ado_mobile_kpi_snapshot','ado_mobile_sync_cursor','ado_mobile_alert_ack','ado_mobile_incident_action'
}

conn = pymysql.connect(host='123.60.180.165', port=3306, user='aidopremote', password='1234567890aiDOP#', charset='utf8mb4', autocommit=True)
try:
    with conn.cursor() as cur:
        cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='aidopdev' AND table_name LIKE 'ado\\_%'")
        existing = {r[0] for r in cur.fetchall()}
finally:
    conn.close()

extra = sorted(existing - planned)
missing = sorted(planned - existing)
matched = sorted(existing & planned)

out = Path(r'd:/Projects/Ai-DOP/SourceCode/ai-dop-platform/docs/db/aidopdev_新增与历史表差异清单.md')
with out.open('w', encoding='utf-8') as f:
    f.write('# aidopdev 新增与历史表差异清单\n\n')
    f.write(f'- 方案目标表数: {len(planned)}\n')
    f.write(f'- 远端 ado_* 实际表数: {len(existing)}\n')
    f.write(f'- 命中方案表数: {len(matched)}\n')
    f.write(f'- 额外历史表数: {len(extra)}\n')
    f.write(f'- 缺失方案表数: {len(missing)}\n\n')

    f.write('## 额外历史表（非本次方案）\n')
    if extra:
        for t in extra:
            f.write(f'- `{t}`\n')
    else:
        f.write('- 无\n')

    f.write('\n## 缺失方案表\n')
    if missing:
        for t in missing:
            f.write(f'- `{t}`\n')
    else:
        f.write('- 无\n')

    f.write('\n## 已命中方案表\n')
    for t in matched:
        f.write(f'- `{t}`\n')

print(out)
print('extra=', len(extra), 'missing=', len(missing), 'matched=', len(matched))