from qgis._core import QgsCoordinateTransformContext
from qgis.core import (
    QgsVectorLayer,
    QgsDataSourceUri,
    QgsVectorFileWriter,
    QgsApplication
)
from PyQt5.QtSql import QSqlDatabase, QSqlQuery

# 初始化 QGIS 应用程序（如果在外部运行脚本需要添加）
qgs = QgsApplication([], False)
qgs.initQgis()

# 1. 将 SHP 文件导入到 Spatialite
shp_file_path = "D:\\gisdata\\hainanShp\\building4326\\Building.shp"  # 输入的 SHP 文件路径
spatialite_db_path = "D:\\temp\\output.sqlite"  # Spatialite 数据库路径
table_name = "building_table"  # 导入后的表名
output_shp_path = "D:\\gisdata\\hainanShp\\building4326\\test\\sqlliteexport.shp"  # 输出的 SHP 文件路径
export_layer_name = table_name

# 加载 SHP 文件
shp_layer = QgsVectorLayer(shp_file_path, "shp_layer", "ogr")
if not shp_layer.isValid():
    print("Failed to load SHP file.")
    exit()

# 获取 SHP 文件的 CRS（坐标参考系）
shp_crs = shp_layer.crs().authid()

# 构造连接到 Spatialite 数据库的 URI
uri = QgsDataSourceUri()
uri.setDatabase(spatialite_db_path)

# 将 SHP 文件写入 Spatialite 数据库
options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = "SQLite"
options.layerName = table_name
options.fileEncoding = "UTF-8"
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
# 将QgsVectorLayer导入到spatialite并指定表名
result = QgsVectorFileWriter.writeAsVectorFormatV2(shp_layer, spatialite_db_path, QgsCoordinateTransformContext(), options)
if result[0] != QgsVectorFileWriter.NoError:
    print("Failed to import SHP file to Spatialite.")
    exit()

print(f"SHP file imported to Spatialite as table: {table_name}")

# 2. 执行 SQL 语句
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(spatialite_db_path)
if not db.open():
    print("Failed to open Spatialite database.")
    exit()

query = QSqlQuery(db)
sql = f"""
-- 在这里输入你的 SQL 语句
SELECT * FROM {table_name};
"""
if not query.exec(sql):
    print("SQL execution failed:", query.lastError().text())
    exit()

print("SQL execution completed.")

# 3. 将数据导出为本地 SHP 文件

# 加载处理后的表
processed_layer = QgsVectorLayer(f"{spatialite_db_path}|layername={export_layer_name}", "processed_layer", "ogr")
if not processed_layer.isValid():
    print("Failed to load processed layer.")
    exit()

# 导出为 SHP 文件
export_options = QgsVectorFileWriter.SaveVectorOptions()
export_options.driverName = "ESRI Shapefile"
export_options.fileEncoding = "UTF-8"

result = QgsVectorFileWriter.writeAsVectorFormatV2(processed_layer, output_shp_path, QgsCoordinateTransformContext(), export_options)
if result[0] != QgsVectorFileWriter.NoError:
    print("Failed to export processed layer to SHP file.")
    exit()

print(f"Processed layer exported to SHP file: {output_shp_path}")

# 释放资源
db.close()
qgs.exitQgis()