package com.product.patch.service;

import com.google.common.collect.Lists;
import com.product.common.lang.StringUtils;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.transfer.Transactional;
import com.product.patch.service.idel.ISyncTableStructureService;
import com.product.tool.table.enums.FieldType;
import com.product.util.BaseUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 实现功能：
 *
 * @author 作者[夜丶光]
 * @version 1.0.00  2024-10-16 16:39
 */
@Service
public class SyncTableStructureService extends AbstractBaseService implements ISyncTableStructureService {
    @Autowired
    private BaseDao baseDao;

    /**
     * 查询获取出数据库表结构字段和缓存字段表字段存在差异的表
     * @return
     */
    @Override
    public List<String> listDiffTable() {
        StringBuilder sql = new StringBuilder(128);
        sql.append("\nWITH t1 AS (");
        sql.append("\n	SELECT t.table_name,f.field_name");
        sql.append("\n	FROM product_sys_datamodel_field f");
        sql.append("\n	INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
        sql.append("\n),t2 AS (");
        sql.append("\n	SELECT table_name,column_name field_name");
        sql.append("\n	FROM information_schema.`COLUMNS`");
        sql.append("\n	WHERE table_schema='product_db_v3.0.0'");
        sql.append("\n)");
        sql.append("\nSELECT DISTINCT table_name");
        sql.append("\nFROM (");
        sql.append("\n	SELECT t1.table_name table_name,t1.field_name field_name FROM t1 LEFT  JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t2.field_name IS NULL");
        sql.append("\n	UNION ALL ");
        sql.append("\n	SELECT t2.table_name table_name,t2.field_name field_name FROM t1 RIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t1.field_name IS NULL");
        sql.append("\n) t");
        sql.append("\nORDER BY 1");
        DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
        List<String> list = Lists.newArrayList();
        for (int i = 0; i < dte.getRows(); i++) {
            FieldSetEntity fse = dte.getFieldSetEntity(i);
            list.add(fse.getString("table_name"));
        }
        System.out.println(list);
        return list;
    }

    /**
     * 将数据库表结构字段同步至缓存字段表字段
     */
    @Override
    @Transactional
    public void execute() {
        // 将字段缓存表中多余的字段清理，只能直接连接数据库执行
        clearTableEx();

        // 将数据库中表结构字段同步到字段缓存表中
        syncDB2TableForTable();
        syncDB2TableForField();
    }

    /**
     * 将数据库中表结构字段同步到字段缓存表中-表
     */
    public void syncDB2TableForTable() {
        StringBuilder sql = new StringBuilder(128);
        sql.append("\nWITH t1 AS (");
        sql.append("\n    SELECT uuid,table_name,table_description,table_type FROM product_sys_datamodel_table");
        sql.append("\n),t2 AS (");
        sql.append("\n    SELECT TABLE_NAME table_name,TABLE_COMMENT table_description,CASE WHEN TABLE_TYPE='BASE TABLE' THEN 1 ELSE 2 END table_type");
        sql.append("\n    FROM information_schema.`TABLES`");
        sql.append("\n    WHERE table_schema='product_db_v3.0.0'");
        sql.append("\n)");
        sql.append("\nSELECT t2.*");
        sql.append("\nFROM t1");
        sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name");
        sql.append("\nWHERE t1.table_name IS NULL");
        DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
        for (int i = 0; i < dte.getRows(); i++) {
            FieldSetEntity fse = dte.getFieldSetEntity(i);
            FieldSetEntity saveFse = new FieldSetEntity();
            saveFse.setTableName("product_sys_datamodel_table");
            saveFse.setValue("table_name",          fse.getString("table_name"));
            saveFse.setValue("table_description",   fse.getString("table_description"));
            saveFse.setValue("table_type",          fse.getString("table_type"));
            saveFse.setValue("sequence", 1);
            System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
            baseDao.add(saveFse);
        }
    }

    /**
     * 将数据库中表结构字段同步到字段缓存表中-字段
     */
    public void syncDB2TableForField() {
        StringBuilder sql = new StringBuilder(128);
        sql.append("\nWITH t1 AS (");
        sql.append("\n    SELECT t.table_name,f.table_uuid,f.field_name,field_type type,field_length,field_unit,field_description");
        sql.append("\n    FROM product_sys_datamodel_field f");
        sql.append("\n    INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
        sql.append("\n),t2 AS (");
        sql.append("\n    SELECT table_name,column_name field_name,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
        sql.append("\n    FROM information_schema.`COLUMNS`");
        sql.append("\n    WHERE table_schema='product_db_v3.0.0'");
        sql.append("\n)");
        sql.append("\nSELECT t.uuid table_uuid,t2.*");
        sql.append("\nFROM t1 ");
        sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
        sql.append("\nLEFT JOIN product_sys_datamodel_table t ON t2.table_name=t.table_name");
        sql.append("\nWHERE t1.field_name IS NULL");
        sql.append("\nAND t2.table_name NOT LIKE '%v'");
        sql.append("\nAND t2.table_name NOT LIKE 'qrtz%'");
        sql.append("\nORDER BY 1");
        DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
        for (int i = 0; i < dte.getRows(); i++) {
            FieldSetEntity fse = dte.getFieldSetEntity(i);
            FieldSetEntity saveFse = new FieldSetEntity();
            saveFse.setTableName("product_sys_datamodel_field");
            saveFse.setValue("table_uuid",          fse.getString("table_uuid"));
            saveFse.setValue("field_name",          fse.getString("field_name"));
            saveFse.setValue("field_type",          getDictType(fse.getString("field_name"), fse.getString("type")));
            saveFse.setValue("field_show_name",     StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
            saveFse.setValue("field_description",   StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
            saveFse.setValue("field_length",        StringUtils.isEmpty(fse.getString("field_length")) ? 0 : fse.getString("field_length"));
            saveFse.setValue("field_unit",          StringUtils.isEmpty(fse.getString("field_unit")) ? 0 : fse.getString("field_unit"));
            saveFse.setValue("is_required",     0);
            saveFse.setValue("is_unique",       0);
            saveFse.setValue("is_filter",       0);
            saveFse.setValue("field_sequence",  0);
            System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
            baseDao.add(saveFse);
        }
    }

    /**
     * 根据数据库存储的类型获取字段缓存表的存储类型
     * @param dbType
     * @return
     */
    private String getDictType(String fieldName, String dbType) {
        fieldName = fieldName.trim();
        List<FieldType> baseList = Arrays.asList(FieldType.values());
        List<FieldType> aimList = baseList.stream().filter(o -> o.getDatabaseType().equals(dbType)).collect(Collectors.toList());
        String result = "";
        if (aimList.size() > 1) {
            if ("varchar".equals(dbType)) {
                if (Arrays.asList(
                        "district_sqe","asset_code","material_name","material_sn","supplier_uuid","uuid","version","material_uuid","use_dept",
                        "remark","name","standard_value","standard_value_1","inspect_item","inspect_remark","device_information","device_sn",
                        "fault_type","repair_applicant","origin_plan_uuid","maintenance_content","maintenance_item","id_number","id_type",
                        "sub_device_uuid","unit_uuid","high_threshold","department_uuid","manager_opinion","staff_uuid","personnel_opinion",
                        "must_read_num","file_org_level_uuid","light_mode_icon","dark_mode_icon"
                ).contains(fieldName)) {
                    result = "string";
                } else if("org_level_uuid".equals(fieldName)) {
                    result = "orgUuid";
                } else if (fieldName.endsWith("img") || fieldName.endsWith("photo") || fieldName.endsWith("attachment")) {
                    result = "file";
                } else if (Arrays.asList("parent_uuid","parent_key").contains(fieldName)) {
                    result = "parentuuid";
                } else if ("user_id".equals(fieldName)) {
                    result = "userid";
                }
            }
            if ("bigint".equals(dbType)) {
                if ("created_by".equals(fieldName)) {
                    result = "userid";
                } else if ("id".equals(fieldName)) {
                    result = "pk";
                } else {
                    result = "int";
                }
            }
            if ("int".equals(dbType)) {
                if (!"flow_flag".equals(fieldName)) {
                    result = "int";
                }
            }
            System.out.println("result = " + result);;
        } else if (aimList.size() == 1) {
            result = aimList.get(0).getDictValue();
        }
        if (StringUtils.isEmpty(result)) {
            throw new RuntimeException(String.format("test bug...fieldName:%s,dbType:%s", fieldName, dbType));
        }
        return result;
    }

    /**
     * 将字段缓存表中多余的字段清理
     */
    public void clearTableEx() {
        StringBuilder sql = new StringBuilder(128);
        sql.append("\nWITH t1 AS (");
        sql.append("\n    SELECT t.table_name,f.table_uuid,f.field_name,f.uuid field_uuid,field_type type,field_length,field_unit,field_description");
        sql.append("\n    FROM product_sys_datamodel_field f");
        sql.append("\n    INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
        sql.append("\n),t2 AS (");
        sql.append("\n    SELECT table_name,column_name field_name,null,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
        sql.append("\n    FROM information_schema.`COLUMNS`");
        sql.append("\n    WHERE table_schema='product_db_v3.0.0'");
        sql.append("\n)");
        sql.append("\nDELETE FROM product_sys_datamodel_field WHERE uuid IN (");
        sql.append("\n    SELECT t1.field_uuid");
        sql.append("\n    FROM t1 ");
        sql.append("\n    LEFT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
        sql.append("\n    WHERE t2.field_name IS NULL");
        sql.append("\n    AND t1.table_name NOT LIKE '%v'");
        sql.append("\n    AND t1.table_name NOT LIKE 'qrtz%'");
        sql.append("\n)");
        System.out.println("该sql无法通过baseDao执行，但是能够直接连接到数据库执行");
        System.out.println("========================================================================");
        System.out.println(sql);
        System.out.println("========================================================================");
    }
}