"""
批量检查所有 rainfall 模板的图层，看哪些动态图层需要显示、表是否存在
"""
import zipfile, re, os, psycopg2

TEMPLATE_DIR = r"F:\project\xian\xian_algorithm_new\app\data\template\rainfall"

# 收集所有非GPKG动态图层的表名
all_dynamic = set()
for fname in sorted(os.listdir(TEMPLATE_DIR)):
    if not fname.endswith('.qgz') or fname.startswith('tmp'):
        continue
    path = os.path.join(TEMPLATE_DIR, fname)
    z = zipfile.ZipFile(path)
    qgs_name = [n for n in z.namelist() if n.endswith('.qgs')][0]
    content = z.read(qgs_name).decode('utf-8')
    
    maplayer_re = re.compile(r'<maplayer[^>]*>(.*?)</maplayer>', re.DOTALL)
    for m in maplayer_re.finditer(content):
        block = m.group(1)
        name_m = re.search(r'<layername>([^<]+)</layername>', block)
        provider_m = re.search(r'<provider[^>]*>(\w+)</provider>', block)
        ds_m = re.search(r'<datasource>(.*?)</datasource>', block, re.DOTALL)
        
        provider = provider_m.group(1) if provider_m else '?'
        if provider != 'postgres':
            continue
        
        ds = ds_m.group(1).strip() if ds_m else ''
        table_m = re.search(r'table="(\w+)"\."(\w+)"', ds)
        layer_name = name_m.group(1) if name_m else '?'
        
        if table_m:
            schema = table_m.group(1)
            table = table_m.group(2)
            key = f"{schema}.{table}"
            all_dynamic.add((layer_name, key))

# 已知的GPKG静态层（会被替换为ogr）
static_tables = {
    'base.rivers', 'base.river', 'base.sx', 'base.sx_capital',
    'base.sx_street', 'base.sx_xa_county', 'base.sx_xa_county_boundary',
    'base.sx_zb_county_boundary', 'base.sx_zb_city', 'base.sx_zb_county',
    'base.active_fault', 'base.traffic_expressway', 'base.traffic_provincial',
    'base.traffic_railway', 'base.traffic_township', 'base.traffic_trunk_line',
}

# DB检查
c = psycopg2.connect(host='47.92.216.173', port=7654, user='postgres', password='zhangsan', database='xian_new')
c.autocommit = True
cur = c.cursor()

print(f"{'图层名':20s} {'原表':35s} {'qgis中存在':12s} {'行数':>8s}")
print("-" * 80)

for layer_name, table_key in sorted(all_dynamic):
    if table_key in static_tables:
        continue  # 会被GPKG替换，跳过
    
    schema, table = table_key.split('.', 1)
    try:
        cur.execute(f'SELECT count(*) FROM qgis.{table}')
        count = cur.fetchone()[0]
        exists = "YES"
    except:
        exists = "NO"
        count = 0
    
    marker = " !!!" if exists == "NO" else ""
    print(f"{layer_name:20s} {table_key:35s} {exists:12s} {count:>8,d}{marker}")

c.close()