"""
Migration script to add organization and sponsor management system.
"""
import sys
import os as os_module

from sqlalchemy import text, create_engine, inspect

# Use Unix socket connection
DATABASE_URL = os_module.getenv('DATABASE_URL', 'postgresql://postgres@/jadwa')

def migrate():
    """Run the migration."""
    engine = create_engine(DATABASE_URL)

    with engine.connect() as conn:
        print("Creating organization and sponsor tables...")

        # Create organizations table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS organizations (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                name_ar VARCHAR(255),
                slug VARCHAR(255) UNIQUE NOT NULL,
                description TEXT,
                description_ar TEXT,
                logo_url VARCHAR(500),
                org_type VARCHAR(50) DEFAULT 'company',
                website_url VARCHAR(500),
                email VARCHAR(255),
                phone VARCHAR(50),
                address VARCHAR(500),
                city VARCHAR(100),
                country VARCHAR(100),
                sponsor_tier VARCHAR(20),
                is_active BOOLEAN DEFAULT TRUE,
                is_verified BOOLEAN DEFAULT FALSE,
                deleted_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """))

        # Create indexes for organizations
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_organizations_name ON organizations(name)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_organizations_slug ON organizations(slug)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_organizations_is_active ON organizations(is_active)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_organizations_created_at ON organizations(created_at)"))

        # Create sponsor_profiles table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS sponsor_profiles (
                id SERIAL PRIMARY KEY,
                organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
                user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
                logo_url VARCHAR(500),
                website_url VARCHAR(500),
                tagline VARCHAR(255),
                tagline_ar VARCHAR(255),
                tier VARCHAR(20) DEFAULT 'bronze',
                monthly_contribution NUMERIC(10, 2),
                status VARCHAR(20) DEFAULT 'pending',
                display_on_landing BOOLEAN DEFAULT FALSE,
                display_order INTEGER DEFAULT 0,
                notes TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                approved_at TIMESTAMP,
                rejected_at TIMESTAMP
            )
        """))

        # Create indexes for sponsor_profiles
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_sponsor_profiles_status ON sponsor_profiles(status)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS ix_sponsor_profiles_created_at ON sponsor_profiles(created_at)"))

        # Add organization_id to users table if it doesn't exist
        print("Checking organization_id column in users table...")
        inspector = inspect(engine)
        users_columns = [col['name'] for col in inspector.get_columns('users')]

        if 'organization_id' not in users_columns:
            print("Adding organization_id column to users table...")
            conn.execute(text("ALTER TABLE users ADD COLUMN organization_id INTEGER REFERENCES organizations(id)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS ix_users_organization_id ON users(organization_id)"))
            print("organization_id column added successfully.")
        else:
            print("organization_id column already exists.")

        conn.commit()

    # Verify tables were created
    print("\nVerifying tables...")
    with engine.connect() as conn:
        inspector = inspect(engine)
        tables = inspector.get_table_names()

        if 'organizations' in tables:
            print("✓ organizations table created")
        else:
            print("✗ organizations table NOT created")

        if 'sponsor_profiles' in tables:
            print("✓ sponsor_profiles table created")
        else:
            print("✗ sponsor_profiles table NOT created")

        users_columns = [col['name'] for col in inspector.get_columns('users')]
        if 'organization_id' in users_columns:
            print("✓ users.organization_id column exists")
        else:
            print("✗ users.organization_id column does NOT exist")

    print("\nMigration completed successfully!")


if __name__ == "__main__":
    migrate()