package com.fs.survey.service;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.fs.common.exception.ServiceException;
import com.fs.survey.domain.SurveyFieldConfig;
import com.fs.survey.mapper.DynamicTableMapper;
import com.fs.survey.mapper.SurveyFieldConfigMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 动态表管理Service
 *
 */
@Service
public class DynamicTableService {

    @Autowired
    private DynamicTableMapper dynamicTableMapper;

    @Autowired
    private SurveyFieldConfigMapper fieldConfigMapper;

    @Autowired
    private ISurveyFieldTypeMappingService fieldTypeMappingService;

    /**
     * 根据formJson创建动态表
     */
    @Transactional(rollbackFor = Exception.class)
    public String createDynamicTable(Long companyId, Long questionnaireId, Long versionId, String formJson) {
        String tableName = generateTableName(questionnaireId, versionId);

        // 检查表是否已存在
        if (dynamicTableMapper.checkTableExists(tableName) > 0) {
            throw new ServiceException("表已存在: " + tableName);
        }

        // 解析formJson
        JSONObject formData = JSONObject.parseObject(formJson);
        JSONArray fields = formData.getJSONArray("fields");

        if (fields == null || fields.isEmpty()) {
            throw new ServiceException("表单字段不能为空");
        }

        // 生成建表SQL
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("CREATE TABLE `").append(tableName).append("` (");
        createTableSql.append("`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',");
        createTableSql.append("`company_id` bigint NOT NULL COMMENT '租户ID',");
        createTableSql.append("`version_id` bigint NOT NULL COMMENT '版本ID',");

        List<SurveyFieldConfig> fieldConfigs = new ArrayList<>();
        int sortOrder = 0;

        // 遍历字段生成列定义
        for (int i = 0; i < fields.size(); i++) {
            JSONObject field = fields.getJSONObject(i);
            JSONObject config = field.getJSONObject("__config__");
            String vModel = field.getString("__vModel__");

            if (vModel == null || vModel.isEmpty()) {
                continue;
            }

            // 创建字段配置
            SurveyFieldConfig fieldConfig = createFieldConfig(companyId, questionnaireId, versionId, field);
            fieldConfig.setSortOrder(sortOrder++);
            fieldConfigs.add(fieldConfig);

            // 添加列定义
            String columnDef = buildColumnDefinition(fieldConfig);
            createTableSql.append(columnDef).append(",");
        }

        // 添加固定字段
        createTableSql.append("`user_id` bigint DEFAULT NULL COMMENT '提交用户ID',");
        createTableSql.append("`user_name` varchar(64) DEFAULT NULL COMMENT '提交用户名',");
        createTableSql.append("`is_anonymous` tinyint NOT NULL DEFAULT '0' COMMENT '是否匿名',");
        createTableSql.append("`submit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',");
        createTableSql.append("`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',");
        createTableSql.append("`ip_address` varchar(50) DEFAULT NULL COMMENT 'IP地址',");
        createTableSql.append("`user_agent` varchar(500) DEFAULT NULL COMMENT '浏览器UA',");
        createTableSql.append("`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标志',");

        // 添加主键和索引
        createTableSql.append("PRIMARY KEY (`id`),");
        createTableSql.append("KEY `idx_company_id` (`company_id`),");
        createTableSql.append("KEY `idx_user_id` (`user_id`),");
        createTableSql.append("KEY `idx_submit_time` (`submit_time`),");
        createTableSql.append("KEY `idx_del_flag` (`del_flag`)");

        createTableSql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ");
        createTableSql.append("COMMENT='问卷数据表-问卷").append(questionnaireId).append("-版本").append(versionId).append("'");

        // 执行建表
        dynamicTableMapper.createTable(createTableSql.toString());

        // 保存字段配置
        for (SurveyFieldConfig config : fieldConfigs) {
            fieldConfigMapper.insertSurveyFieldConfig(config);
        }

        return tableName;
    }

    /**
     * 生成表名
     */
    private String generateTableName(Long questionnaireId, Long versionId) {
        return "survey_data_" + questionnaireId + "_" + versionId;
    }

    /**
     * 创建字段配置对象
     */
    private SurveyFieldConfig createFieldConfig(Long companyId, Long questionnaireId, Long versionId, JSONObject field) {
        SurveyFieldConfig config = new SurveyFieldConfig();

        JSONObject configObj = field.getJSONObject("__config__");
        String vModel = field.getString("__vModel__");
        String tag = configObj.getString("tag");
        String tagIcon = configObj.getString("tagIcon");
        String label = configObj.getString("label");

        config.setCompanyId(companyId);
        config.setQuestionnaireId(questionnaireId);
        config.setVersionId(versionId);
        config.setFieldKey(vModel);
        config.setFieldLabel(label);
        config.setFieldTag(tag);
        config.setFieldTagIcon(tagIcon);

        // 获取字段类型映射
        Map<String, Object> typeMapping = fieldTypeMappingService.getTypeMappingByTag(tag, tagIcon);
        config.setDbColumnType((String) typeMapping.get("dbType"));
        config.setDbColumnLength((Integer) typeMapping.get("dbLength"));
        config.setSearchType((String) typeMapping.get("searchType"));

        // 设置是否必填
        config.setIsRequired(configObj.getBoolean("required") ? 1 : 0);

        // 设置默认值 & 保存选项配置(下拉、单选、多选等)，均来自 __slot__
        if (field.containsKey("__slot__")) {
            JSONObject slot = field.getJSONObject("__slot__");
            if (slot.containsKey("default")) {
                config.setDefaultValue(slot.getString("default"));
            }
            if (slot.containsKey("options")) {
                config.setOptionsJson(slot.getJSONArray("options").toJSONString());
            }
        }

        // 保存校验规则
        if (configObj.containsKey("regList")) {
            config.setRegListJson(configObj.getJSONArray("regList").toJSONString());
        }

        // 保存原始配置
        config.setRawConfigJson(configObj.toJSONString());

        // 默认配置
        config.setIsSearchable(1);
        config.setIsListShow(1);
        config.setIsExport(1);

        return config;
    }

    /**
     * 构建列定义SQL
     */
    private String buildColumnDefinition(SurveyFieldConfig config) {
        StringBuilder columnDef = new StringBuilder();
        columnDef.append("`").append(config.getFieldKey()).append("` ");
        columnDef.append(config.getDbColumnType());

        if (config.getDbColumnLength() != null && config.getDbColumnLength() > 0) {
            columnDef.append("(").append(config.getDbColumnLength()).append(")");
        }

        columnDef.append(" DEFAULT NULL");
        columnDef.append(" COMMENT '").append(config.getFieldLabel()).append("'");

        return columnDef.toString();
    }

    /**
     * 插入数据到动态表
     */
    public int insertDynamicData(String tableName, Map<String, Object> data) {
        return dynamicTableMapper.insertData(tableName, data);
    }

    /**
     * 查询动态表数据
     */
    public List<Map<String, Object>> selectDynamicDataList(String tableName, Map<String, Object> params) {
        return dynamicTableMapper.selectDataList(tableName, params);
    }

    /**
     * 删除动态表
     */
    public void dropDynamicTable(String tableName) {
        if (dynamicTableMapper.checkTableExists(tableName) > 0) {
            dynamicTableMapper.dropTable(tableName);
        }
    }
}
