#!/usr/bin/env python3
"""
WGS84 → GCJ02（高德坐标系）坐标转换脚本
目标表: garden_gis_road
转换字段: starting_latitude/longitude, end_latitude/longitude, gis_polygon_coords

使用方法:
    python3 convert_gis_road_coords.py

前提: pip install pymysql
"""
import pymysql
import math
import re

# ========== 数据库配置 ==========
DB_CONFIG = {
    'host': '172.17.16.15',
    'port': 3306,
    'user': 'root',
    'password': 'mysql2025!',
    'database': 'urban_ops_agent',
    'charset': 'utf8mb4',
}

# ========== WGS84 → GCJ02 算法 ==========
PI = math.pi
A = 6378245.0          # 长半轴
EE = 0.00669342162296594323  # 偏心率平方


def wgs84_to_gcj02(lng, lat):
    """WGS84 转 GCJ02（高德坐标系）"""
    if lng < 72.004 or lng > 137.8347 or lat < 0.8293 or lat > 55.8271:
        return lng, lat

    x, y = lng - 105.0, lat - 35.0

    dlat = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * math.sqrt(abs(x))
    dlat += (20.0 * math.sin(6.0 * x * PI) + 20.0 * math.sin(2.0 * x * PI)) * 2.0 / 3.0
    dlat += (20.0 * math.sin(y * PI) + 40.0 * math.sin(y / 3.0 * PI)) * 2.0 / 3.0
    dlat += (160.0 * math.sin(y / 12.0 * PI) + 320.0 * math.sin(y * PI / 30.0)) * 2.0 / 3.0

    dlng = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * math.sqrt(abs(x))
    dlng += (20.0 * math.sin(6.0 * x * PI) + 20.0 * math.sin(2.0 * x * PI)) * 2.0 / 3.0
    dlng += (20.0 * math.sin(x * PI) + 40.0 * math.sin(x / 3.0 * PI)) * 2.0 / 3.0
    dlng += (150.0 * math.sin(x / 12.0 * PI) + 300.0 * math.sin(x / 30.0 * PI)) * 2.0 / 3.0

    rad_lat = lat / 180.0 * PI
    magic = math.sin(rad_lat)
    magic = 1 - EE * magic * magic
    sqrt_magic = math.sqrt(magic)

    dlat = (dlat * 180.0) / ((A * (1 - EE)) / (magic * sqrt_magic) * PI)
    dlng = (dlng * 180.0) / (A / sqrt_magic * math.cos(rad_lat) * PI)

    return lng + dlng, lat + dlat


def transform_wkt(wkt):
    """转换 WKT 字符串中的所有坐标对（支持 POLYGON / MULTIPOLYGON）"""
    if not wkt or not wkt.strip():
        return wkt

    def convert_pair(m):
        nlng, nlat = wgs84_to_gcj02(float(m.group(1)), float(m.group(2)))
        return f"{nlng:.6f} {nlat:.6f}"

    return re.sub(r'(\d+\.?\d*)\s+(\d+\.?\d*)', convert_pair, wkt)


def main():
    conn = pymysql.connect(**DB_CONFIG)
    cur = conn.cursor()

    # 1. 转换起点/终点坐标（如果有的话）
    cur.execute(
        "SELECT id, starting_latitude, starting_longitude, end_latitude, end_longitude "
        "FROM garden_gis_road "
        "WHERE (starting_latitude IS NOT NULL AND starting_latitude != '') "
        "   OR (end_latitude IS NOT NULL AND end_latitude != '')"
    )
    rows = cur.fetchall()
    updated = 0
    for row in rows:
        id_, slat, slng, elat, elng = row
        updates = []
        params = []

        if slat and slng and slat.strip():
            try:
                nlng, nlat = wgs84_to_gcj02(float(slng), float(slat))
                updates.append('starting_longitude = %s')
                updates.append('starting_latitude = %s')
                params.append(str(round(nlng, 6)))
                params.append(str(round(nlat, 6)))
            except ValueError:
                pass

        if elat and elng and elat.strip():
            try:
                nlng, nlat = wgs84_to_gcj02(float(elng), float(elat))
                updates.append('end_longitude = %s')
                updates.append('end_latitude = %s')
                params.append(str(round(nlng, 6)))
                params.append(str(round(nlat, 6)))
            except ValueError:
                pass

        if updates:
            sql = 'UPDATE garden_gis_road SET ' + ', '.join(updates) + ' WHERE id = %s'
            params.append(id_)
            cur.execute(sql, params)
            updated += 1

    conn.commit()
    print(f'起点/终点坐标: 转换 {updated} 条')

    # 2. 转换围栏坐标
    cur.execute(
        "SELECT COUNT(*) FROM garden_gis_road "
        "WHERE gis_polygon_coords IS NOT NULL AND gis_polygon_coords != ''"
    )
    total = cur.fetchone()[0]
    print(f'围栏坐标记录: {total} 条')

    batch_size = 500
    updated = 0
    for offset in range(0, total, batch_size):
        cur.execute(
            "SELECT id, gis_polygon_coords FROM garden_gis_road "
            "WHERE gis_polygon_coords IS NOT NULL AND gis_polygon_coords != '' "
            "LIMIT %s OFFSET %s",
            (batch_size, offset)
        )
        for id_, wkt in cur.fetchall():
            new_wkt = transform_wkt(wkt)
            if new_wkt != wkt:
                cur.execute(
                    "UPDATE garden_gis_road SET gis_polygon_coords = %s WHERE id = %s",
                    (new_wkt, id_)
                )
                updated += 1
        conn.commit()
        print(f'  进度: {min(offset + batch_size, total)}/{total}, 已更新: {updated}')

    print(f'围栏坐标: 转换 {updated} 条')

    # 3. 验证
    cur.execute(
        "SELECT COUNT(*) FROM garden_gis_road "
        "WHERE gis_polygon_coords IS NOT NULL AND gis_polygon_coords != ''"
    )
    total = cur.fetchone()[0]
    unconverted = 0
    cur.execute(
        "SELECT id, gis_polygon_coords FROM garden_gis_road "
        "WHERE gis_polygon_coords IS NOT NULL AND gis_polygon_coords != ''"
    )
    for id_, wkt in cur.fetchall():
        m = re.search(r'(\d+\.?\d*)\s+(\d+\.?\d*)', wkt)
        if m:
            wlng, wlat = float(m.group(1)), float(m.group(2))
            nlng, nlat = wgs84_to_gcj02(wlng, wlat)
            if abs(wlng - nlng) < 0.00001:
                unconverted += 1

    print(f'\n验证: {unconverted}/{total} 条未转换')
    if unconverted == 0:
        print('所有坐标已成功转换为 GCJ02（高德坐标系）')

    cur.close()
    conn.close()


if __name__ == '__main__':
    main()