package dicmanagement

import (
	"context"
	"fmt"
	"regexp"
	"strings"

	"git.x2erp.com/qdy/go-base/ctx"
	"git.x2erp.com/qdy/go-base/logger"
	"git.x2erp.com/qdy/go-base/model/response"
	"git.x2erp.com/qdy/go-base/util"
	"git.x2erp.com/qdy/go-db/factory/database"
	"git.x2erp.com/qdy/go-svc-configure/internal/tables"
	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
)

// SaveDicTable 保存数据库表字典（主表+子表）
func SaveDicTable(req *DicTableRequest, ctx context.Context, dbFactory *database.DBFactory, reqCtx *ctx.RequestContext) *response.QueryResult[DicTableDetail] {
	logger.Debug("SaveDicTable-开始保存数据库表字典")

	// 参数验证
	if err := validateDicTableRequest(req); err != nil {
		logger.ErrorC(reqCtx, fmt.Sprintf("参数验证失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("参数验证失败: %v", err), reqCtx)
	}

	// 获取数据库连接并开始事务
	db := dbFactory.GetDB()
	tx, err := db.BeginTxx(ctx, nil)
	if err != nil {
		logger.ErrorC(reqCtx, fmt.Sprintf("开始事务失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("开始事务失败: %v", err), reqCtx)
	}

	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p)
		}
	}()

	// 获取当前用户
	creator := reqCtx.UserID
	if creator == "" {
		creator = "system"
	}

	// 1. 处理主表：检查是否存在 → 存在则更新，软删除则恢复，不存在则插入
	tableExists, err := checkTableExists(ctx, tx, req.TableID)
	if err != nil {
		tx.Rollback()
		logger.ErrorC(reqCtx, fmt.Sprintf("检查表存在性失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("检查表存在性失败: %v", err), reqCtx)
	}

	var table tables.DicTableDB
	if tableExists {
		// 更新主表
		table, err = updateTable(ctx, tx, req, creator)
		if err != nil {
			tx.Rollback()
			logger.ErrorC(reqCtx, fmt.Sprintf("更新表失败: %v", err))
			return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("更新表失败: %v", err), reqCtx)
		}
		logger.Debug(fmt.Sprintf("更新表成功: %s", req.TableID))
	} else {
		// 检查是否有软删除的记录
		softDeletedExists, err := checkSoftDeletedTableExists(ctx, tx, req.TableID)
		if err != nil {
			tx.Rollback()
			logger.ErrorC(reqCtx, fmt.Sprintf("检查软删除表存在性失败: %v", err))
			return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("检查软删除表存在性失败: %v", err), reqCtx)
		}

		if softDeletedExists {
			// 恢复软删除的表
			table, err = restoreTable(ctx, tx, req, creator)
			if err != nil {
				tx.Rollback()
				logger.ErrorC(reqCtx, fmt.Sprintf("恢复软删除表失败: %v", err))
				return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("恢复软删除表失败: %v", err), reqCtx)
			}
			logger.Debug(fmt.Sprintf("恢复软删除表成功: %s", req.TableID))
		} else {
			// 插入主表
			table, err = insertTable(ctx, tx, req, creator)
			if err != nil {
				tx.Rollback()
				logger.ErrorC(reqCtx, fmt.Sprintf("插入表失败: %v", err))
				return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("插入表失败: %v", err), reqCtx)
			}
			logger.Debug(fmt.Sprintf("插入表成功: %s", req.TableID))
		}
	}

	// 2. 处理子表：删除此表的所有字段，然后插入新的记录
	// 首先软删除该表的所有字段记录
	err = deleteAllTableFields(ctx, tx, req.TableID)
	if err != nil {
		tx.Rollback()
		logger.ErrorC(reqCtx, fmt.Sprintf("删除表字段失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("删除表字段失败: %v", err), reqCtx)
	}
	logger.Debug(fmt.Sprintf("已软删除表 %s 的所有字段记录", req.TableID))

	// 然后批量插入新的字段记录
	fields, err := batchInsertTableFields(ctx, tx, req, creator)
	if err != nil {
		tx.Rollback()
		logger.ErrorC(reqCtx, fmt.Sprintf("批量插入表字段失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("批量插入表字段失败: %v", err), reqCtx)
	}
	logger.Debug(fmt.Sprintf("成功批量插入 %d 个表字段", len(fields)))

	// 提交事务
	if err := tx.Commit(); err != nil {
		logger.ErrorC(reqCtx, fmt.Sprintf("提交事务失败: %v", err))
		return util.CreateErrorResult[DicTableDetail](fmt.Sprintf("提交事务失败: %v", err), reqCtx)
	}

	logger.Debug(fmt.Sprintf("成功保存数据库表字典: %s, 包含 %d 个字段", req.TableID, len(fields)))

	// 构建返回结果
	detail := DicTableDetail{
		Table:  table,
		Fields: fields,
	}

	return util.CreateSuccessResultData[DicTableDetail](detail, reqCtx)
}

// validateDicTableRequest 验证数据库表字典请求
func validateDicTableRequest(req *DicTableRequest) error {
	if req.TableID == "" {
		return fmt.Errorf("表ID不能为空")
	}

	// 验证表ID格式：仅允许字母、数字、下划线
	match, _ := regexp.MatchString("^[a-zA-Z0-9_]+$", req.TableID)
	if !match {
		return fmt.Errorf("表ID只能包含字母、数字、下划线")
	}

	if req.TableType == "" {
		return fmt.Errorf("表类型不能为空")
	}
	// 验证表类型
	validTypes := map[string]bool{"实体表": true, "视图": true, "物化视图": true}
	if !validTypes[req.TableType] {
		return fmt.Errorf("表类型必须是'实体表'、'视图'或'物化视图'")
	}

	if req.Name == "" {
		return fmt.Errorf("表名称不能为空")
	}

	// 验证子表数据
	for i, field := range req.Fields {
		if field.FieldID == "" {
			return fmt.Errorf("第%d个字段的字段ID不能为空", i+1)
		}
		if field.FieldName == "" {
			return fmt.Errorf("第%d个字段的字段名称不能为空", i+1)
		}
		if field.FiledType == "" {
			return fmt.Errorf("第%d个字段的字段类型不能为空", i+1)
		}
		// 验证字段类型
		validFieldTypes := map[string]bool{"实际字段": true, "计算字段": true}
		if !validFieldTypes[field.FiledType] {
			return fmt.Errorf("第%d个字段的字段类型必须是'实际字段'或'计算字段'", i+1)
		}
		if field.DataType == "" {
			return fmt.Errorf("第%d个字段的数据类型不能为空", i+1)
		}
		// 验证数据类型
		validDataTypes := map[string]bool{"字符型": true, "数值型": true, "日期型": true, "布尔型": true}
		if !validDataTypes[field.DataType] {
			return fmt.Errorf("第%d个字段的数据类型必须是'字符型'、'数值型'、'日期型'或'布尔型'", i+1)
		}
	}

	return nil
}

// checkTableExists 检查表是否存在（仅活跃记录）
func checkTableExists(ctx context.Context, tx *sqlx.Tx, tableID string) (bool, error) {
	var count int
	query := "SELECT COUNT(*) FROM dic_table WHERE table_id = ? AND deleted_at IS NULL"
	err := tx.GetContext(ctx, &count, query, tableID)
	return count > 0, err
}

// checkSoftDeletedTableExists 检查表是否被软删除
func checkSoftDeletedTableExists(ctx context.Context, tx *sqlx.Tx, tableID string) (bool, error) {
	var count int
	query := "SELECT COUNT(*) FROM dic_table WHERE table_id = ? AND deleted_at IS NOT NULL"
	err := tx.GetContext(ctx, &count, query, tableID)
	return count > 0, err
}

// restoreTable 恢复软删除的表
func restoreTable(ctx context.Context, tx *sqlx.Tx, req *DicTableRequest, creator string) (tables.DicTableDB, error) {
	query := `
		UPDATE dic_table 
		SET deleted_at = NULL, table_type = ?, table_name = ?, description = ?, updated_at = CURRENT_TIMESTAMP
		WHERE table_id = ? AND deleted_at IS NOT NULL
	`
	_, err := tx.ExecContext(ctx, query,
		req.TableType,
		req.Name,
		req.Description,
		req.TableID,
	)
	if err != nil {
		return tables.DicTableDB{}, err
	}

	// 查询恢复后的记录
	var table tables.DicTableDB
	selectQuery := `
		SELECT table_id, table_type, table_name, description, creator, created_at, updated_at, deleted_at
		FROM dic_table
		WHERE table_id = ? AND deleted_at IS NULL
	`
	err = tx.GetContext(ctx, &table, selectQuery, req.TableID)
	return table, err
}

// insertTable 插入表主表
func insertTable(ctx context.Context, tx *sqlx.Tx, req *DicTableRequest, creator string) (tables.DicTableDB, error) {
	query := `
		INSERT INTO dic_table (table_id, table_type, table_name, description, creator, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
	`
	logger.Debug(fmt.Sprintf("insertTable - 执行插入: query=%s, table_id=%s, creator=%s", query, req.TableID, creator))

	_, err := tx.ExecContext(ctx, query,
		req.TableID,
		req.TableType,
		req.Name,
		req.Description,
		creator,
	)
	if err != nil {
		logger.Error(fmt.Sprintf("insertTable - 插入失败: %v", err))
		return tables.DicTableDB{}, err
	}

	// 查询刚插入的记录
	var table tables.DicTableDB
	selectQuery := `
		SELECT table_id, table_type, table_name, description, creator, created_at, updated_at, deleted_at
		FROM dic_table
		WHERE table_id = ? AND deleted_at IS NULL
	`
	err = tx.GetContext(ctx, &table, selectQuery, req.TableID)
	return table, err
}

// updateTable 更新表主表
func updateTable(ctx context.Context, tx *sqlx.Tx, req *DicTableRequest, creator string) (tables.DicTableDB, error) {
	query := `
		UPDATE dic_table
		SET table_type = ?, table_name = ?, description = ?, updated_at = CURRENT_TIMESTAMP
		WHERE table_id = ? AND deleted_at IS NULL
	`
	_, err := tx.ExecContext(ctx, query,
		req.TableType,
		req.Name,
		req.Description,
		req.TableID,
	)
	if err != nil {
		return tables.DicTableDB{}, err
	}

	// 查询更新后的记录
	var table tables.DicTableDB
	selectQuery := `
		SELECT table_id, table_type, table_name, description, creator, created_at, updated_at, deleted_at
		FROM dic_table
		WHERE table_id = ? AND deleted_at IS NULL
	`
	err = tx.GetContext(ctx, &table, selectQuery, req.TableID)
	return table, err
}

// deleteAllTableFields 软删除表的所有字段记录
func deleteAllTableFields(ctx context.Context, tx *sqlx.Tx, tableID string) error {
	query := "UPDATE dic_table_field SET deleted_at = CURRENT_TIMESTAMP WHERE table_id = ? AND deleted_at IS NULL"
	_, err := tx.ExecContext(ctx, query, tableID)
	return err
}

// insertTableField 插入表字段子表
func insertTableField(ctx context.Context, tx *sqlx.Tx, fieldReq *DicTableFieldRequest, creator string) (tables.DicTableFieldDB, error) {
	// 生成ID
	id := uuid.New().String()

	query := `
		INSERT INTO dic_table_field (id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
	`
	_, err := tx.ExecContext(ctx, query,
		id,
		fieldReq.FieldID,
		fieldReq.TableID,
		fieldReq.FiledType,
		fieldReq.DataType,
		fieldReq.FieldName,
		fieldReq.FieldNameCN,
		fieldReq.Description,
		creator,
	)
	if err != nil {
		return tables.DicTableFieldDB{}, err
	}

	// 查询刚插入的记录
	var field tables.DicTableFieldDB
	selectQuery := `
		SELECT id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator, created_at, updated_at, deleted_at
		FROM dic_table_field
		WHERE field_id = ? AND deleted_at IS NULL
	`
	err = tx.GetContext(ctx, &field, selectQuery, fieldReq.FieldID)
	return field, err
}

// batchInsertTableFields 批量插入表字段子表
func batchInsertTableFields(ctx context.Context, tx *sqlx.Tx, req *DicTableRequest, creator string) ([]tables.DicTableFieldDB, error) {
	if len(req.Fields) == 0 {
		return nil, nil
	}

	// 创建临时表
	tempTableName := "temp_dic_table_field_" + uuid.New().String()[:8]
	createTempTableSQL := fmt.Sprintf(`
		CREATE TEMPORARY TABLE %s (
			id VARCHAR(128) NOT NULL,
			field_id VARCHAR(128) NOT NULL,
			table_id VARCHAR(64) NOT NULL,
			filed_type VARCHAR(20) NOT NULL,
			data_type VARCHAR(20) NOT NULL,
			field_name VARCHAR(64) NOT NULL,
			field_name_cn VARCHAR(64) NOT NULL,
			description VARCHAR(500) NOT NULL,
			creator VARCHAR(32) NOT NULL
		)
	`, tempTableName)

	_, err := tx.ExecContext(ctx, createTempTableSQL)
	if err != nil {
		return nil, fmt.Errorf("创建临时表失败: %w", err)
	}
	defer func() {
		// 清理临时表
		dropSQL := fmt.Sprintf("DROP TEMPORARY TABLE IF EXISTS %s", tempTableName)
		tx.ExecContext(ctx, dropSQL)
	}()

	// 准备批量插入到临时表
	query := fmt.Sprintf("INSERT INTO %s (id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator) VALUES ", tempTableName)

	valueStrings := make([]string, 0, len(req.Fields))
	valueArgs := make([]interface{}, 0, len(req.Fields)*9) // 9个字段每个字段

	fieldIDs := make([]string, len(req.Fields))

	for i, fieldReq := range req.Fields {
		// 验证字段主键规则：fieldID 应该是 table_id + "." + field_name
		expectedFieldID := req.TableID + "." + fieldReq.FieldName
		if fieldReq.FieldID != expectedFieldID {
			return nil, fmt.Errorf("字段主键不符合规则: 期望 %s, 实际 %s", expectedFieldID, fieldReq.FieldID)
		}

		// 确保字段的 tableID 与主表一致
		fieldReq.TableID = req.TableID

		// 生成ID
		id := uuid.New().String()

		// 保存fieldID用于后续查询
		fieldIDs[i] = fieldReq.FieldID

		// 构建VALUES占位符
		valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?, ?)")

		// 添加参数值
		valueArgs = append(valueArgs, id)
		valueArgs = append(valueArgs, fieldReq.FieldID)
		valueArgs = append(valueArgs, fieldReq.TableID)
		valueArgs = append(valueArgs, fieldReq.FiledType)
		valueArgs = append(valueArgs, fieldReq.DataType)
		valueArgs = append(valueArgs, fieldReq.FieldName)
		valueArgs = append(valueArgs, fieldReq.FieldNameCN)
		valueArgs = append(valueArgs, fieldReq.Description)
		valueArgs = append(valueArgs, creator)
	}

	// 执行批量插入到临时表
	fullQuery := query + strings.Join(valueStrings, ", ")
	_, err = tx.ExecContext(ctx, fullQuery, valueArgs...)
	if err != nil {
		return nil, fmt.Errorf("批量插入到临时表失败: %w", err)
	}

	// 从临时表复制数据到目标表
	copySQL := fmt.Sprintf(`
		INSERT INTO dic_table_field (id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator, created_at, updated_at)
		SELECT id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
		FROM %s
	`, tempTableName)

	_, err = tx.ExecContext(ctx, copySQL)
	if err != nil {
		return nil, fmt.Errorf("从临时表复制数据失败: %w", err)
	}

	// 批量查询刚插入的记录
	// 构建IN查询
	queryStr, args, err := sqlx.In(`
		SELECT id, field_id, table_id, filed_type, data_type, field_name, field_name_cn, description, creator, created_at, updated_at, deleted_at
		FROM dic_table_field
		WHERE field_id IN (?) AND deleted_at IS NULL
		ORDER BY FIELD(field_id, ?)
	`, fieldIDs, fieldIDs)

	if err != nil {
		return nil, fmt.Errorf("构建IN查询失败: %w", err)
	}

	queryStr = tx.Rebind(queryStr)
	var fields []tables.DicTableFieldDB
	err = tx.SelectContext(ctx, &fields, queryStr, args...)
	if err != nil {
		return nil, fmt.Errorf("查询插入的字段失败: %w", err)
	}

	return fields, nil
}