package db

import (
	"fmt"
	"sort"
	"strings"

	"GoNavi-Wails/internal/connection"
	"GoNavi-Wails/internal/logger"
)

var damengDatabaseQueries = []string{
	// 优先使用达梦原生系统表（SYSDBA 保留：作为默认管理员 schema，大多数用户在此创建业务表）
	"SELECT DISTINCT OBJECT_NAME AS DATABASE_NAME FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCH' AND OBJECT_NAME NOT IN ('SYS','SYSAUDITOR','SYSSSO','CTISYS','__RECYCLE_USER__') ORDER BY OBJECT_NAME",
	"SELECT SCHEMA_NAME AS DATABASE_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('SYS','SYSAUDITOR','SYSSSO','CTISYS','INFORMATION_SCHEMA') ORDER BY SCHEMA_NAME",
	// Oracle 兼容层
	"SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS DATABASE_NAME FROM DUAL",
	"SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') AS DATABASE_NAME FROM DUAL",
	"SELECT USERNAME AS DATABASE_NAME FROM USER_USERS",
	"SELECT USERNAME AS DATABASE_NAME FROM ALL_USERS ORDER BY USERNAME",
	"SELECT USERNAME AS DATABASE_NAME FROM DBA_USERS ORDER BY USERNAME",
	"SELECT USERNAME AS DATABASE_NAME FROM SYS.DBA_USERS ORDER BY USERNAME",
	"SELECT DISTINCT OWNER AS DATABASE_NAME FROM ALL_OBJECTS ORDER BY OWNER",
	"SELECT DISTINCT OWNER AS DATABASE_NAME FROM ALL_TABLES ORDER BY OWNER",
	// 最终兜底：获取当前连接用户作为 schema 名称
	"SELECT USER AS DATABASE_NAME FROM DUAL",
}

type damengQueryFunc func(query string) ([]map[string]interface{}, []string, error)

func collectDamengDatabaseNames(query damengQueryFunc) ([]string, error) {
	seen := make(map[string]struct{})
	dbs := make([]string, 0, 64)
	var lastErr error

	for idx, q := range damengDatabaseQueries {
		data, _, err := query(q)
		if err != nil {
			logger.Warnf("达梦 GetDatabases 查询[%d]失败：%v（SQL: %.80s…）", idx, err, q)
			lastErr = err
			continue
		}
		newCount := 0
		for _, row := range data {
			name := getDamengRowString(row,
				"DATABASE_NAME",
				"USERNAME",
				"OWNER",
				"SCHEMA_NAME",
				"CURRENT_SCHEMA",
				"CURRENT_USER",
			)
			if name == "" {
				for _, v := range row {
					text := strings.TrimSpace(fmt.Sprintf("%v", v))
					if text == "" || strings.EqualFold(text, "<nil>") {
						continue
					}
					name = text
					break
				}
			}
			if name == "" {
				continue
			}
			key := strings.ToUpper(name)
			if _, ok := seen[key]; ok {
				continue
			}
			seen[key] = struct{}{}
			dbs = append(dbs, name)
			newCount++
		}
		logger.Infof("达梦 GetDatabases 查询[%d]成功：返回 %d 行，新增 %d 条（SQL: %.80s…）", idx, len(data), newCount, q)
	}

	logger.Infof("达梦 GetDatabases 最终结果：共 %d 条数据库/schema", len(dbs))
	if len(dbs) == 0 && lastErr != nil {
		logger.Warnf("达梦 GetDatabases 所有查询均失败，返回最后错误：%v", lastErr)
		return nil, lastErr
	}

	sort.Slice(dbs, func(i, j int) bool {
		return strings.ToUpper(dbs[i]) < strings.ToUpper(dbs[j])
	})
	return dbs, nil
}

func getDamengRowString(row map[string]interface{}, keys ...string) string {
	if len(row) == 0 {
		return ""
	}
	for _, key := range keys {
		for k, v := range row {
			if !strings.EqualFold(strings.TrimSpace(k), strings.TrimSpace(key)) {
				continue
			}
			text := strings.TrimSpace(fmt.Sprintf("%v", v))
			if text == "" || strings.EqualFold(text, "<nil>") {
				return ""
			}
			return text
		}
	}
	return ""
}

