// Create utils/migration/tower_migration.go
package migrations

import (
    "fmt"
    "gorm.io/gorm"
)

func MigrateTowerConstraints(db *gorm.DB) error {
    // First, check if the constraint exists before trying to drop it
    var constraintExists bool
    err := db.Raw(`
        SELECT EXISTS (
            SELECT 1 FROM information_schema.table_constraints 
            WHERE constraint_name = 'uni_towers_tower_code' 
            AND table_name = 'towers'
        )
    `).Scan(&constraintExists).Error
    
    if err != nil {
        return fmt.Errorf("failed to check constraint existence: %w", err)
    }
    
    // Only drop the constraint if it exists
    if constraintExists {
        err = db.Exec("ALTER TABLE towers DROP CONSTRAINT uni_towers_tower_code").Error
        if err != nil {
            return fmt.Errorf("failed to drop constraint: %w", err)
        }
    }
    
    // Check if the new index exists
    var indexExists bool
    err = db.Raw(`
        SELECT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE indexname = 'idx_towers_tower_code' 
            AND tablename = 'towers'
        )
    `).Scan(&indexExists).Error
    
    if err != nil {
        return fmt.Errorf("failed to check index existence: %w", err)
    }
    
    // Create the new unique index if it doesn't exist
    if !indexExists {
        err = db.Exec("CREATE UNIQUE INDEX idx_towers_tower_code ON towers (tower_code)").Error
        if err != nil {
            return fmt.Errorf("failed to create unique index: %w", err)
        }
    }
    
    // Add image_urls column if it doesn't exist
    var columnExists bool
    err = db.Raw(`
        SELECT EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'towers' 
            AND column_name = 'image_urls'
        )
    `).Scan(&columnExists).Error
    
    if err != nil {
        return fmt.Errorf("failed to check column existence: %w", err)
    }
    
    if !columnExists {
        err = db.Exec("ALTER TABLE towers ADD COLUMN image_urls JSONB").Error
        if err != nil {
            return fmt.Errorf("failed to add image_urls column: %w", err)
        }
    }
    
    return nil
}