"""initial blog schema

Revision ID: d3a7e69d727b
Revises: 
Create Date: 2026-08-04 15:30:14.387544

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('admin_users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(length=255), nullable=False),
    sa.Column('name', sa.String(length=160), nullable=False),
    sa.Column('password_hash', sa.String(length=512), nullable=False),
    sa.Column('role', sa.String(length=30), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint("role IN ('administrator','editor')", name=op.f('ck_admin_users_valid_role')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_admin_users'))
    )
    with op.batch_alter_table('admin_users', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_admin_users_email'), ['email'], unique=True)

    op.create_table('categories',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=120), nullable=False),
    sa.Column('slug', sa.String(length=140), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.Column('seo_title', sa.String(length=180), nullable=False),
    sa.Column('meta_description', sa.String(length=320), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_categories')),
    sa.UniqueConstraint('name', name=op.f('uq_categories_name'))
    )
    with op.batch_alter_table('categories', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_categories_slug'), ['slug'], unique=True)

    op.create_table('redirects',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('source_path', sa.String(length=500), nullable=False),
    sa.Column('destination_path', sa.String(length=500), nullable=False),
    sa.Column('status_code', sa.Integer(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint('status_code IN (301,302,307,308)', name=op.f('ck_redirects_valid_status_code')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_redirects')),
    sa.UniqueConstraint('source_path', name=op.f('uq_redirects_source_path'))
    )
    op.create_table('tags',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=120), nullable=False),
    sa.Column('slug', sa.String(length=140), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_tags')),
    sa.UniqueConstraint('name', name=op.f('uq_tags_name'))
    )
    with op.batch_alter_table('tags', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_tags_slug'), ['slug'], unique=True)

    op.create_table('audit_logs',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('actor_id', sa.Integer(), nullable=True),
    sa.Column('action', sa.String(length=120), nullable=False),
    sa.Column('entity_type', sa.String(length=80), nullable=False),
    sa.Column('entity_id', sa.Integer(), nullable=True),
    sa.Column('details', sa.JSON(), nullable=False),
    sa.Column('ip_address', sa.String(length=64), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['actor_id'], ['admin_users.id'], name=op.f('fk_audit_logs_actor_id_admin_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_audit_logs'))
    )
    with op.batch_alter_table('audit_logs', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_audit_logs_action'), ['action'], unique=False)
        batch_op.create_index(batch_op.f('ix_audit_logs_created_at'), ['created_at'], unique=False)

    op.create_table('media_assets',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('original_filename', sa.String(length=255), nullable=False),
    sa.Column('stored_filename', sa.String(length=255), nullable=False),
    sa.Column('mime_type', sa.String(length=120), nullable=False),
    sa.Column('byte_size', sa.Integer(), nullable=False),
    sa.Column('width', sa.Integer(), nullable=True),
    sa.Column('height', sa.Integer(), nullable=True),
    sa.Column('alt_text', sa.String(length=255), nullable=False),
    sa.Column('caption', sa.Text(), nullable=False),
    sa.Column('uploaded_by_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['uploaded_by_id'], ['admin_users.id'], name=op.f('fk_media_assets_uploaded_by_id_admin_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_media_assets')),
    sa.UniqueConstraint('stored_filename', name=op.f('uq_media_assets_stored_filename'))
    )
    op.create_table('posts',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(length=220), nullable=False),
    sa.Column('slug', sa.String(length=240), nullable=False),
    sa.Column('excerpt', sa.Text(), nullable=False),
    sa.Column('content', sa.JSON(), nullable=False),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('author_id', sa.Integer(), nullable=False),
    sa.Column('category_id', sa.Integer(), nullable=True),
    sa.Column('featured_image_id', sa.Integer(), nullable=True),
    sa.Column('og_image_id', sa.Integer(), nullable=True),
    sa.Column('is_featured', sa.Boolean(), nullable=False),
    sa.Column('seo_title', sa.String(length=180), nullable=False),
    sa.Column('meta_description', sa.String(length=320), nullable=False),
    sa.Column('focus_keyword', sa.String(length=180), nullable=False),
    sa.Column('canonical_url', sa.String(length=500), nullable=False),
    sa.Column('robots_index', sa.Boolean(), nullable=False),
    sa.Column('published_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('scheduled_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint("status IN ('draft','scheduled','published','archived')", name=op.f('ck_posts_valid_status')),
    sa.ForeignKeyConstraint(['author_id'], ['admin_users.id'], name=op.f('fk_posts_author_id_admin_users'), ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['category_id'], ['categories.id'], name=op.f('fk_posts_category_id_categories'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['featured_image_id'], ['media_assets.id'], name=op.f('fk_posts_featured_image_id_media_assets'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['og_image_id'], ['media_assets.id'], name=op.f('fk_posts_og_image_id_media_assets'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_posts'))
    )
    with op.batch_alter_table('posts', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_posts_published_at'), ['published_at'], unique=False)
        batch_op.create_index(batch_op.f('ix_posts_scheduled_at'), ['scheduled_at'], unique=False)
        batch_op.create_index(batch_op.f('ix_posts_slug'), ['slug'], unique=True)
        batch_op.create_index(batch_op.f('ix_posts_status'), ['status'], unique=False)
        batch_op.create_index('ix_posts_status_published_at', ['status', 'published_at'], unique=False)

    op.create_table('post_revisions',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('post_id', sa.Integer(), nullable=False),
    sa.Column('editor_id', sa.Integer(), nullable=True),
    sa.Column('version', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(length=220), nullable=False),
    sa.Column('excerpt', sa.Text(), nullable=False),
    sa.Column('content', sa.JSON(), nullable=False),
    sa.Column('snapshot', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['editor_id'], ['admin_users.id'], name=op.f('fk_post_revisions_editor_id_admin_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['post_id'], ['posts.id'], name=op.f('fk_post_revisions_post_id_posts'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_post_revisions')),
    sa.UniqueConstraint('post_id', 'version', name='post_revision_version')
    )
    op.create_table('post_tags',
    sa.Column('post_id', sa.Integer(), nullable=False),
    sa.Column('tag_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['post_id'], ['posts.id'], name=op.f('fk_post_tags_post_id_posts'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['tag_id'], ['tags.id'], name=op.f('fk_post_tags_tag_id_tags'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('post_id', 'tag_id', name=op.f('pk_post_tags'))
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('post_tags')
    op.drop_table('post_revisions')
    with op.batch_alter_table('posts', schema=None) as batch_op:
        batch_op.drop_index('ix_posts_status_published_at')
        batch_op.drop_index(batch_op.f('ix_posts_status'))
        batch_op.drop_index(batch_op.f('ix_posts_slug'))
        batch_op.drop_index(batch_op.f('ix_posts_scheduled_at'))
        batch_op.drop_index(batch_op.f('ix_posts_published_at'))

    op.drop_table('posts')
    op.drop_table('media_assets')
    with op.batch_alter_table('audit_logs', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_audit_logs_created_at'))
        batch_op.drop_index(batch_op.f('ix_audit_logs_action'))

    op.drop_table('audit_logs')
    with op.batch_alter_table('tags', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_tags_slug'))

    op.drop_table('tags')
    op.drop_table('redirects')
    with op.batch_alter_table('categories', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_categories_slug'))

    op.drop_table('categories')
    with op.batch_alter_table('admin_users', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_admin_users_email'))

    op.drop_table('admin_users')
    # ### end Alembic commands ###