"""add_counties_table

Revision ID: f0d12e4fab12
Revises: c1cf3ab2c7fe
Create Date: 2025-05-17 20:39:22.830946

"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2


# revision identifiers, used by Alembic.
revision = 'f0d12e4fab12'
down_revision = 'c1cf3ab2c7fe'
branch_labels = None
depends_on = None


def upgrade():
    """升级数据库到当前版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('counties',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False, comment='县名'),
    sa.Column('code', sa.Integer(), nullable=True, comment='县代码'),
    sa.Column('city_name', sa.String(length=100), nullable=True, comment='市名'),
    sa.Column('city_code', sa.Integer(), nullable=True, comment='市代码'),
    sa.Column('province_name', sa.String(length=100), nullable=True, comment='省名'),
    sa.Column('province_code', sa.Integer(), nullable=True, comment='省代码'),
    sa.Column('geometry', geoalchemy2.types.Geometry(geometry_type='MULTIPOLYGON', srid=4326, from_text='ST_GeomFromEWKT', name='geometry', nullable=False), nullable=False, comment='县级行政区划的几何数据'),
    sa.Column('geojson', sa.JSON(), nullable=False, comment='完整的GeoJSON数据'),
    sa.PrimaryKeyConstraint('id')
    )
    
    # 检查并删除已存在的索引
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    existing_indexes = inspector.get_indexes('counties')
    
    for index in existing_indexes:
        if index['name'] in ['idx_counties_code', 'idx_counties_geometry', 'idx_counties_name', 'ix_counties_id']:
            op.drop_index(index['name'], table_name='counties')
    
    # 创建新的索引
    op.create_index('idx_counties_code', 'counties', ['code'], unique=False)
    op.create_index('idx_counties_geometry', 'counties', ['geometry'], unique=False, postgresql_using='gist')
    op.create_index('idx_counties_name', 'counties', ['name'], unique=False)
    op.create_index(op.f('ix_counties_id'), 'counties', ['id'], unique=False)
    op.create_index(op.f('ix_raster_table_id'), 'raster_table', ['id'], unique=False)
    # ### end Alembic commands ###


def downgrade():
    """将数据库降级到上一版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_raster_table_id'), table_name='raster_table')
    op.drop_index(op.f('ix_counties_id'), table_name='counties')
    op.drop_index('idx_counties_name', table_name='counties')
    op.drop_index('idx_counties_geometry', table_name='counties', postgresql_using='gist')
    op.drop_index('idx_counties_code', table_name='counties')
    op.drop_table('counties')
    # ### end Alembic commands ###