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 old UNIQUE constraints if they exist (they don't work well with NULLs)
        cr.execute("""
            SELECT constraint_name 
            FROM information_schema.table_constraints 
            WHERE table_name = 'helpdesk_template_field' 
            AND constraint_name IN ('helpdesk_template_field_unique_template_field', 
                                     'helpdesk_template_field_unique_workflow_template_field')
        """)
        old_constraints = cr.fetchall()
        
        for constraint in old_constraints:
            constraint_name = constraint[0]
            _logger.info(f"Dropping old constraint: {constraint_name}...")
            try:
                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}")
        
        # 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