# -*- 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. 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