func buildDamengColumnsQuery(dbName, tableName string) string {
	upperTableName := strings.ToUpper(strings.TrimSpace(tableName))
	upperDBName := strings.ToUpper(strings.TrimSpace(dbName))

	if upperDBName == "" {
		return fmt.Sprintf(`SELECT c.column_name, c.data_type, c.data_length, c.char_length, c.data_precision, c.data_scale, c.nullable, c.data_default,
		CASE WHEN pk.column_name IS NOT NULL THEN 'PRI' ELSE '' END AS column_key
		FROM user_tab_columns c
		LEFT JOIN (
			SELECT cols.table_name, cols.column_name
			FROM user_constraints cons
			JOIN user_cons_columns cols USING (constraint_name)
			WHERE cons.constraint_type = 'P'
		) pk ON c.table_name = pk.table_name AND c.column_name = pk.column_name
		WHERE c.table_name = '%s'
		ORDER BY c.column_id`, upperTableName)
	}

	return fmt.Sprintf(`SELECT c.column_name, c.data_type, c.data_length, c.char_length, c.data_precision, c.data_scale, c.nullable, c.data_default,
		CASE WHEN pk.column_name IS NOT NULL THEN 'PRI' ELSE '' END AS column_key
		FROM all_tab_columns c
		LEFT JOIN (
			SELECT cols.owner, cols.table_name, cols.column_name
			FROM all_constraints cons
			JOIN all_cons_columns cols
			  ON cons.owner = cols.owner AND cons.constraint_name = cols.constraint_name
			WHERE cons.constraint_type = 'P'
		) pk ON c.owner = pk.owner AND c.table_name = pk.table_name AND c.column_name = pk.column_name
		WHERE c.owner = '%s' AND c.table_name = '%s'
		ORDER BY c.column_id`, upperDBName, upperTableName)
}

func getDamengRowInt(row map[string]interface{}, keys ...string) (int, bool) {
	for _, key := range keys {
		raw := getDamengRowString(row, key)
		if raw == "" {
			continue
		}
		var parsed int
		if _, err := fmt.Sscanf(raw, "%d", &parsed); err == nil {
			return parsed, true
		}
	}
	return 0, false
}

func normalizeDamengNullable(value string) string {
	switch strings.ToUpper(strings.TrimSpace(value)) {
	case "N", "NO", "FALSE", "0", "NOT NULL":
		return "NO"
	case "Y", "YES", "TRUE", "1", "NULL", "NULLABLE":
		return "YES"
	default:
		return strings.TrimSpace(value)
	}
}

func isDamengLengthQualifiedType(upperType string) bool {
	switch strings.TrimSpace(upperType) {
	case "CHAR", "NCHAR", "VARCHAR", "VARCHAR2", "NVARCHAR", "NVARCHAR2", "RAW", "BINARY", "VARBINARY":
		return true
	default:
		return strings.Contains(upperType, "CHARACTER")
	}
}

func formatDamengColumnType(row map[string]interface{}) string {
	dataType := getDamengRowString(row, "DATA_TYPE")
	if dataType == "" || strings.Contains(dataType, "(") {
		return dataType
	}

	upperType := strings.ToUpper(dataType)
	if isDamengLengthQualifiedType(upperType) {
		if charLength, ok := getDamengRowInt(row, "CHAR_LENGTH", "CHAR_COL_DECL_LENGTH"); ok && charLength > 0 {
			return fmt.Sprintf("%s(%d)", dataType, charLength)
		}
		if dataLength, ok := getDamengRowInt(row, "DATA_LENGTH"); ok && dataLength > 0 {
			return fmt.Sprintf("%s(%d)", dataType, dataLength)
		}
	}

	if strings.Contains(upperType, "NUMBER") || strings.Contains(upperType, "DECIMAL") || strings.Contains(upperType, "NUMERIC") {
		precision, hasPrecision := getDamengRowInt(row, "DATA_PRECISION", "NUMERIC_PRECISION")
		if hasPrecision && precision > 0 {
			scale, hasScale := getDamengRowInt(row, "DATA_SCALE", "NUMERIC_SCALE")
			if hasScale && scale > 0 {
				return fmt.Sprintf("%s(%d,%d)", dataType, precision, scale)
			}
			return fmt.Sprintf("%s(%d)", dataType, precision)
		}
	}

	return dataType
}

func buildDamengColumnDefinitions(data []map[string]interface{}) []connection.ColumnDefinition {
	columns := make([]connection.ColumnDefinition, 0, len(data))
	for _, row := range data {
		col := connection.ColumnDefinition{
			Name:     getDamengRowString(row, "COLUMN_NAME"),
			Type:     formatDamengColumnType(row),
			Nullable: normalizeDamengNullable(getDamengRowString(row, "NULLABLE")),
			Key:      getDamengRowString(row, "COLUMN_KEY"),
		}

		defaultValue := getDamengRowString(row, "DATA_DEFAULT")
		if defaultValue != "" {
			def := defaultValue
			col.Default = &def
		}

		columns = append(columns, col)
	}

	return columns
}
