package database

import (
	"context"
	"fmt"
	"strings"
	"time"

	"payment/internal/config"
	"payment/internal/models"

	"github.com/golang-migrate/migrate/v4"
	migratePostgres "github.com/golang-migrate/migrate/v4/database/postgres"
	_ "github.com/golang-migrate/migrate/v4/source/file"
	"github.com/sirupsen/logrus"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// Initialize initializes the database connection
func Initialize(cfg *config.Config) (*gorm.DB, error) {
	// Build PostgreSQL DSN
	dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
		cfg.Database.Host,
		cfg.Database.Port,
		cfg.Database.User,
		cfg.Database.Password,
		cfg.Database.Name,
		cfg.Database.SSLMode,
	)

	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		Logger:                                   logger.Default.LogMode(logger.Info),
		DisableForeignKeyConstraintWhenMigrating: true,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}

	// Configure connection pool
	sqlDB, err := db.DB()
	if err != nil {
		return nil, fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	sqlDB.SetMaxOpenConns(cfg.Database.MaxOpenConns)
	sqlDB.SetMaxIdleConns(cfg.Database.MaxIdleConns)
	sqlDB.SetConnMaxLifetime(cfg.Database.ConnMaxLifetime)

	// Test connection
	if err := sqlDB.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	logrus.Info("Database connection established successfully")
	return db, nil
}

