package config

import (
	"fmt"
	"log"
	"os"
	"strconv"
	"strings"
	"system-altrak/internal/domain"
	"system-altrak/internal/migration"
	"system-altrak/pkg/utils"
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

func InitDB(cfg *Config) *gorm.DB {
	var db *gorm.DB
	var err error
	maxRetries := 5

	for i := 1; i <= maxRetries; i++ {
		if cfg.DBType == "sqlite" {
			db, err = gorm.Open(sqlite.Open(cfg.DBDSN), &gorm.Config{})
		} else {
			db, err = gorm.Open(mysql.Open(cfg.DBDSN), &gorm.Config{})
		}

		if err == nil {
			break
		}

		if i < maxRetries {
			log.Printf("⚠️ Hubungan ke database gagal (Percobaan %d/%d): %v. Mencoba lagi dalam 3 detik...", i, maxRetries, err)
			time.Sleep(3 * time.Second)
		} else {
			log.Fatalf("❌ Gagal terhubung ke database setelah %d percobaan: %v", maxRetries, err)
		}
	}

	// Evolution Protocol: Handle legacy schema transformations before AutoMigrate
	migration.ApplyLegacyTransforms(db)

	// MySQL/MariaDB specific: Disable FK checks during migration to avoid "Cannot change column" errors
	silentDB := db.Session(&gorm.Session{Logger: db.Logger.LogMode(0)})

	if cfg.DBType == "mysql" {
		silentDB.Exec("SET FOREIGN_KEY_CHECKS = 0")

		// Professional check before dropping FKs to avoid #1146 errors
		if silentDB.Migrator().HasTable("documents") {
			silentDB.Exec("ALTER TABLE documents DROP FOREIGN KEY IF EXISTS documents_ibfk_1")
		}
		if silentDB.Migrator().HasTable("service_requisition_checklists") {
			silentDB.Exec("ALTER TABLE service_requisition_checklists DROP FOREIGN KEY IF EXISTS uni_service_requisition_checklists_service_requisition_id")
		}
		if silentDB.Migrator().HasTable("refresh_tokens") {
			silentDB.Exec("ALTER TABLE refresh_tokens DROP FOREIGN KEY IF EXISTS refresh_tokens_ibfk_1")
		}
	}

	// Enterprise Protocol: Auto-migrate schema based on English models
	tableModels := []interface{}{
		&domain.User{},
		&domain.OperationalRecord{},
		&domain.ServiceRequisition{},
		&domain.ServiceRequisitionChecklist{},
		&domain.ServiceRequisitionEquipment{},
		&domain.Memorandum{},
		&domain.CreditLimit{},
		&domain.SerialSequence{},
		&domain.RefreshToken{},
		&domain.LoginAttempt{},
		&domain.RevokedToken{},
		&domain.ActivityLog{},
		&domain.AuditLog{},
		&domain.Role{},
		&domain.ServiceAuthorization{},
		&domain.CustomerProfile{},
		&domain.CustomerDecisionMaker{},
		&domain.CustomerEquipment{},
		&domain.CustomerAffiliation{},
		&domain.SystemSetting{},
		&domain.BackgroundJob{},
		&domain.MemoServiceParts{},
		&domain.MemoServicePartsChecklist{},
		&domain.MemoServicePartsEquipment{},
		&domain.MemoServiceKalibrasi{},
		&domain.MemoServiceKalibrasiChecklist{},
		&domain.MemoServiceKalibrasiEquipment{},
		&domain.RolePermission{},
	}

	skipMigrate := os.Getenv("SKIP_MIGRATIONS") == "true"
	if !skipMigrate {
		for _, model := range tableModels {
			_ = silentDB.AutoMigrate(model)
		}
		log.Println("Database Evolution: AutoMigrate completed.")
	} else {
		log.Println("Database Evolution: AutoMigrate skipped by request (SKIP_MIGRATIONS=true).")
	}

	seedDefaultSystemSettings(silentDB)
	seedDefaultRolePermissions(silentDB)
	synchronizeCustomerProfileDocumentNumbers(silentDB)

	if cfg.DBType == "mysql" {
		silentDB.Exec("SET FOREIGN_KEY_CHECKS = 1")
	}

	// Configure connection pooling settings
	if sqlDB, err := db.DB(); err == nil {
		sqlDB.SetMaxIdleConns(10)
		sqlDB.SetMaxOpenConns(100)
		sqlDB.SetConnMaxLifetime(time.Hour)
		sqlDB.SetConnMaxIdleTime(15 * time.Minute)
		log.Println("Database connection pool initialized: MaxOpenConns=100, MaxIdleConns=10, ConnMaxLifetime=1h, ConnMaxIdleTime=15m")
	} else {
		log.Printf("⚠️ Gagal mendapatkan sql.DB untuk konfigurasi connection pool: %v", err)
	}

	// Background healthcheck to ping connection and catch drops (like XAMPP sleep/restart)
	go func(d *gorm.DB) {
		ticker := time.NewTicker(30 * time.Second)
		defer ticker.Stop()
		for range ticker.C {
			sqlDB, err := d.DB()
			if err != nil {
				log.Printf("⚠️ Health Check: Database connection instance is unavailable: %v", err)
				continue
			}
			if err := sqlDB.Ping(); err != nil {
				log.Printf("⚠️ Health Check: Database Ping failed: %v. Database might be down or restarting.", err)
			}
		}
	}(db)

	registerAuditCallbacks(db)

	return db
}

// runSchemaEvolution ensures critical data integrity before AutoMigrate execution.
func runSchemaEvolution(db *gorm.DB) {
	m := db.Migrator()

	if !m.HasTable("users") {
		return
	}

	// Sanitize empty/invalid dates that crash Go SQLite driver
	db.Exec("UPDATE operational_records SET verified_at = NULL WHERE verified_at = '0000-00-00 00:00:00' OR verified_at = '' OR verified_at = 'now()'")
	db.Exec("UPDATE operational_records SET pso_date = NULL WHERE pso_date = '0000-00-00 00:00:00' OR pso_date = '' OR pso_date = 'now()'")
	log.Println("Database Evolution: Date sanitization completed.")

	// Sanitize empty password hashes to prevent migration failures
	db.Exec("UPDATE users SET password_hash = 'ACCOUNT_LOCKED_SECURE' WHERE password_hash IS NULL OR password_hash = ''")
	db.Exec("UPDATE users SET is_active = 0 WHERE password_hash = 'ACCOUNT_LOCKED_SECURE'")
	log.Println("Database Evolution: Pre-migration cleanup completed.")
}

func synchronizeCustomerProfileDocumentNumbers(db *gorm.DB) {
	m := db.Migrator()
	if !m.HasTable(&domain.CustomerProfile{}) || !m.HasColumn(&domain.CustomerProfile{}, "document_number") {
		return
	}

	var profiles []domain.CustomerProfile
	if err := db.Order("created_at asc, id asc").Find(&profiles).Error; err != nil {
		log.Printf("Database Evolution: Failed to load customer profiles for numbering sync: %v", err)
		return
	}

	maxNumber := 0
	for _, profile := range profiles {
		parsedNumber, ok := customerProfileSequenceNumber(profile.DocumentNumber)
		if !ok {
			continue
		}
		if parsedNumber > maxNumber {
			maxNumber = parsedNumber
		}
	}

	updated := false
	if err := db.Transaction(func(tx *gorm.DB) error {
		for _, profile := range profiles {
			if strings.TrimSpace(profile.DocumentNumber) != "" {
				continue
			}

			maxNumber++
			documentNumber := fmt.Sprintf("%03d", maxNumber)
			if err := tx.Model(&domain.CustomerProfile{}).Where("id = ?", profile.ID).Update("document_number", documentNumber).Error; err != nil {
				return err
			}
			updated = true
		}

		sequence := domain.SerialSequence{}
		seed := domain.SerialSequence{DocType: domain.CustomerProfileSequenceDocType, Year: 0, Month: 0}
		if err := tx.Where(seed).FirstOrCreate(&sequence, seed).Error; err != nil {
			return err
		}
		sequence.CurrentNumber = maxNumber
		return tx.Save(&sequence).Error
	}); err != nil {
		log.Printf("Database Evolution: Failed to synchronize customer profile numbering: %v", err)
		return
	}

	if updated {
		log.Println("Database Evolution: Customer Profile document numbers synchronized.")
	}
}

func seedDefaultSystemSettings(db *gorm.DB) {
	if db == nil {
		return
	}

	defaults := []domain.SystemSetting{
		{Key: "cp_format", Value: domain.CustomerProfileDefaultNumberFormat},
		{Key: "cp_reset", Value: domain.CustomerProfileDefaultResetMode},
		{Key: "ui_visual_transitions", Value: "true"},
		{Key: "ui_dynamic_sidebar", Value: "false"},
		{Key: "security_mfa_enabled", Value: "false"},
		{Key: "security_auto_logout", Value: "true"},
	}

	for _, defaultSetting := range defaults {
		var existing domain.SystemSetting
		tx := db.Where("`key` = ?", defaultSetting.Key).Limit(1).Find(&existing)
		if tx.Error != nil {
			log.Printf("Database Evolution: Failed to check default system setting %q: %v", defaultSetting.Key, tx.Error)
			continue
		}

		if tx.RowsAffected > 0 {
			if defaultSetting.Key == "cp_format" {
				trimmedValue := strings.TrimSpace(existing.Value)
				if trimmedValue == "" || trimmedValue == domain.CustomerProfileLegacyNumberFormat {
					if err := db.Model(&existing).Update("value", defaultSetting.Value).Error; err != nil {
						log.Printf("Database Evolution: Failed to migrate default system setting %q: %v", defaultSetting.Key, err)
					}
				}
			}
			continue
		}

		if err := db.Create(&defaultSetting).Error; err != nil {
			log.Printf("Database Evolution: Failed to seed default system setting %q: %v", defaultSetting.Key, err)
		}
	}
}

func customerProfileSequenceNumber(raw string) (int, bool) {
	normalized := strings.TrimSpace(raw)
	if normalized == "" {
		return 0, false
	}

	digits := strings.Builder{}
	foundDigit := false
	for _, r := range normalized {
		if r >= '0' && r <= '9' {
			digits.WriteRune(r)
			foundDigit = true
			continue
		}

		if foundDigit {
			break
		}
	}

	if !foundDigit {
		return 0, false
	}

	parsedNumber, err := strconv.Atoi(digits.String())
	if err != nil || parsedNumber <= 0 {
		return 0, false
	}

	return parsedNumber, true
}

func SeedDB(db *gorm.DB, cfg *Config) {
	var superAdmin domain.User
	err := db.Where("LOWER(username) = LOWER(?)", "super_spareparts").First(&superAdmin).Error
	if err != nil {
		initialPassword := cfg.SuperAdminPassword
		if initialPassword == "" {
			if cfg.Environment == "production" {
				log.Fatal("❌ FATAL: SUPERADMIN_PASSWORD wajib diatur pada environment production.")
			}

			generated, genErr := GenerateSecureSecret(32)
			if genErr != nil {
				log.Printf("❌ Gagal menghasilkan password SuperAdmin: %v", genErr)
				return
			}
			initialPassword = generated
			log.Printf("⚠️  SUPERADMIN_PASSWORD tidak disediakan. Password sementara dibuat otomatis (development only). Silakan set env SUPERADMIN_PASSWORD dan reset password segera.")
		}

		hashedPassword, hashErr := utils.HashPassword(initialPassword)
		if hashErr != nil {
			log.Printf("❌ Gagal hash password SuperAdmin: %v", hashErr)
			return
		}

		superAdmin = domain.User{
			FullName:     "SuperAdmin",
			Username:     "super_spareparts",
			PasswordHash: hashedPassword,
			Role:         "superadmin",
			IsActive:     true,
		}
		if createErr := db.Create(&superAdmin).Error; createErr != nil {
			log.Printf("❌ Gagal membuat SuperAdmin: %v", createErr)
			return
		}
		log.Printf("✅ SuperAdmin '%s' berhasil dibuat.", superAdmin.Username)
	} else {
		// Log presence but do NOT force update password_hash on every restart
		// as it would overwrite manual admin changes.
		log.Printf("ℹ️  SuperAdmin '%s' exists.", superAdmin.Username)
	}
}

func seedDefaultRolePermissions(db *gorm.DB) {
	if db == nil {
		return
	}

	modules := []string{
		"customer_profile",
		"iom_management",
		"isr_management",
		"po_status",
		"daily_reports",
		"audit_logs",
		"settings",
		"user_management",
	}

	roles := []string{"superadmin", "staff"}

	for _, role := range roles {
		for _, module := range modules {
			var existing domain.RolePermission
			tx := db.Where("role_name = ? AND module = ?", role, module).First(&existing)
			if tx.RowsAffected > 0 {
				continue
			}

			// Defaults
			isSuper := role == "superadmin"
			perm := domain.RolePermission{
				RoleName:  role,
				Module:    module,
				CanView:   true,
				CanCreate: isSuper,
				CanEdit:   isSuper,
				CanDelete: isSuper,
				CanExport: isSuper,
			}

			// Staff can create/edit some things by default
			if role == "staff" {
				switch module {
				case "customer_profile", "iom_management", "isr_management", "po_status":
					perm.CanCreate = true
					perm.CanEdit = true
					perm.CanExport = true
				case "audit_logs", "settings", "user_management":
					perm.CanView = false
				}
			}

			if err := db.Create(&perm).Error; err != nil {
				log.Printf("Database Evolution: Failed to seed role permission for %s:%s: %v", role, module, err)
			}
		}
	}
}