# Generated by Django 5.2 on 2025-06-20 16:33

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('api', '0039_persistent_agent_models'),
    ]

    operations = [
        # Enable PostgreSQL extensions (gracefully handles when already enabled)
        migrations.RunSQL(
            "CREATE EXTENSION IF NOT EXISTS citext;",
            reverse_sql="-- Extension citext is kept for other uses"
        ),
        
        # Create enum type for message channels
        migrations.RunSQL(
            "CREATE TYPE message_channel AS ENUM ('email', 'sms');",
            reverse_sql="DROP TYPE IF EXISTS message_channel;"
        ),
        
        # Convert email address field to CITEXT for case-insensitive operations
        migrations.RunSQL(
            "ALTER TABLE api_persistentagentemail ALTER COLUMN address TYPE CITEXT;",
            reverse_sql="ALTER TABLE api_persistentagentemail ALTER COLUMN address TYPE VARCHAR(320);"
        ),
        
        # Add unique constraint for primary emails per agent
        migrations.RunSQL(
            """
            CREATE UNIQUE INDEX uniq_primary_email
                ON api_persistentagentemail(persistent_agent_id)
                WHERE is_primary;
            """,
            reverse_sql="DROP INDEX IF EXISTS uniq_primary_email;"
        ),
        
        # Add global email uniqueness (case-insensitive)
        migrations.RunSQL(
            """
            CREATE UNIQUE INDEX uniq_email_address
                ON api_persistentagentemail(lower(address));
            """,
            reverse_sql="DROP INDEX IF EXISTS uniq_email_address;"
        ),
        
        # Add E.164 phone number validation constraint
        migrations.RunSQL(
            """
            ALTER TABLE api_persistentagentsmsnumber
                ADD CONSTRAINT chk_e164_phone
                CHECK (phone_number ~ '^\\+?[1-9]\\d{1,14}$');
            """,
            reverse_sql="ALTER TABLE api_persistentagentsmsnumber DROP CONSTRAINT IF EXISTS chk_e164_phone;"
        ),
        
        # Add unique constraint for primary SMS numbers per agent
        migrations.RunSQL(
            """
            CREATE UNIQUE INDEX uniq_primary_sms
                ON api_persistentagentsmsnumber(persistent_agent_id)
                WHERE is_primary;
            """,
            reverse_sql="DROP INDEX IF EXISTS uniq_primary_sms;"
        ),
        
        # Add global SMS number uniqueness
        migrations.RunSQL(
            """
            CREATE UNIQUE INDEX uniq_sms_number
                ON api_persistentagentsmsnumber(phone_number);
            """,
            reverse_sql="DROP INDEX IF EXISTS uniq_sms_number;"
        ),
        
        # Add sender validation constraint (exactly one sender field)
        migrations.RunSQL(
            """
            ALTER TABLE api_persistentagentmessage
                ADD CONSTRAINT chk_exactly_one_sender
                CHECK (
                    (from_email_id IS NOT NULL)::int +
                    (from_sms_id IS NOT NULL)::int +
                    (from_external_address IS NOT NULL AND from_external_address != '')::int = 1
                );
            """,
            reverse_sql="ALTER TABLE api_persistentagentmessage DROP CONSTRAINT IF EXISTS chk_exactly_one_sender;"
        ),
        
        # Add recipient validation constraint (exactly one recipient field)
        migrations.RunSQL(
            """
            ALTER TABLE api_persistentagentmessage
                ADD CONSTRAINT chk_exactly_one_recipient
                CHECK (
                    (to_email_id IS NOT NULL)::int +
                    (to_sms_id IS NOT NULL)::int +
                    (to_external_address IS NOT NULL AND to_external_address != '')::int = 1
                );
            """,
            reverse_sql="ALTER TABLE api_persistentagentmessage DROP CONSTRAINT IF EXISTS chk_exactly_one_recipient;"
        ),
    ]