// Migrate runs database migrations
func Migrate(db *gorm.DB) error {
	// Get underlying SQL DB for raw queries
	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	// Helper function to safely drop constraint if it exists
	dropConstraintIfExists := func(tableName, constraintName string) {
		query := fmt.Sprintf(
			"ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s",
			tableName,
			constraintName,
		)
		_, err := sqlDB.Exec(query)
		if err != nil {
			logrus.Debugf("Could not drop constraint %s on %s: %v", constraintName, tableName, err)
		}
	}

	// Helper function to drop foreign key constraint if it exists
	dropForeignKeyIfExists := func(tableName, constraintName string) {
		query := fmt.Sprintf(
			"ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s",
			tableName,
			constraintName,
		)
		_, err := sqlDB.Exec(query)
		if err != nil {
			logrus.Debugf("Could not drop foreign key %s on %s: %v", constraintName, tableName, err)
		}
	}

	// Drop potentially conflicting constraints before migration
	// This handles cases where tables were created by Django with different constraint names
	logrus.Info("Cleaning up potentially conflicting constraints...")
	dropConstraintIfExists("clients", "uni_clients_email")
	dropConstraintIfExists("clients", "uni_clients_username")
	dropConstraintIfExists("clients", "clients_email_key")
	dropConstraintIfExists("clients", "clients_username_key")
	dropConstraintIfExists("clients", "clients_email_unique")
	dropConstraintIfExists("clients", "clients_username_unique")

	// Drop foreign key constraints that might prevent column type changes
	// Django creates these with different names, we'll drop them and let GORM recreate
	logrus.Info("Dropping foreign key constraints to allow column type changes...")
	
	// Drop known Django foreign key constraints
	dropForeignKeyIfExists("payment_credentials", "payment_credentials_client_id_66d55880_fk_clients_id")
	dropForeignKeyIfExists("internet_packages", "internet_packages_client_id_e5beabe1_fk_clients_id")
	dropForeignKeyIfExists("transactions", "transactions_client_id_1fb697bd_fk_clients_id")
	dropForeignKeyIfExists("transactions", "transactions_user_id_fk_users_id")
	dropForeignKeyIfExists("transactions", "transactions_package_id_fk_internet_packages_id")
	dropForeignKeyIfExists("users", "users_client_id_fk_clients_id")
	dropForeignKeyIfExists("mpesa_access_tokens", "mpesa_access_tokens_client_id_bad5e760_fk_clients_id")
	dropForeignKeyIfExists("access_tokens", "access_tokens_client_id_bad5e760_fk_clients_id") // Legacy table name support
	
	// Query to find ALL foreign key constraints referencing clients.id (PostgreSQL syntax)
	// This is more reliable than hardcoding constraint names
	fkQuery := `
		SELECT 
			tc.table_name, 
			tc.constraint_name
		FROM information_schema.table_constraints AS tc
		JOIN information_schema.key_column_usage AS kcu
			ON tc.constraint_schema = kcu.constraint_schema
			AND tc.constraint_name = kcu.constraint_name
		JOIN information_schema.constraint_column_usage AS ccu
			ON tc.constraint_schema = ccu.constraint_schema
			AND tc.constraint_name = ccu.constraint_name
		WHERE tc.constraint_type = 'FOREIGN KEY'
			AND ccu.table_name = 'clients'
			AND ccu.column_name = 'id'
			AND tc.table_schema = CURRENT_SCHEMA()
	`
	rows, err := sqlDB.Query(fkQuery)
	if err == nil {
		defer rows.Close()
		foundCount := 0
		for rows.Next() {
			var tableName, constraintName string
			if err := rows.Scan(&tableName, &constraintName); err == nil {
				logrus.Infof("Found foreign key %s on table %s, dropping...", constraintName, tableName)
				dropForeignKeyIfExists(tableName, constraintName)
				foundCount++
			}
		}
		if foundCount > 0 {
			logrus.Infof("Dropped %d foreign key constraint(s) referencing clients.id", foundCount)
		}
	} else {
		logrus.Warnf("Could not query for foreign keys: %v", err)
	}
	
	// Also try alternative query using pg_constraint (more direct and reliable)
	pgFkQuery := `
		SELECT 
			conrelid::regclass::text AS table_name,
			conname AS constraint_name
		FROM pg_constraint
		WHERE contype = 'f'
			AND confrelid = (
				SELECT oid 
				FROM pg_class 
				WHERE relname = 'clients' 
				AND relnamespace = (
					SELECT oid 
					FROM pg_namespace 
					WHERE nspname = CURRENT_SCHEMA()
				)
			)
	`
	pgRows, err := sqlDB.Query(pgFkQuery)
	if err == nil {
		defer pgRows.Close()
		pgFoundCount := 0
		for pgRows.Next() {
			var tableName, constraintName string
			if err := pgRows.Scan(&tableName, &constraintName); err == nil {
				// Remove schema prefix if present (e.g., "public.payment_credentials" -> "payment_credentials")
				if idx := strings.Index(tableName, "."); idx != -1 {
					tableName = tableName[idx+1:]
				}
				logrus.Infof("Found foreign key %s on table %s (via pg_constraint), dropping...", constraintName, tableName)
				dropForeignKeyIfExists(tableName, constraintName)
				pgFoundCount++
			}
		}
		if pgFoundCount > 0 {
			logrus.Infof("Dropped %d additional foreign key constraint(s) via pg_constraint query", pgFoundCount)
		}
	} else {
		logrus.Warnf("Could not query pg_constraint for foreign keys: %v", err)
	}
	
	// Verify all foreign keys are dropped by querying again
	logrus.Info("Verifying all foreign keys to clients.id have been dropped...")
	verifyQuery := `
		SELECT conname AS constraint_name
		FROM pg_constraint
		WHERE contype = 'f'
			AND confrelid = (
				SELECT oid 
				FROM pg_class 
				WHERE relname = 'clients' 
				AND relnamespace = (
					SELECT oid 
					FROM pg_namespace 
					WHERE nspname = CURRENT_SCHEMA()
				)
			)
	`
	verifyRows, err := sqlDB.Query(verifyQuery)
	if err == nil {
		defer verifyRows.Close()
		remaining := []string{}
		for verifyRows.Next() {
			var constraintName string
			if err := verifyRows.Scan(&constraintName); err == nil {
				remaining = append(remaining, constraintName)
			}
		}
		if len(remaining) > 0 {
			logrus.Warnf("Warning: %d foreign key constraint(s) still exist: %v", len(remaining), remaining)
			logrus.Warnf("Attempting to drop remaining constraints...")
			for _, constraintName := range remaining {
				// Try to find which table this constraint belongs to
				tableQuery := `
					SELECT conrelid::regclass::text
					FROM pg_constraint
					WHERE conname = $1
				`
				var tableName string
				if err := sqlDB.QueryRow(tableQuery, constraintName).Scan(&tableName); err == nil {
					if idx := strings.Index(tableName, "."); idx != -1 {
						tableName = tableName[idx+1:]
					}
					logrus.Infof("Dropping remaining constraint %s from table %s", constraintName, tableName)
					dropForeignKeyIfExists(tableName, constraintName)
				}
			}
		} else {
			logrus.Info("All foreign key constraints to clients.id have been successfully dropped")
		}
	}

	// Auto-migrate models
	// Migrate each model individually to handle errors gracefully
	modelsToMigrate := []interface{}{
		&models.Client{},
		&models.User{},
		&models.PaymentCredentials{},
		&models.InternetPackage{},
		&models.Transaction{},
		&models.MpesaAccessToken{},
	}

	var migrationErrors []error
	for _, model := range modelsToMigrate {
		if err := db.AutoMigrate(model); err != nil {
			// Check if error is about missing constraint (which we can ignore)
			errStr := err.Error()
			if strings.Contains(errStr, "does not exist") && strings.Contains(errStr, "constraint") {
				logrus.Warnf("Migration warning (constraint issue, continuing): %v", err)
				continue
			}
			// Check if error is about foreign key constraint during column type change
			// This can happen when Django created tables with UUID type and GORM wants char(36)
			if strings.Contains(errStr, "foreign key constraint") && strings.Contains(errStr, "cannot be implemented") {
				logrus.Warnf("Migration warning (foreign key type issue, this is expected with existing Django tables): %v", err)
				// Try to drop the problematic foreign key and retry
				// Extract table name from error if possible, or try common ones
				if strings.Contains(errStr, "payment_credentials") {
					dropForeignKeyIfExists("payment_credentials", "payment_credentials_client_id_66d55880_fk_clients_id")
					// Retry migration for this model
					if retryErr := db.AutoMigrate(model); retryErr != nil {
						logrus.Warnf("Retry migration still failed, but continuing: %v", retryErr)
					}
					continue
				}
				if strings.Contains(errStr, "internet_packages") {
					dropForeignKeyIfExists("internet_packages", "internet_packages_client_id_e5beabe1_fk_clients_id")
					if retryErr := db.AutoMigrate(model); retryErr != nil {
						logrus.Warnf("Retry migration still failed, but continuing: %v", retryErr)
					}
					continue
				}
				if strings.Contains(errStr, "transactions") {
					dropForeignKeyIfExists("transactions", "transactions_client_id_1fb697bd_fk_clients_id")
					dropForeignKeyIfExists("transactions", "transactions_user_id_fk_users_id")
					dropForeignKeyIfExists("transactions", "transactions_package_id_fk_internet_packages_id")
					if retryErr := db.AutoMigrate(model); retryErr != nil {
						logrus.Warnf("Retry migration still failed, but continuing: %v", retryErr)
					}
					continue
				}
				if strings.Contains(errStr, "users") {
					dropForeignKeyIfExists("users", "users_client_id_fk_clients_id")
					if retryErr := db.AutoMigrate(model); retryErr != nil {
						logrus.Warnf("Retry migration still failed, but continuing: %v", retryErr)
					}
					continue
				}
				if strings.Contains(errStr, "access_tokens") || strings.Contains(errStr, "mpesa_access_tokens") {
					dropForeignKeyIfExists("mpesa_access_tokens", "mpesa_access_tokens_client_id_bad5e760_fk_clients_id")
					dropForeignKeyIfExists("access_tokens", "access_tokens_client_id_bad5e760_fk_clients_id") // Legacy support
					if retryErr := db.AutoMigrate(model); retryErr != nil {
						logrus.Warnf("Retry migration still failed, but continuing: %v", retryErr)
					}
					continue
				}
				// If we can't identify the table, log and continue
				logrus.Warnf("Foreign key constraint issue (continuing): %v", err)
				continue
			}
			migrationErrors = append(migrationErrors, err)
			logrus.Errorf("Failed to migrate model: %v", err)
		}
	}

	// If we have critical errors (not just constraint issues), return error
	if len(migrationErrors) > 0 {
		// Check if all errors are just constraint-related
		allConstraintErrors := true
		for _, err := range migrationErrors {
			errStr := err.Error()
			if !(strings.Contains(errStr, "does not exist") && strings.Contains(errStr, "constraint")) {
				allConstraintErrors = false
				break
			}
		}
		if !allConstraintErrors {
			return fmt.Errorf("failed to auto-migrate models: %v", migrationErrors)
		}
	}

	logrus.Info("Database migrations completed successfully")
	return nil
}

// MigrateWithFiles runs migrations from SQL files
func MigrateWithFiles(db *gorm.DB, migrationsPath string) error {
	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	driver, err := migratePostgres.WithInstance(sqlDB, &migratePostgres.Config{})
	if err != nil {
		return fmt.Errorf("failed to create postgres driver: %w", err)
	}

	m, err := migrate.NewWithDatabaseInstance(
		fmt.Sprintf("file://%s", migrationsPath),
		"postgres",
		driver,
	)
	if err != nil {
		return fmt.Errorf("failed to create migrate instance: %w", err)
	}

	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("failed to run migrations: %w", err)
	}

	logrus.Info("Database migrations from files completed successfully")
	return nil
}

// HealthCheck checks database health
func HealthCheck(db *gorm.DB) error {
	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	if err := sqlDB.PingContext(ctx); err != nil {
		return fmt.Errorf("database health check failed: %w", err)
	}

	return nil
}