package dicmanagement

import (
	"context"
	"fmt"
	"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"
	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
)

// BatchSaveDicTables 批量保存数据库表字典
func BatchSaveDicTables(req *BatchSaveDicTablesRequest, ctx context.Context, dbFactory *database.DBFactory, reqCtx *ctx.RequestContext) *response.QueryResult[bool] {
	logger.Debug("BatchSaveDicTables-开始批量保存数据库表字典")

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

	// 1.1: 从字段字典表中收集所有表名称，然后跟表集合中对比
	if err := validateTableReferences(req); err != nil {
		logger.ErrorC(reqCtx, fmt.Sprintf("表引用验证失败: %v", err))
		return util.CreateErrorResult[bool](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[bool](fmt.Sprintf("开始事务失败: %v", err), reqCtx)
	}

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

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

	// 2. 首先把这两个集合插入到内存表（数据库是mysql）
	// 使用临时表存储批量数据
	if err := batchProcessWithTempTables(ctx, tx, req, creator); err != nil {
		tx.Rollback()
		logger.ErrorC(reqCtx, fmt.Sprintf("批量处理失败: %v", err))
		return util.CreateErrorResult[bool](fmt.Sprintf("批量处理失败: %v", err), reqCtx)
	}

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

	logger.Debug(fmt.Sprintf("成功批量保存数据库表字典: %d 个表, %d 个字段", len(req.Tables), len(req.Fields)))
	return util.CreateSuccessResultData[bool](true, reqCtx)
}

// validateBatchSaveRequest 验证批量保存请求
func validateBatchSaveRequest(req *BatchSaveDicTablesRequest) error {
	if len(req.Tables) == 0 {
		return fmt.Errorf("表集合不能为空")
	}
	if len(req.Fields) == 0 {
		return fmt.Errorf("字段集合不能为空")
	}

	// 验证表集合
	tableIDs := make(map[string]bool)
	for i, table := range req.Tables {
		if table.TableID == "" {
			return fmt.Errorf("第%d个表的表ID不能为空", i+1)
		}
		if table.TableType == "" {
			return fmt.Errorf("第%d个表的表类型不能为空", i+1)
		}
		if table.Name == "" {
			return fmt.Errorf("第%d个表的表名称不能为空", i+1)
		}
		tableIDs[table.TableID] = true
	}

	// 验证字段集合
	for i, field := range req.Fields {
		if field.FieldID == "" {
			return fmt.Errorf("第%d个字段的字段ID不能为空", i+1)
		}
		if field.TableID == "" {
			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)
		}
		if field.DataType == "" {
			return fmt.Errorf("第%d个字段的数据类型不能为空", i+1)
		}
		// 验证字段ID格式：table_id + "." + field_name
		expectedFieldID := field.TableID + "." + field.FieldName
		if field.FieldID != expectedFieldID {
			return fmt.Errorf("第%d个字段的字段ID格式错误：期望 '%s'，实际 '%s'", i+1, expectedFieldID, field.FieldID)
		}
	}

	return nil
}

// validateTableReferences 验证表引用
func validateTableReferences(req *BatchSaveDicTablesRequest) error {
	// 收集表集合中的所有表ID
	tableIDSet := make(map[string]bool)
	for _, table := range req.Tables {
		tableIDSet[table.TableID] = true
	}

	// 检查字段集合中的所有表ID是否都在表集合中存在
	for i, field := range req.Fields {
		if !tableIDSet[field.TableID] {
			return fmt.Errorf("第%d个字段引用的表ID '%s' 不在表集合中", i+1, field.TableID)
		}
	}

	return nil
}

