"""add_user_address_table

Revision ID: b43b32dbe7d2
Revises: a31fb3c02c61
Create Date: 2025-02-25 14:31:28.933373

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
from sqlalchemy import inspect

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


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    
    # 테이블이 존재하는지 확인
    conn = op.get_bind()
    inspector = inspect(conn)
    
    # user_addresses 테이블이 없을 경우에만 생성
    if 'user_addresses' not in inspector.get_table_names():
        # 사용자 주소 테이블 생성
        op.create_table('user_addresses',
            sa.Column('id', mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False),
            sa.Column('user_id', mysql.BIGINT(unsigned=True), autoincrement=False, nullable=False),
            sa.Column('recipient_name', sa.String(length=100), nullable=False),
            sa.Column('address_line1', sa.String(length=255), nullable=False),
            sa.Column('address_line2', sa.String(length=255), nullable=True),
            sa.Column('city', sa.String(length=100), nullable=False),
            sa.Column('postal_code', sa.String(length=20), nullable=False),
            sa.Column('country', sa.String(length=2), nullable=False),
            sa.Column('phone', sa.String(length=20), nullable=False),
            sa.Column('is_default', sa.Boolean(), default=False, nullable=False),
            sa.Column('created_at', sa.DateTime(), nullable=False),
            sa.Column('updated_at', sa.DateTime(), nullable=False),
            sa.ForeignKeyConstraint(['user_id'], ['users.id'], name='user_addresses_ibfk_1', ondelete='CASCADE'),
            sa.PrimaryKeyConstraint('id'),
            mysql_collate='utf8mb4_unicode_ci',
            mysql_default_charset='utf8mb4',
            mysql_engine='InnoDB'
        )
        
        # 인덱스가 없는 경우에만 생성
        if 'idx_user_id' not in [idx['name'] for idx in inspector.get_indexes('user_addresses')]:
            op.create_index('idx_user_id', 'user_addresses', ['user_id'], unique=False)
    
    # orders 테이블에 address_id 컬럼 추가 (없는 경우에만)
    columns = [c['name'] for c in inspector.get_columns('orders')]
    if 'address_id' not in columns:
        op.add_column('orders', sa.Column('address_id', mysql.BIGINT(unsigned=True), nullable=True))
        
        # 기존 제약조건 확인
        fks = [fk['name'] for fk in inspector.get_foreign_keys('orders')]
        if 'fk_orders_address' not in fks:
            op.create_foreign_key(
                'fk_orders_address', 
                'orders', 'user_addresses', 
                ['address_id'], ['id'], 
                ondelete='SET NULL'
            )
    
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    
    # 테이블이 존재하는지 확인
    conn = op.get_bind()
    inspector = inspect(conn)
    
    # orders 테이블의 address_id 컬럼 관련 제약조건 및 컬럼 삭제
    fks = [fk['name'] for fk in inspector.get_foreign_keys('orders')]
    if 'fk_orders_address' in fks:
        op.drop_constraint('fk_orders_address', 'orders', type_='foreignkey')
    
    columns = [c['name'] for c in inspector.get_columns('orders')]
    if 'address_id' in columns:
        op.drop_column('orders', 'address_id')
    
    # user_addresses 테이블이 있을 경우에만 삭제 시도
    if 'user_addresses' in inspector.get_table_names():
        # 인덱스 먼저 삭제
        if 'idx_user_id' in [idx['name'] for idx in inspector.get_indexes('user_addresses')]:
            op.drop_index('idx_user_id', table_name='user_addresses')
        op.drop_table('user_addresses')
    
    # ### end Alembic commands ###