whatsapp_web_groups/helpdesk_extras/migrations/18.0.1.0.12/pre-migration.py

# -*- coding: utf-8 -*-
"""
Pre-migration script to prepare database schema for template integration

This script runs BEFORE the module update to:
1. Make template_id nullable
2. Add workflow_template_id column
3. Prepare for constraint changes
"""
import logging

_logger = logging.getLogger(__name__)


def migrate(cr, version):
    """Prepare database schema for template integration"""
    try:
        _logger.info("=" * 80)
        _logger.info("Starting pre-migration: Preparing database schema")
        _logger.info("=" * 80)
        
        # 1. Check if workflow_template_id column exists
        cr.execute("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = 'helpdesk_template_field' 
            AND column_name = 'workflow_template_id'
        """)
        workflow_col_exists = cr.fetchone()
        
        if not workflow_col_exists:
            _logger.info("Adding workflow_template_id column to helpdesk_template_field...")
            cr.execute("""
                ALTER TABLE helpdesk_template_field 
                ADD COLUMN workflow_template_id INTEGER
            """)
            _logger.info("✅ Column workflow_template_id added")
        else:
            _logger.info("Column workflow_template_id already exists")
        
        # 2. Add foreign key constraint if it doesn't exist
        cr.execute("""
            SELECT constraint_name 
            FROM information_schema.table_constraints 
            WHERE table_name = 'helpdesk_template_field' 
            AND constraint_name = 'helpdesk_template_field_workflow_template_id_fkey'
        """)
        fk_exists = cr.fetchone()
        
        if not fk_exists:
            _logger.info("Adding foreign key constraint for workflow_template_id...")
            cr.execute("""
                ALTER TABLE helpdesk_template_field 
                ADD CONSTRAINT helpdesk_template_field_workflow_template_id_fkey 
                FOREIGN KEY (workflow_template_id) 
                REFERENCES helpdesk_workflow_template(id) 
                ON DELETE CASCADE
            """)
            _logger.info("✅ Foreign key constraint added")
        else:
            _logger.info("Foreign key constraint already exists")
        
        # 3. Make template_id nullable (if it's not already)
        cr.execute("""
            SELECT is_nullable 
            FROM information_schema.columns 
            WHERE table_name = 'helpdesk_template_field' 
            AND column_name = 'template_id'
        """)
        template_nullable = cr.fetchone()
        
        if template_nullable and template_nullable[0] == 'NO':
            _logger.info("Making template_id nullable...")
            # First, drop the NOT NULL constraint by altering the column
            cr.execute("""
                ALTER TABLE helpdesk_template_field 
                ALTER COLUMN template_id DROP NOT NULL
            """)
            _logger.info("✅ template_id is now nullable")
        else:
            _logger.info("template_id is already nullable")
        
        # 4. Add index on workflow_template_id for performance
        cr.execute("""
            SELECT indexname 
            FROM pg_indexes 
            WHERE tablename = 'helpdesk_template_field' 
            AND indexname = 'helpdesk_template_field_workflow_template_id_index'
        """)
        index_exists = cr.fetchone()
        
        if not index_exists:
            _logger.info("Adding index on workflow_template_id...")
            cr.execute("""
                CREATE INDEX helpdesk_template_field_workflow_template_id_index 
                ON helpdesk_template_field(workflow_template_id)
            """)
            _logger.info("✅ Index added")
        else:
            _logger.info("Index already exists")
        
        # 5. Drop ALL old UNIQUE constraints on helpdesk_template_field (they don't work well with NULLs)
        # Odoo names constraints as: {table_name}_{constraint_name}
        # We need to find and drop any UNIQUE constraints that involve template_id or workflow_template_id
        cr.execute("""
            SELECT DISTINCT tc.constraint_name
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu 
                ON tc.constraint_name = kcu.constraint_name
                AND tc.table_schema = kcu.table_schema
            WHERE tc.table_name = 'helpdesk_template_field' 
            AND tc.constraint_type = 'UNIQUE'
            AND (kcu.column_name IN ('template_id', 'workflow_template_id', 'field_id'))
        """)
        old_constraints = cr.fetchall()
        
        if old_constraints:
            _logger.info(f"Found {len(old_constraints)} UNIQUE constraint(s) to drop")
            for constraint in old_constraints:
                constraint_name = constraint[0]
                _logger.info(f"Dropping old constraint: {constraint_name}...")
                try:
                    # Use format string with proper escaping
                    cr.execute(f'ALTER TABLE helpdesk_template_field DROP CONSTRAINT IF EXISTS "{constraint_name}"')
                    _logger.info(f"✅ Dropped constraint: {constraint_name}")
                except Exception as e:
                    _logger.warning(f"Could not drop constraint {constraint_name}: {e}")
                    # Try alternative method
                    try:
                        cr.execute("""
                            ALTER TABLE helpdesk_template_field 
                            DROP CONSTRAINT IF EXISTS %s
                        """, (constraint_name,))
                        _logger.info(f"✅ Dropped constraint (method 2): {constraint_name}")
                    except Exception as e2:
                        _logger.error(f"Failed to drop constraint {constraint_name} with both methods: {e2}")
        else:
            _logger.info("No old UNIQUE constraints found to drop")
        
        # 6. Add partial unique indexes (work correctly with NULLs)
        # Index for template_id + field_id (only when template_id IS NOT NULL)
        cr.execute("""
            SELECT indexname 
            FROM pg_indexes 
            WHERE tablename = 'helpdesk_template_field' 
            AND indexname = 'helpdesk_template_field_unique_template_field_idx'
        """)
        template_idx_exists = cr.fetchone()
        
        if not template_idx_exists:
            _logger.info("Adding partial unique index for template_id + field_id...")
            cr.execute("""
                CREATE UNIQUE INDEX helpdesk_template_field_unique_template_field_idx 
                ON helpdesk_template_field(template_id, field_id)
                WHERE template_id IS NOT NULL
            """)
            _logger.info("✅ Partial unique index for template_id added")
        else:
            _logger.info("Partial unique index for template_id already exists")
        
        # Index for workflow_template_id + field_id (only when workflow_template_id IS NOT NULL)
        cr.execute("""
            SELECT indexname 
            FROM pg_indexes 
            WHERE tablename = 'helpdesk_template_field' 
            AND indexname = 'helpdesk_template_field_unique_workflow_template_field_idx'
        """)
        workflow_idx_exists = cr.fetchone()
        
        if not workflow_idx_exists:
            _logger.info("Adding partial unique index for workflow_template_id + field_id...")
            cr.execute("""
                CREATE UNIQUE INDEX helpdesk_template_field_unique_workflow_template_field_idx 
                ON helpdesk_template_field(workflow_template_id, field_id)
                WHERE workflow_template_id IS NOT NULL
            """)
            _logger.info("✅ Partial unique index for workflow_template_id added")
        else:
            _logger.info("Partial unique index for workflow_template_id already exists")
        
        # 7. Add CHECK constraint to ensure only one of template_id or workflow_template_id is set
        cr.execute("""
            SELECT constraint_name 
            FROM information_schema.table_constraints 
            WHERE table_name = 'helpdesk_template_field' 
            AND constraint_name = 'helpdesk_template_field_check_template_or_workflow'
        """)
        check_exists = cr.fetchone()
        
        if not check_exists:
            _logger.info("Adding CHECK constraint for template_or_workflow...")
            cr.execute("""
                ALTER TABLE helpdesk_template_field 
                ADD CONSTRAINT helpdesk_template_field_check_template_or_workflow
                CHECK ((template_id IS NOT NULL AND workflow_template_id IS NULL) 
                       OR (template_id IS NULL AND workflow_template_id IS NOT NULL))
            """)
            _logger.info("✅ CHECK constraint added")
        else:
            _logger.info("CHECK constraint already exists")
        
        cr.commit()
        _logger.info("=" * 80)
        _logger.info("✅ Pre-migration completed successfully")
        _logger.info("=" * 80)
        
    except Exception as e:
        _logger.error(f"❌ Error in pre-migration: {e}", exc_info=True)
        try:
            cr.rollback()
        except Exception:
            pass  # Ignore rollback errors
        # Raise to prevent module update if pre-migration fails
        # This ensures database is in consistent state
        raise