// batchProcessWithTempTables 使用临时表批量处理数据
func batchProcessWithTempTables(ctx context.Context, tx *sqlx.Tx, req *BatchSaveDicTablesRequest, creator string) error {
	// 创建临时表存储批量表数据
	tempTablesTable := "temp_dic_table_batch_" + uuid.New().String()[:8]
	if err := createTempTables(ctx, tx, tempTablesTable); err != nil {
		return fmt.Errorf("创建临时表失败: %w", err)
	}
	defer cleanupTempTable(ctx, tx, tempTablesTable)

	// 批量插入表数据到临时表
	if err := batchInsertTablesToTemp(ctx, tx, tempTablesTable, req.Tables, creator); err != nil {
		return fmt.Errorf("批量插入表数据到临时表失败: %w", err)
	}

	// 3. 跟dic_table.go对比，在dic_table.go中存在的，使用内存表更新；在dic_table.go不存在的插入
	if err := upsertTablesFromTemp(ctx, tx, tempTablesTable); err != nil {
		return fmt.Errorf("更新/插入表失败: %w", err)
	}

	// 创建临时表存储批量字段数据
	tempFieldsTable := "temp_dic_table_field_batch_" + uuid.New().String()[:8]
	if err := createTempFieldsTable(ctx, tx, tempFieldsTable); err != nil {
		return fmt.Errorf("创建临时字段表失败: %w", err)
	}
	defer cleanupTempTable(ctx, tx, tempFieldsTable)

	// 批量插入字段数据到临时表
	if err := batchInsertFieldsToTemp(ctx, tx, tempFieldsTable, req.Fields, creator); err != nil {
		return fmt.Errorf("批量插入字段数据到临时表失败: %w", err)
	}

	// 4. dic_table_field.go 字段字典处理：删除相关表的所有字段，然后批量插入
	if err := upsertFieldsFromTemp(ctx, tx, tempFieldsTable); err != nil {
		return fmt.Errorf("处理字段失败: %w", err)
	}

	return nil
}

// createTempTables 创建临时表存储批量表数据
func createTempTables(ctx context.Context, tx *sqlx.Tx, tableName string) error {
	createSQL := fmt.Sprintf(`
		CREATE TEMPORARY TABLE %s (
			table_id VARCHAR(64) NOT NULL PRIMARY KEY,
			table_type VARCHAR(20) NOT NULL,
			table_name VARCHAR(100) NOT NULL,
			description VARCHAR(500) NOT NULL DEFAULT '',
			creator VARCHAR(32) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
	`, tableName)

	_, err := tx.ExecContext(ctx, createSQL)
	return err
}

// createTempFieldsTable 创建临时字段表
func createTempFieldsTable(ctx context.Context, tx *sqlx.Tx, tableName string) error {
	createSQL := fmt.Sprintf(`
		CREATE TEMPORARY TABLE %s (
			id VARCHAR(128) NOT NULL PRIMARY KEY,
			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 DEFAULT '',
			description VARCHAR(500) NOT NULL DEFAULT '',
			creator VARCHAR(32) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
	`, tableName)

	_, err := tx.ExecContext(ctx, createSQL)
	return err
}

// cleanupTempTable 清理临时表
func cleanupTempTable(ctx context.Context, tx *sqlx.Tx, tableName string) {
	dropSQL := fmt.Sprintf("DROP TEMPORARY TABLE IF EXISTS %s", tableName)
	tx.ExecContext(ctx, dropSQL)
}

// batchInsertTablesToTemp 批量插入表数据到临时表
func batchInsertTablesToTemp(ctx context.Context, tx *sqlx.Tx, tempTableName string, tables []DicTableRequest, creator string) error {
	if len(tables) == 0 {
		return nil
	}

	query := fmt.Sprintf("INSERT INTO %s (table_id, table_type, table_name, description, creator) VALUES ", tempTableName)
	valueStrings := make([]string, 0, len(tables))
	valueArgs := make([]interface{}, 0, len(tables)*5)

	for _, table := range tables {
		valueStrings = append(valueStrings, "(?, ?, ?, ?, ?)")
		valueArgs = append(valueArgs, table.TableID)
		valueArgs = append(valueArgs, table.TableType)
		valueArgs = append(valueArgs, table.Name)
		valueArgs = append(valueArgs, table.Description)
		valueArgs = append(valueArgs, creator)
	}

	fullQuery := query + strings.Join(valueStrings, ", ")
	_, err := tx.ExecContext(ctx, fullQuery, valueArgs...)
	return err
}

