package main

import (
	"context"
	"fmt"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"

	"sql-platform/config"
	"sql-platform/internal/handler"
	"sql-platform/internal/middleware"
	"sql-platform/internal/model"
	"sql-platform/router"

	"github.com/gin-gonic/gin"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

// AppVersion 与前端 package.json 版本保持同步
const AppVersion = "0.4.0"

func main() {
	// Load config
	config.Init()

	if !config.C.Debug {
		gin.SetMode(gin.ReleaseMode)
	}

	// Connect to SQLite
	//
	// DSN 必须显式带 pragma，否则默认配置在高并发下会出问题：
	//   裸 DSN + 默认 journal 模式下，写事务与读事务互相阻塞，
	//   并发写会直接返回 "database is locked"（SQLITE_BUSY），
	//   表现为「点保存失败 / 偶尔 500」，而且不会卡住所以极难定位。
	// 这里用业界通用组合：
	//   _busy_timeout=5000  遇到锁时等 5 秒再报错，而不是立刻失败
	//   _journal_mode=WAL   读写不互斥（写不阻塞读），并发请求不再排队
	//   _synchronous=NORMAL WAL 下的常规安全级别，比 FULL 快很多
	//   _foreign_keys=on    外键约束（SQLite 默认关闭，容易埋脏数据）
	dsn := config.C.DBPath
	if !strings.Contains(dsn, "?") {
		dsn += "?_busy_timeout=5000&_journal_mode=WAL&_synchronous=NORMAL&_foreign_keys=on"
	}
	db, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	// 连接池上限：SQLite 同一时刻只允许一个写者，连接开太多只会加剧锁竞争。
	// 4 个连接足够支撑日常并发（读多写少），并把锁等待控制在可预期范围内。
	if sqlDB, dbErr := db.DB(); dbErr == nil {
		sqlDB.SetMaxOpenConns(4)
		sqlDB.SetMaxIdleConns(4)
		sqlDB.SetConnMaxLifetime(0)
	}
	model.DB = db

	// Auto-migrate (add missing tables/columns, won\'t drop existing data)
	_ = db.AutoMigrate(
		&model.User{},
		&model.Instance{},
		&model.Ticket{},
		&model.TicketApproval{},
		&model.TicketSQLItem{},
		&model.AuditRule{},
		&model.AuditLog{},
		&model.QueryHistory{},
		&model.WorkflowDefinition{},
		&model.WorkflowStep{},
		&model.WorkflowVersion{},
		&model.WorkflowExecutionLog{},
		&model.Announcement{},
		&model.InstanceGroup{},
		&model.InstanceGroupMember{},
		&model.LDAPConfig{},
		&model.NotificationChannel{},
		&model.NotificationTemplate{},
		&model.SystemSetting{},
		&model.OperationLog{},
		&model.MaskingRule{},
		&model.Backup{},
		&model.SchemaRevision{},
		&model.SlowLog{},
		&model.ScheduledTask{},
		&model.ScheduledTaskLog{},
		&model.UserGroup{},
		&model.RefreshToken{},
	)

	// Seed default data (idempotent): users, workflow, settings
	seedAll(db)

	// 治理默认 JWT 密钥（生产模式自动生成并持久化）
	config.EnsureJWTSecret()

	// Setup Gin
	//
	// 用 gin.New() 而不是 gin.Default()：Default 自带的是文本格式的 gin.Logger()，
	// 这里换成结构化 JSON 访问日志（middleware.AccessLog），便于 Promtail→Loki 按字段过滤，
	// 同时跳过健康检查/静态资源，避免探针轮询把日志量刷满。
	// Recovery 必须显式加回来（Default 里包含它），否则 panic 会打挂整个进程。
	r := gin.New()
	r.Use(middleware.AccessLog(), gin.Recovery(), middleware.CORSMiddleware())

	// Health check
	r.GET("/health", func(c *gin.Context) {
		c.JSON(200, gin.H{"status": "ok", "version": AppVersion, "backend": "go"})
	})

	// Routes
	router.Setup(r)

	// 后台保留期清理：启动 2 分钟后跑第一次，之后每 24h 一次。
	// 解决「只配了保留天数但没人执行」的问题 —— 之前审计日志/查询历史
	// 只能靠管理员手动点「立即清理」，表会一直涨、SQLite 文件也不会缩。
	handler.StartCleanupScheduler(context.Background())

	// 单镜像部署:直接托管前端产物(DIST_DIR 为空则跳过,开发模式由 vite 独立提供)
	if distDir := os.Getenv("DIST_DIR"); distDir != "" {
		indexPath := filepath.Join(distDir, "index.html")
		if _, err := os.Stat(indexPath); err == nil {
			r.NoRoute(func(c *gin.Context) {
				p := c.Request.URL.Path
				if strings.HasPrefix(p, "/api") {
					c.JSON(http.StatusNotFound, gin.H{"detail": "Not found"})
					return
				}
				// 目录穿越防护后按文件直出(assets/db-logos/favicon),其余回落 SPA index
				full := filepath.Join(distDir, filepath.Clean("/"+p))
				if strings.HasPrefix(full, filepath.Clean(distDir)) {
					if info, err := os.Stat(full); err == nil && !info.IsDir() {
						c.File(full)
						return
					}
				}
				c.File(indexPath)
			})
			log.Printf("serving frontend from %s", distDir)
		} else {
			log.Printf("DIST_DIR=%s 无 index.html,跳过静态托管", distDir)
		}
	}

	// Start server
	addr := fmt.Sprintf(":%s", config.C.Port)
	log.Printf("SQL Platform Go backend starting on %s", addr)
	if err := r.Run(addr); err != nil {
		log.Fatalf("Failed to start server: %v", err)
	}
}

func seedDefaultSettings(db *gorm.DB) {
	defaults := []model.SystemSetting{
		// ── 查询控制 ──
		{Key: "query_limit_max", Value: "5000", ValueType: "number", Group: "query", Label: "LIMIT 上限", Desc: strPtr("SELECT 查询返回的最大行数（默认 5000），超过自动截断；对 MySQL / PostgreSQL / Redis / MongoDB 统一生效"), DefaultValue: strPtr("5000"), SortOrder: 1},
		{Key: "query_timeout", Value: "30", ValueType: "number", Group: "query", Label: "查询超时(秒)", Desc: strPtr("单次查询最长执行时间，超时自动终止"), DefaultValue: strPtr("30"), SortOrder: 2},
		{Key: "query_auto_limit", Value: "true", ValueType: "boolean", Group: "query", Label: "自动加 LIMIT", Desc: strPtr("用户未写 LIMIT 时自动追加，防止全表扫描"), DefaultValue: strPtr("true"), SortOrder: 3},
		{Key: "query_max_rows_examined", Value: "1000000", ValueType: "number", Group: "query", Label: "最大扫描行数", Desc: strPtr("单次查询允许扫描的最大行数"), DefaultValue: strPtr("1000000"), SortOrder: 4},
		{Key: "query_dangerous_pattern", Value: "true", ValueType: "boolean", Group: "query", Label: "危险模式检测", Desc: strPtr("检测不带 WHERE 的 DELETE/UPDATE 等高危操作"), DefaultValue: strPtr("true"), SortOrder: 5},

		// ── 安全策略 ──
		{Key: "login_max_attempts", Value: "5", ValueType: "number", Group: "security", Label: "登录失败锁定次数", Desc: strPtr("连续登录失败达到此次数后锁定账号"), DefaultValue: strPtr("5"), SortOrder: 1},
		{Key: "login_lockout_minutes", Value: "30", ValueType: "number", Group: "security", Label: "锁定时长(分钟)", Desc: strPtr("账号被锁定后的持续时长"), DefaultValue: strPtr("30"), SortOrder: 2},
		{Key: "password_min_length", Value: "8", ValueType: "number", Group: "security", Label: "密码最小长度", Desc: strPtr("用户密码的最短字符数"), DefaultValue: strPtr("8"), SortOrder: 3},
		{Key: "password_require_complex", Value: "true", ValueType: "boolean", Group: "security", Label: "密码复杂度", Desc: strPtr("要求包含大小写字母、数字和特殊字符"), DefaultValue: strPtr("true"), SortOrder: 4},
		{Key: "session_timeout_hours", Value: "24", ValueType: "number", Group: "security", Label: "会话超时(小时)", Desc: strPtr("JWT Token 有效期"), DefaultValue: strPtr("24"), SortOrder: 5},

		// ── 数据脱敏 ──
		{Key: "data_masking_enabled", Value: "true", ValueType: "boolean", Group: "data_masking", Label: "启用数据脱敏", Desc: strPtr("启用后，查询结果中的敏感字段将被替换为 *"), DefaultValue: strPtr("true"), SortOrder: 1},
		{Key: "data_masking_sensitive_fields", Value: "mobile,concat_mobile,email_alias,email,password,ga_key,private_key,secret,secret_key,key_snow,telephone,username,real_name,token,two_factor_secret,content,phone", ValueType: "string", Group: "data_masking", Label: "默认敏感字段列表", Desc: strPtr("全局敏感字段，逗号分隔。匹配列名（不区分大小写）"), DefaultValue: strPtr("mobile,concat_mobile,email_alias,email,password,ga_key,private_key,secret,secret_key,key_snow,telephone,username,real_name,token,two_factor_secret,content,phone"), SortOrder: 2},

		// ── 日志清理 ──
		{Key: "ticket_retention_days", Value: "180", ValueType: "number", Group: "cleanup", Label: "工单保留天数", Desc: strPtr("超过此天数的已完成/已关闭工单将被清理"), DefaultValue: strPtr("180"), SortOrder: 1},
		{Key: "audit_log_retention_days", Value: "365", ValueType: "number", Group: "cleanup", Label: "审计日志保留天数", Desc: strPtr("超过此天数的审计日志将被清理"), DefaultValue: strPtr("365"), SortOrder: 2},
		{Key: "query_history_retention_days", Value: "90", ValueType: "number", Group: "cleanup", Label: "查询历史保留天数", Desc: strPtr("超过此天数的查询历史将被清理"), DefaultValue: strPtr("90"), SortOrder: 3},
		{Key: "slow_log_retention_days", Value: "180", ValueType: "number", Group: "cleanup", Label: "慢日志保留天数", Desc: strPtr("超过此天数的慢查询日志将被清理"), DefaultValue: strPtr("180"), SortOrder: 4},
		{Key: "operation_log_retention_days", Value: "365", ValueType: "number", Group: "cleanup", Label: "操作日志保留天数", Desc: strPtr("超过此天数的操作日志将被清理"), DefaultValue: strPtr("365"), SortOrder: 5},
		{Key: "workflow_log_retention_days", Value: "180", ValueType: "number", Group: "cleanup", Label: "工单流程日志保留天数", Desc: strPtr("超过此天数的工单流程执行日志将被清理"), DefaultValue: strPtr("180"), SortOrder: 6},
		{Key: "scheduled_log_retention_days", Value: "180", ValueType: "number", Group: "cleanup", Label: "定时任务日志保留天数", Desc: strPtr("超过此天数的定时任务执行日志将被清理"), DefaultValue: strPtr("180"), SortOrder: 7},
		{Key: "cleanup_auto_enabled", Value: "true", ValueType: "boolean", Group: "cleanup", Label: "自动清理", Desc: strPtr("每天自动按上面的保留天数清理一次；关闭后仍可在本页点「立即清理」"), DefaultValue: strPtr("true"), SortOrder: 99},

		// ── 执行器 ──
		{Key: "executor_timeout", Value: "300", ValueType: "number", Group: "executor", Label: "执行超时(秒)", Desc: strPtr("工单中 SQL 语句执行的最大等待时间"), DefaultValue: strPtr("300"), SortOrder: 1},
		{Key: "executor_batch_size", Value: "100", ValueType: "number", Group: "executor", Label: "批量执行大小", Desc: strPtr("DML 批量执行时每批的行数"), DefaultValue: strPtr("100"), SortOrder: 2},
		{Key: "executor_dry_run", Value: "true", ValueType: "boolean", Group: "executor", Label: "执行前预检查", Desc: strPtr("执行前先 EXPLAIN 验证 SQL 语法正确性"), DefaultValue: strPtr("true"), SortOrder: 3},
		{Key: "executor_backup", Value: "true", ValueType: "boolean", Group: "executor", Label: "执行前备份", Desc: strPtr("DML 执行前自动备份受影响的数据"), DefaultValue: strPtr("true"), SortOrder: 4},

		// ── Ghost/OSC (gh-ost 在线DDL) ──
		{Key: "ghost_enabled", Value: "false", ValueType: "boolean", Group: "ghost", Label: "启用 gh-ost", Desc: strPtr("启用 gh-ost 在线 DDL 变更工具"), DefaultValue: strPtr("false"), SortOrder: 1},
		{Key: "ghost_path", Value: "gh-ost", ValueType: "string", Group: "ghost", Label: "gh-ost 路径", Desc: strPtr("gh-ost 二进制文件路径"), DefaultValue: strPtr("gh-ost"), SortOrder: 2},
		{Key: "ghost_default_mode", Value: "dry_run", ValueType: "string", Group: "ghost", Label: "默认执行模式", Desc: strPtr("dry_run=预演 / execute=直接执行"), DefaultValue: strPtr("dry_run"), SortOrder: 3},
		{Key: "ghost_osc_priority", Value: "native_first", ValueType: "string", Group: "ghost", Label: "OSC 优先级", Desc: strPtr("native_first / ghost_first / ghost_only"), DefaultValue: strPtr("native_first"), SortOrder: 4},
		{Key: "ghost_env_mode", Value: "development", ValueType: "string", Group: "ghost", Label: "环境模式", Desc: strPtr("development / staging / production"), DefaultValue: strPtr("development"), SortOrder: 5},
		{Key: "ghost_cloud_type", Value: "self_hosted", ValueType: "string", Group: "ghost", Label: "部署类型", Desc: strPtr("self_hosted / rds / cloud"), DefaultValue: strPtr("self_hosted"), SortOrder: 6},
		{Key: "ghost_auto_trigger_rows", Value: "10", ValueType: "number", Group: "ghost", Label: "自动触发行数(万)", Desc: strPtr("表行数超过此阈值(万)自动推荐 OSC"), DefaultValue: strPtr("10"), SortOrder: 7},
		{Key: "ghost_auto_trigger_ddl", Value: "ADD COLUMN,MODIFY COLUMN,ADD INDEX", ValueType: "string", Group: "ghost", Label: "自动触发DDL类型", Desc: strPtr("自动推荐 OSC 的 DDL 操作类型列表"), DefaultValue: strPtr("ADD COLUMN,MODIFY COLUMN,ADD INDEX"), SortOrder: 8},
		{Key: "ghost_default_chunk_size", Value: "1000", ValueType: "number", Group: "ghost", Label: "默认 chunk 大小", Desc: strPtr("每次拷贝的行数"), DefaultValue: strPtr("1000"), SortOrder: 9},
		{Key: "ghost_max_load", Value: "Threads_running=25", ValueType: "string", Group: "ghost", Label: "最大负载阈值", Desc: strPtr("超过此负载暂停拷贝"), DefaultValue: strPtr("Threads_running=25"), SortOrder: 10},
		{Key: "ghost_critical_load", Value: "Threads_running=50", ValueType: "string", Group: "ghost", Label: "临界负载阈值", Desc: strPtr("超过此负载立即停止"), DefaultValue: strPtr("Threads_running=50"), SortOrder: 11},
		{Key: "ghost_allow_on_master", Value: "true", ValueType: "boolean", Group: "ghost", Label: "允许在主库执行", Desc: strPtr("是否允许直接在主库运行 gh-ost"), DefaultValue: strPtr("true"), SortOrder: 12},
		{Key: "ghost_max_lag_millis", Value: "1500", ValueType: "number", Group: "ghost", Label: "最大复制延迟(ms)", Desc: strPtr("从库复制延迟超过此值暂停拷贝"), DefaultValue: strPtr("1500"), SortOrder: 13},
	}
	for _, s := range defaults {
		var existing model.SystemSetting
		if db.Where("`key` = ?", s.Key).First(&existing).Error != nil {
			db.Create(&s)
		}
	}
}

func strPtr(s string) *string {
	return &s
}