"""Add profile fields to users table

Revision ID: 002
Revises: 001
Create Date: 2024-01-01 00:00:01.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.engine.reflection import Inspector

# revision identifiers, used by Alembic.
revision: str = '002'
down_revision: Union[str, None] = '001'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    bind = op.get_bind()
    inspector = Inspector.from_engine(bind)
    columns = [c['name'] for c in inspector.get_columns(table_name)]
    return column_name in columns


def upgrade() -> None:
    # List of columns to add (without server defaults for SQLite compatibility)
    columns_to_add = [
        ('first_name', sa.Column('first_name', sa.String(), nullable=True)),
        ('last_name', sa.Column('last_name', sa.String(), nullable=True)),
        ('phone', sa.Column('phone', sa.String(), nullable=True)),
        ('bio', sa.Column('bio', sa.String(), nullable=True)),
        ('preferred_language', sa.Column('preferred_language', sa.String(), nullable=True)),
        ('timezone', sa.Column('timezone', sa.String(), nullable=True)),
        ('updated_at', sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True))  # No server_default for SQLite
    ]
    
    # Add columns only if they don't exist
    for column_name, column_def in columns_to_add:
        if not column_exists('users', column_name):
            op.add_column('users', column_def)
    
    # Set default values for existing users (only if columns exist)
    if column_exists('users', 'preferred_language'):
        op.execute("UPDATE users SET preferred_language = 'en' WHERE preferred_language IS NULL")
    if column_exists('users', 'timezone'):
        op.execute("UPDATE users SET timezone = 'UTC' WHERE timezone IS NULL")
    if column_exists('users', 'updated_at'):
        # Set updated_at to current timestamp for existing users
        op.execute("UPDATE users SET updated_at = datetime('now') WHERE updated_at IS NULL")


def downgrade() -> None:
    # List of columns to remove (in reverse order)
    columns_to_remove = [
        'updated_at',
        'timezone', 
        'preferred_language',
        'bio',
        'phone',
        'last_name',
        'first_name'
    ]
    
    # Remove columns only if they exist
    for column_name in columns_to_remove:
        if column_exists('users', column_name):
            op.drop_column('users', column_name)