# -*- coding: utf-8 -*-
"""执行建库 + 建表
1) CREATE DATABASE rmsys（显式 utf8mb4）
2) 执行 db/schema.sql（EF Core 生成的 20 张表 + 索引 + 外键）
3) 校验：表数量、每张表的字符集、中文往返
只操作 rmsys 库，不触碰同服务器其他 20 个库。
"""
import re
import sys

import pymysql

HOST, PORT, USER, PWD = '192.168.20.66', 3306, 'tanghouhu', 'admin123#@!'
SCHEMA = '/data/www/files/rmsys-app/backend/db/schema.sql'

c = pymysql.connect(host=HOST, port=PORT, user=USER, password=PWD, charset='utf8mb4',
                    autocommit=True, connect_timeout=10)
cur = c.cursor()

# ---------- 1) 建库 ----------
print('=== [1/4] 建库 ===')
cur.execute("CREATE DATABASE IF NOT EXISTS rmsys "
            "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci")
cur.execute("SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME "
            "FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='rmsys'")
print('   rmsys 字符集:', cur.fetchone())
cur.execute("USE rmsys")

# ---------- 2) 执行 schema.sql ----------
print('=== [2/4] 执行 schema.sql ===')
sql = open(SCHEMA, encoding='utf-8').read()
# 去掉 BOM 与行注释，再按 ; 切分（本文件是纯 DDL，无字符串内分号）
sql = sql.lstrip('\ufeff')
lines = [ln for ln in sql.splitlines() if not ln.strip().startswith('--')]
body = '\n'.join(lines)
stmts = [s.strip() for s in body.split(';') if s.strip()]

done, failed = 0, []
for st in stmts:
    head = re.sub(r'\s+', ' ', st)[:60]
    try:
        cur.execute(st)
        done += 1
    except Exception as e:
        # 幂等：忽略「已存在」
        if e.args and e.args[0] in (1050, 1061, 1062, 1826):
            done += 1
            continue
        failed.append((head, str(e)[:160]))

print('   语句执行: 成功 %d / 共 %d' % (done, len(stmts)))
for h, e in failed:
    print('   ✗ %s ... -> %s' % (h, e))

# ---------- 3) 校验表与字符集 ----------
print('=== [3/4] 校验 ===')
cur.execute("SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA='rmsys' ORDER BY TABLE_NAME")
rows = cur.fetchall()
print('   表数量: %d' % len(rows))
bad = [(t, col) for t, col in rows if not str(col).startswith('utf8mb4')]
print('   非 utf8mb4 的表: %s' % (bad if bad else '无 ✓'))
for t, col in rows:
    print('     %-24s %s' % (t, col))

cur.execute("SELECT COUNT(*) FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA='rmsys'")
print('   索引条数:', cur.fetchone()[0])
cur.execute("SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE "
            "WHERE TABLE_SCHEMA='rmsys' AND REFERENCED_TABLE_NAME IS NOT NULL")
print('   外键条数:', cur.fetchone()[0])

# ---------- 4) 中文往返（写入→读出→比对→删除）----------
print('=== [4/4] 中文往返验证 ===')
zh_name = '张建国'
zh_post = '海东市发展和改革委员会党组成员、副主任'
zh_reason = '政治素质好，大局意识强，熟悉宏观经济工作。'
cur.execute("INSERT INTO depts(Code,Name,Duty,Leader,Sort,Enabled,CreatedAt) "
            "VALUES('_probe','_探针处室','职责','负责人',99,1,NOW())")
probe_dept = cur.lastrowid
cur.execute("INSERT INTO cadres(DeptId,Name,Sex,Birth,Nation,NativePlace,CurrentPost,"
            "AppointReason,CreatedAt,UpdatedAt) VALUES(%s,%s,'男','1976.03','汉族',"
            "'四川海东',%s,%s,NOW(),NOW())",
            (probe_dept, zh_name, zh_post, zh_reason))
probe_cadre = cur.lastrowid
cur.execute("SELECT Name,CurrentPost,AppointReason,CHAR_LENGTH(AppointReason),"
            "HEX(LEFT(Name,1)) FROM cadres WHERE Id=%s", (probe_cadre,))
got = cur.fetchone()
ok = got[0] == zh_name and got[1] == zh_post and got[2] == zh_reason
print('   写入 姓名=%s 职务=%s' % (zh_name, zh_post))
print('   读出 姓名=%s 职务=%s' % (got[0], got[1]))
print('   理由一致=%s 长度=%d 字 首字UTF8=%s' % (got[2] == zh_reason, got[3], got[4]))
print('   中文往返:', '完全一致 ✓ 无乱码' if ok else '不一致 ✗')
cur.execute("DELETE FROM cadres WHERE Id=%s", (probe_cadre,))
cur.execute("DELETE FROM depts WHERE Id=%s", (probe_dept,))
cur.execute("SELECT COUNT(*) FROM cadres")
print('   清理后 cadres 行数(应为0):', cur.fetchone()[0])

c.close()
print('SCHEMA_APPLY_DONE')