"""creat_farmland_orm

Revision ID: beeaf68d0ee1
Revises: f0d12e4fab12
Create Date: 2025-06-14 17:07:57.976453

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


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


def upgrade():
    """升级数据库到当前版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    
    # 检查表是否已存在，如果不存在才创建
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    
    if 'Farmland_data' not in inspector.get_table_names():
        op.create_table('Farmland_data',
        sa.Column('Farmland_ID', sa.Integer(), nullable=False, comment='区域农业用地矢量点编号'),
        sa.Column('Sample_ID', sa.Integer(), autoincrement=True, nullable=False, comment='采样自增的ID'),
        sa.Column('lon', sa.Float(), nullable=True, comment='经度'),
        sa.Column('lan', sa.Float(), nullable=True, comment='纬度'),
        sa.Column('Type', sa.Float(), nullable=True, comment='用地类型：旱地(0)、水田(1)、水浇地(2)'),
        sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POINT', srid=4326, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True, comment='点几何对象'),
        sa.PrimaryKeyConstraint('Farmland_ID', 'Sample_ID')
        )
        
        # 为表添加注释
        op.execute("COMMENT ON TABLE \"Farmland_data\" IS '耕地样点空间位置与索引数据表，存储农业用地的坐标、类型和空间几何信息'")
    
    # 使用原生SQL安全创建索引
    try:
        op.execute('CREATE INDEX IF NOT EXISTS idx_Farmland_data_geom ON "Farmland_data" USING gist (geom)')
    except Exception as e:
        print(f"索引创建警告: {e}")
    
    # ### end Alembic commands ###


def downgrade():
    """将数据库降级到上一版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    
    # 安全地删除索引和表，检查存在性
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    
    if 'Farmland_data' in inspector.get_table_names():
        # 检查索引是否存在，存在才删除
        indexes = inspector.get_indexes('Farmland_data')
        index_names = [idx['name'] for idx in indexes]
        
        if 'idx_Farmland_data_geom' in index_names:
            op.drop_index('idx_Farmland_data_geom', table_name='Farmland_data', postgresql_using='gist')
            
        # 删除表
        op.drop_table('Farmland_data')
    
    # ### end Alembic commands ###