// batchInsertFieldsToTemp 批量插入字段数据到临时表
func batchInsertFieldsToTemp(ctx context.Context, tx *sqlx.Tx, tempTableName string, fields []DicTableFieldRequest, creator string) error {
	if len(fields) == 0 {
		return nil
	}

	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(fields))
	valueArgs := make([]interface{}, 0, len(fields)*9)

	for _, field := range fields {
		valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?, ?)")
		valueArgs = append(valueArgs, field.FieldID) // id字段
		valueArgs = append(valueArgs, field.FieldID) // field_id字段
		valueArgs = append(valueArgs, field.TableID)
		valueArgs = append(valueArgs, field.FiledType)
		valueArgs = append(valueArgs, field.DataType)
		valueArgs = append(valueArgs, field.FieldName)
		valueArgs = append(valueArgs, field.FieldNameCN)
		valueArgs = append(valueArgs, field.Description)
		valueArgs = append(valueArgs, creator)
	}

	fullQuery := query + strings.Join(valueStrings, ", ")
	_, err := tx.ExecContext(ctx, fullQuery, valueArgs...)
	return err
}

// upsertTablesFromTemp 从临时表更新或插入表数据
func upsertTablesFromTemp(ctx context.Context, tx *sqlx.Tx, tempTableName string) error {
	// 更新已存在的记录
	updateSQL := fmt.Sprintf(`
		UPDATE dic_table dt
		JOIN %s tt ON dt.table_id = tt.table_id AND dt.deleted_at IS NULL
		SET dt.table_type = tt.table_type,
			dt.table_name = tt.table_name,
			dt.description = tt.description,
			dt.updated_at = CURRENT_TIMESTAMP
	`, tempTableName)
	if _, err := tx.ExecContext(ctx, updateSQL); err != nil {
		return fmt.Errorf("更新表记录失败: %w", err)
	}

	// 插入不存在的记录
	insertSQL := fmt.Sprintf(`
		INSERT INTO dic_table (table_id, table_type, table_name, description, creator, created_at, updated_at)
		SELECT tt.table_id, tt.table_type, tt.table_name, tt.description, tt.creator, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
		FROM %s tt
		WHERE NOT EXISTS (
			SELECT 1 FROM dic_table dt WHERE dt.table_id = tt.table_id AND dt.deleted_at IS NULL
		)
	`, tempTableName)
	if _, err := tx.ExecContext(ctx, insertSQL); err != nil {
		return fmt.Errorf("插入新表记录失败: %w", err)
	}

	return nil
}

// upsertFieldsFromTemp 从临时表删除并重新插入字段数据
func upsertFieldsFromTemp(ctx context.Context, tx *sqlx.Tx, tempTableName string) error {

	// 1. 删除本次批量中所有表的所有字段记录
	deleteSQL := fmt.Sprintf(`
		DELETE dtf 
		FROM dic_table_field dtf
		WHERE EXISTS (SELECT 1 FROM %s tt WHERE tt.table_id = dtf.table_id)
		  AND dtf.deleted_at IS NULL
	`, tempTableName)

	if _, err := tx.ExecContext(ctx, deleteSQL); err != nil {
		return fmt.Errorf("删除字段记录失败: %w", err)
	}

	// 2. 批量插入字段记录
	insertSQL := 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 
			tt.field_id, tt.field_id, tt.table_id, tt.filed_type, tt.data_type, tt.field_name, 
			tt.field_name_cn, tt.description, tt.creator, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
		FROM %s tt
	`, tempTableName)

	if _, err := tx.ExecContext(ctx, insertSQL); err != nil {
		return fmt.Errorf("插入字段记录失败: %w", err)
	}

	return nil
}