const fs = require('fs');
const turf = require('@turf/turf');
const wkx = require('wkx');
const Database = require('./db');
const moment = require('moment');


const pool = new Database({
    host: '192.168.100.30',
    database: 'real3d',
    user: 'postgres',
    password: 'postgis',
    port: 5432,
});


// 选址结果导出
async function readFile(inFilePath, outFilePath) {
    let typeMap = {
        "公园绿地": {
            name: `公园绿地`,
            one_level_name: "公园绿地",
            one_level_code: `1401`,
            table: `dj_gf`,
            field: `公园_地`
        },
        "防护绿地": {
            name: `防护绿地`,
            one_level_name: "防护绿地",
            one_level_code: `1402`,
            table: `dj_gf`,
            field: `公园_地`
        },
        "广场用地": {
            name: `广场用地`,
            one_level_name: "广场用地",
            one_level_code: `1403`,
            table: `dj_gf`,
            field: `公园_地`
        },
        "零售商业用地": {
            name: `零售商业用地`,
            one_level_name: "商业用地",
            one_level_code: `0901`,
            two_level_name: `零售商业用地`,
            two_level_code: `090101`,
            table: `dj_sf`,
            field: `集体楼`
        },
        // 待定
        "社会停车场用地": {
            name: `社会停车场用地`,
            one_level_name: "交通场站用地",
            one_level_code: `1208`,
            two_level_name: `社会停车场用地`,
            two_level_code: `120803`,
            table: `dj_gf`,
            field: `公园_地`
        },
        "零售商业用地": {
            name: `零售商业用地`,
            one_level_name: "商业用地",
            one_level_code: `0901`,
            two_level_name: `零售商业用地`,
            two_level_code: `090101`,
            table: `dj_sf`,
            field: `集体楼`
        },
        "商务金融混合旅馆混合零售商业用地": {
            name: `商务金融混合旅馆混合零售商业用地`,
            one_level_name: "商业用地",
            one_level_code: `0901`,
            table: `dj_sf`,
            field: `集体楼`
        },
        "零售商业混合旅馆用地": {
            name: `零售商业混合旅馆用地`,
            one_level_name: "商业用地",
            one_level_code: `0901`,
            table: `dj_sf`,
            field: `集体楼`
        },
        "二类城镇住宅混合零售商业用地": {
            name: `二类城镇住宅混合零售商业用地`,
            one_level_name: "城镇住宅用地",
            one_level_code: `0701`,
            table: `dj_zz`,
            field: `集体楼`
        }
    }


    try {
        // 同步读取GeoJSON文件
        const data = fs.readFileSync(inFilePath, 'utf8');
        // 解析GeoJSON数据
        const dataListJson = JSON.parse(data);

        // 连接到数据库，设置模式
        await pool.connect();
        await pool.setSchema('vector');

        // 转换为 GeoJSON 格式
        const geojsonFeatures = [];
        const areaMap = {};
        for (const item of dataListJson) {
            const ewktString = item.ewkt;
            const parseGeom = wkx.Geometry.parse(ewktString).toGeoJSON();
            const multiPolygon = turf.multiPolygon(parseGeom.coordinates);

            const tableInfo = typeMap[item.kzxxgydmc]
            const sqlQuery = `SELECT "${tableInfo.field}" FROM ${tableInfo.table} WHERE public.st_contains(geom, public.st_geomfromewkt('${ewktString}'))`;

            console.log(sqlQuery);
            const res = await pool.query(sqlQuery);
            // 获取查询结果中的第一个值
            const queryResult = res.rows.length > 0 ? res.rows[0][tableInfo.field] : 0;

            // const queryResult = 0;

            const timestamp = new Date().getTime();

            if (areaMap[item.kzxxgydmc]) {
                areaMap[item.kzxxgydmc] += item.ydmj;
            } else {
                areaMap[item.kzxxgydmc] = item.ydmj;
            }

            multiPolygon.properties = {
                dkbm: item.dkbm,//地块编码
                kzxxgyddm: item.kzxxgyddm,
                kzxxgydmc: item.kzxxgydmc,//地块名称类别
                ydmj: item.ydmj,//面积
                name: item.name,
                sjxzqmc: item.sjxzqmc,
                xjxzqmc: item.xjxzqmc,
                xzmc: item.xzmc,
                landPrice: (queryResult * item.ydmj / 10000).toFixed(2),
                bzdw: item.bzdw,
                time: moment(timestamp).format('YYYY-MM-DD HH:mm:ss')
            };
            geojsonFeatures.push(multiPolygon);
        }
        console.log(`面积：`);
        console.log(areaMap);

        // 构造 GeoJSON FeatureCollection 对象
        const geojson = turf.featureCollection(geojsonFeatures);
        // 将 GeoJSON 写入文件
        fs.writeFileSync(outFilePath, JSON.stringify(geojson, null, 2));

        console.log(`成功保存为 GeoJSON 文件：${outFilePath}`);
    } catch (err) {
        console.error('操作GeoJSON文件时出错:', err);
    }
}

readFile("./data/schedule/规划结果.json", "./data/schedule/规划结果.geojson")