package database

import (
	"fmt"
	"strings"

	"gorm.io/gorm"
)

// DriverName 从 GORM 连接获取驱动名（sqlite / mysql / postgres）// Logan.Li
func DriverName(db *gorm.DB) string {
	if db == nil || db.Dialector == nil {
		return "mysql"
	}
	name := strings.ToLower(db.Dialector.Name())
	switch {
	case strings.Contains(name, "sqlite"):
		return "sqlite"
	case strings.Contains(name, "postgres"):
		return "postgres"
	default:
		return "mysql"
	}
}

// HourBucketExpr 按小时分桶表达式（跨 SQLite / MySQL）// Logan.Li
func HourBucketExpr(driver string) string {
	switch driver {
	case "sqlite":
		return "cast(strftime('%s', report_time) as integer) / 3600"
	default:
		return "UNIX_TIMESTAMP(report_time) DIV 3600"
	}
}

// CleanupAgentReportsSQL 清理 7 天前 Agent 上报 // Logan.Li
func CleanupAgentReportsSQL(driver string) string {
	switch driver {
	case "sqlite":
		return "DELETE FROM monitor_agent_reports WHERE report_time < datetime('now', '-7 days')"
	default:
		return "DELETE FROM monitor_agent_reports WHERE report_time < DATE_SUB(NOW(), INTERVAL 7 DAY)"
	}
}

// CleanupSNMPMetricsSQL 清理超期 SNMP 采集指标 // Logan.Li
func CleanupSNMPMetricsSQL(driver string, retentionDays int) string {
	if retentionDays <= 0 {
		retentionDays = 7
	}
	switch driver {
	case "sqlite":
		return fmt.Sprintf(
			"DELETE FROM monitor_snmp_samples WHERE timestamp < CAST(strftime('%%s', 'now', '-%d days') AS INTEGER)",
			retentionDays,
		)
	default:
		return fmt.Sprintf(
			"DELETE FROM monitor_snmp_samples WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))",
			retentionDays,
		)
	}
}

// IndexExists 判断某表上是否已存在指定名称的索引（跨 SQLite / MySQL / PostgreSQL）。
// 供版本化迁移做幂等判断：新库基线已由模型（uniqueIndex 标签）建出同名索引时，
// 迁移不应再建一次，否则 MySQL 会以 1061 重复键名中断整个 migrate up。 // Logan.Li
func IndexExists(db *gorm.DB, table string, index string) (bool, error) {
	var (
		query string
		count int64
	)
	switch DriverName(db) {
	case "sqlite":
		query = "SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND tbl_name = ? AND name = ?"
	case "postgres":
		query = "SELECT COUNT(*) FROM pg_indexes WHERE schemaname = current_schema() AND tablename = ? AND indexname = ?"
	default:
		query = "SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?"
	}
	if err := db.Raw(query, table, index).Scan(&count).Error; err != nil {
		return false, fmt.Errorf("检查索引 %s.%s 是否存在失败: %w", table, index, err)
	}
	return count > 0, nil
}