"""add cmdb_applications table

Revision ID: 012
Revises: 011
Create Date: 2026-04-14
"""
from alembic import op
import sqlalchemy as sa

revision = '012'
down_revision = '011'
branch_labels = None
depends_on = None


def _table_exists(conn, table_name):
    result = conn.execute(sa.text(
        "SELECT COUNT(*) FROM information_schema.tables "
        "WHERE table_schema = DATABASE() AND table_name = :t"
    ), {"t": table_name})
    return result.scalar() > 0


def upgrade():
    conn = op.get_bind()
    if _table_exists(conn, 'cmdb_applications'):
        return
    op.create_table(
        'cmdb_applications',
        sa.Column('id', sa.String(36), primary_key=True, server_default=sa.text("(UUID())")),
        sa.Column('provider_account_id', sa.String(36), sa.ForeignKey('cmdb_provider_accounts.id'), nullable=False),
        sa.Column('app_name', sa.String(256), nullable=False),
        sa.Column('app_name_cn', sa.String(256)),
        sa.Column('category', sa.String(64)),
        sa.Column('repo_name', sa.String(256)),
        sa.Column('repo_url', sa.String(512)),
        sa.Column('status', sa.String(32), nullable=False, server_default='active'),
        sa.Column('owner_raw', sa.String(255)),
        sa.Column('description', sa.Text),
        sa.Column('raw_data', sa.JSON),
        sa.Column('last_seen_sync_job_id', sa.String(36)),
        sa.Column('first_seen_at', sa.DateTime(timezone=True), server_default=sa.text('NOW()')),
        sa.Column('last_synced_at', sa.DateTime(timezone=True), server_default=sa.text('NOW()')),
        sa.Column('deleted_at', sa.DateTime(timezone=True)),
        sa.UniqueConstraint('provider_account_id', 'app_name', name='uq_app_account_name'),
    )


def downgrade():
    op.drop_table('cmdb_applications')