#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
从 GeoPackage 文件读取所有表，生成达梦数据库的建表 SQL 和插入数据 SQL。
几何列使用达梦空间数据库的 ST_GeomFromText 函数插入 WKT 格式数据。
坐标系: EPSG:3857
"""

import sqlite3
import struct
import os

GPKG_PATH = r'D:\GIS数据.gpkg'
OUTPUT_DIR = r'D:\dm_sql'
SRID = 3857

# SQLite 类型 -> 达梦类型映射
TYPE_MAP = {
    'INTEGER': 'INTEGER',
    'TEXT': 'VARCHAR2',
    'REAL': 'DOUBLE',
    'MEDIUMINT': 'INTEGER',
    'DATETIME': 'TIMESTAMP',
    'BLOB': 'BLOB',
    'POINT': 'ST_GEOMETRY',
    'MULTILINESTRING': 'ST_GEOMETRY',
    'MULTIPOLYGON': 'ST_GEOMETRY',
    'POLYGON': 'ST_GEOMETRY',
    'LINESTRING': 'ST_GEOMETRY',
    'MULTIPOINT': 'ST_GEOMETRY',
    'GEOMETRY': 'ST_GEOMETRY',
}

# 几何类型名称集合
GEOM_TYPES = {'POINT', 'MULTILINESTRING', 'MULTIPOLYGON', 'POLYGON', 'LINESTRING', 'MULTIPOINT', 'GEOMETRY'}


def parse_text_length(col_type):
    """从 TEXT(255) 中提取长度"""
    if col_type.startswith('TEXT'):
        if '(' in col_type:
            length = col_type.split('(')[1].rstrip(')')
            return int(length)
        return 4000
    return None


def map_dm_type(col_name, col_type, geom_type_name=None):
    """将 SQLite 列类型映射为达梦类型"""
    upper_type = col_type.upper().strip()

    # 几何列
    if col_name == 'geom':
        return 'SYSGEO2.ST_GEOMETRY'

    # TEXT 类型
    if upper_type.startswith('TEXT'):
        length = parse_text_length(upper_type)
        if length:
            return f'VARCHAR2({length})'
        return 'VARCHAR2(4000)'

    # 其他类型
    base_type = upper_type.split('(')[0].strip()
    return TYPE_MAP.get(base_type, 'VARCHAR2(255)')


def gpkg_geom_to_wkt(blob):
    """将 GeoPackage 二进制几何数据转换为 WKT 格式"""
    if blob is None:
        return None

    try:
        # GeoPackage 几何二进制格式:
        # 前 2 字节: magic number 'GP' (0x4750)
        # 第 3 字节: version
        # 第 4 字节: flags
        # 第 5-8 字节: srs_id (int32)
        # 然后是可选的 envelope，最后是标准 WKB

        if len(blob) < 8:
            return None

        magic = blob[0:2]
        if magic != b'GP':
            return None

        flags = blob[3]
        byte_order = (flags >> 0) & 0x01  # 0=big endian, 1=little endian
        envelope_type = (flags >> 1) & 0x07
        empty_flag = (flags >> 4) & 0x01

        if empty_flag:
            return None

        # 计算 envelope 大小
        envelope_sizes = {0: 0, 1: 32, 2: 48, 3: 48, 4: 64}
        envelope_size = envelope_sizes.get(envelope_type, 0)

        # WKB 数据起始位置
        wkb_offset = 8 + envelope_size
        wkb_data = blob[wkb_offset:]

        if len(wkb_data) < 5:
            return None

        return wkb_to_wkt(wkb_data)
    except Exception as e:
        return None


def read_double(data, offset, bo):
    fmt = '<d' if bo == 1 else '>d'
    return struct.unpack_from(fmt, data, offset)[0], offset + 8


def read_uint32(data, offset, bo):
    fmt = '<I' if bo == 1 else '>I'
    return struct.unpack_from(fmt, data, offset)[0], offset + 4


def wkb_to_wkt(wkb):
    """将 WKB 二进制数据转换为 WKT 文本"""
    if not wkb or len(wkb) < 5:
        return None

    try:
        result, _ = parse_wkb_geometry(wkb, 0)
        return result
    except Exception:
        return None


def parse_wkb_geometry(data, offset):
    """递归解析 WKB 几何体"""
    bo = data[offset]  # byte order: 0=big, 1=little
    offset += 1

    geom_type, offset = read_uint32(data, offset, bo)

    if geom_type == 1:  # Point
        x, offset = read_double(data, offset, bo)
        y, offset = read_double(data, offset, bo)
        return f'POINT ({x} {y})', offset
    elif geom_type == 2:  # LineString
        num_points, offset = read_uint32(data, offset, bo)
        coords = []
        for _ in range(num_points):
            x, offset = read_double(data, offset, bo)
            y, offset = read_double(data, offset, bo)
            coords.append(f'{x} {y}')
        return f'LINESTRING ({", ".join(coords)})', offset
    elif geom_type == 3:  # Polygon
        num_rings, offset = read_uint32(data, offset, bo)
        rings = []
        for _ in range(num_rings):
            num_points, offset = read_uint32(data, offset, bo)
            coords = []
            for _ in range(num_points):
                x, offset = read_double(data, offset, bo)
                y, offset = read_double(data, offset, bo)
                coords.append(f'{x} {y}')
            rings.append(f'({", ".join(coords)})')
        return f'POLYGON ({", ".join(rings)})', offset
    elif geom_type == 4:  # MultiPoint
        num_geoms, offset = read_uint32(data, offset, bo)
        points = []
        for _ in range(num_geoms):
            wkt, offset = parse_wkb_geometry(data, offset)
            # 从 "POINT (x y)" 中提取 "(x y)"
            coord = wkt.replace('POINT ', '')
            points.append(coord)
        return f'MULTIPOINT ({", ".join(points)})', offset
    elif geom_type == 5:  # MultiLineString
        num_geoms, offset = read_uint32(data, offset, bo)
        lines = []
        for _ in range(num_geoms):
            wkt, offset = parse_wkb_geometry(data, offset)
            # 从 "LINESTRING (...)" 中提取 "(...)"
            coord = wkt.replace('LINESTRING ', '')
            lines.append(coord)
        return f'MULTILINESTRING ({", ".join(lines)})', offset
    elif geom_type == 6:  # MultiPolygon
        num_geoms, offset = read_uint32(data, offset, bo)
        polys = []
        for _ in range(num_geoms):
            wkt, offset = parse_wkb_geometry(data, offset)
            coord = wkt.replace('POLYGON ', '')
            polys.append(coord)
        return f'MULTIPOLYGON ({", ".join(polys)})', offset
    else:
        raise ValueError(f'Unsupported geometry type: {geom_type}')


def escape_sql_value(val):
    """转义 SQL 字符串值"""
    if val is None:
        return 'NULL'
    if isinstance(val, (int, float)):
        return str(val)
    if isinstance(val, bytes):
        return 'NULL'  # BLOB 类型跳过
    # 字符串转义
    s = str(val).replace("'", "''")
    return f"'{s}'"


def generate_create_table_sql(table_name, columns, geom_info):
    """生成达梦建表 SQL"""
    lines = []
    lines.append(f'-- 表: {table_name}')
    lines.append(f'DROP TABLE IF EXISTS "{table_name}";')
    lines.append(f'CREATE TABLE "{table_name}" (')

    col_defs = []
    for col in columns:
        cid, name, col_type, notnull, default_val, pk = col
        dm_type = map_dm_type(name, col_type, geom_info.get('geometry_type_name') if geom_info else None)

        col_def = f'    "{name}" {dm_type}'
        if pk:
            col_def += ' PRIMARY KEY'
        if notnull and not pk:
            col_def += ' NOT NULL'
        col_defs.append(col_def)

    lines.append(',\n'.join(col_defs))
    lines.append(');')
    lines.append('')
    return '\n'.join(lines)


def generate_insert_sql(table_name, columns, rows, geom_col_index):
    """生成达梦插入数据 SQL"""
    if not rows:
        return f'-- 表 {table_name} 无数据\n'

    col_names = [col[1] for col in columns]
    sqls = []
    sqls.append(f'-- 表 {table_name} 数据插入 (共 {len(rows)} 条)')
    sqls.append('')

    for row in rows:
        values = []
        for i, val in enumerate(row):
            if i == geom_col_index:
                # 几何列：转换为 WKT 并使用 ST_GeomFromText
                if val is not None:
                    wkt = gpkg_geom_to_wkt(val)
                    if wkt:
                        values.append(f"SYSGEO2.DMGEO2.ST_GeomFromText('{wkt}', {SRID})")
                    else:
                        values.append('NULL')
                else:
                    values.append('NULL')
            else:
                values.append(escape_sql_value(val))

        col_list = ', '.join([f'"{c}"' for c in col_names])
        val_list = ', '.join(values)
        sqls.append(f'INSERT INTO "{table_name}" ({col_list}) VALUES ({val_list});')

    sqls.append('')
    sqls.append('COMMIT;')
    sqls.append('')
    return '\n'.join(sqls)


def main():
    os.makedirs(OUTPUT_DIR, exist_ok=True)

    conn = sqlite3.connect(GPKG_PATH)
    cursor = conn.cursor()

    # 获取所有业务表
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Gas%' ORDER BY name")
    tables = [t[0] for t in cursor.fetchall()]

    # 获取几何列信息
    cursor.execute('SELECT table_name, column_name, geometry_type_name, srs_id FROM gpkg_geometry_columns')
    geom_info_map = {}
    for row in cursor.fetchall():
        geom_info_map[row[0]] = {
            'column_name': row[1],
            'geometry_type_name': row[2],
            'srs_id': row[3]
        }

    # 生成总的建表 SQL 文件
    all_create_sqls = []
    all_create_sqls.append('-- ============================================')
    all_create_sqls.append('-- 浦江燃气 GeoPackage -> 达梦空间数据库')
    all_create_sqls.append('-- 建表 SQL (所有表)')
    all_create_sqls.append(f'-- 坐标系: EPSG:{SRID}')
    all_create_sqls.append('-- ============================================')
    all_create_sqls.append('')

    print(f'共发现 {len(tables)} 个业务表')

    for table_name in tables:
        print(f'处理表: {table_name}')

        # 获取列信息
        cursor.execute(f'PRAGMA table_info("{table_name}")')
        columns = cursor.fetchall()

        geom_info = geom_info_map.get(table_name)

        # 生成建表 SQL
        create_sql = generate_create_table_sql(table_name, columns, geom_info)
        all_create_sqls.append(create_sql)

        # 获取数据
        cursor.execute(f'SELECT COUNT(*) FROM "{table_name}"')
        count = cursor.fetchone()[0]

        # 找到几何列索引
        geom_col_index = -1
        for i, col in enumerate(columns):
            if col[1] == 'geom':
                geom_col_index = i
                break

        if count > 0:
            cursor.execute(f'SELECT * FROM "{table_name}"')
            rows = cursor.fetchall()

            insert_sql = generate_insert_sql(table_name, columns, rows, geom_col_index)

            # 每个表的插入数据单独一个文件
            insert_file = os.path.join(OUTPUT_DIR, f'{table_name}_insert.sql')
            with open(insert_file, 'w', encoding='utf-8') as f:
                f.write(insert_sql)
            print(f'  -> 生成插入 SQL: {insert_file} ({count} 条数据)')
        else:
            print(f'  -> 无数据，跳过插入 SQL')

    # 写入建表 SQL 文件
    create_file = os.path.join(OUTPUT_DIR, '00_create_tables.sql')
    with open(create_file, 'w', encoding='utf-8') as f:
        f.write('\n'.join(all_create_sqls))
    print(f'\n建表 SQL 已生成: {create_file}')

    conn.close()
    print('完成!')


if __name__ == '__main__':
    main()