# 读取 zhenos_kg 的三张表结构 + 既有样本，供生成符合约定的同步 SQL
# 只读，不写入
# 用法: python _design-test/kg-schema.py
import os, re, pymysql

ZF = r'e:\KF_7\ZhenOS-ZF'
env = {}
with open(os.path.join(ZF, '.env.local'), encoding='utf-8') as f:
    for ln in f:
        m = re.match(r'^\s*([A-Z_][A-Z0-9_]*)\s*=\s*(.*?)\s*$', ln)
        if m:
            env[m.group(1)] = m.group(2).strip().strip('"').strip("'")

conn = pymysql.connect(host='rm-0jlbgr2rv6dj3t6jngo.mysql.rds.aliyuncs.com', port=3306,
                       user=env['DB_USERNAME'], password=env['DB_PASSWORD'],
                       charset='utf8mb4', connect_timeout=12)
cur = conn.cursor()
cur.execute("USE zhenos_kg")

for t in ('kg_entities', 'kg_relations', 'dev_knowledge'):
    cur.execute("SHOW CREATE TABLE `%s`" % t)
    print('=' * 70)
    print(cur.fetchone()[1])

cur.execute("SELECT * FROM kg_entities WHERE entity_name='ZhenosSH:ruoyi-ui'")
cur.execute("SHOW COLUMNS FROM kg_entities")
cols = [c[0] for c in cur.fetchall()]
cur.execute("SELECT * FROM kg_entities WHERE entity_name='ZhenosSH:ruoyi-ui'")
row = cur.fetchone()
print('=' * 70)
print('样本实体 ZhenosSH:ruoyi-ui:')
for c, v in zip(cols, row):
    s = str(v)
    print('  %-20s %s' % (c, s[:300]))

cur.execute("SELECT * FROM kg_entities WHERE entity_type='ConfigFile' LIMIT 2")
print('-' * 70)
for r in cur.fetchall():
    print('  ConfigFile 样本:', dict(zip(cols, [str(x)[:80] for x in r])))

cur.execute("SHOW COLUMNS FROM kg_relations")
rcols = [c[0] for c in cur.fetchall()]
cur.execute("SELECT * FROM kg_relations WHERE source_name LIKE 'ZhenosSH:ruoyi-ui%%' LIMIT 4")
print('-' * 70)
print('ruoyi-ui 的关系样本:')
for r in cur.fetchall():
    print('  ', dict(zip(rcols, [str(x)[:60] for x in r])))

cur.execute("SHOW COLUMNS FROM dev_knowledge")
kcols = [c[0] for c in cur.fetchall()]
cur.execute("SELECT type,title,tags,entity_type,entity_name,module,priority,status,source FROM dev_knowledge WHERE type='pitfall' LIMIT 2")
print('-' * 70)
print('dev_knowledge pitfall 样本:')
for r in cur.fetchall():
    print('  ', dict(zip(['type','title','tags','entity_type','entity_name','module','priority','status','source'],
                         [str(x)[:90] for x in r])))
cur.execute("SELECT DISTINCT type FROM dev_knowledge")
print('  dev_knowledge 已有 type:', [r[0] for r in cur.fetchall()])

conn.close()