"""Add advertisements table

Revision ID: a7f4c9e10b2d
Revises: 4db3d5f94ae2
Create Date: 2026-07-02 10:00:00.000000

Implements the extended advertisements table from the approved Administration
database design (docs/02_Architecture/Database/07_Administration.md): a
placement-targeted, data-driven advertising table supporting internal
promotions (banner) and rewarded advertisements.
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'a7f4c9e10b2d'
down_revision = '4db3d5f94ae2'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'advertisements',
        sa.Column('title', sa.String(length=150), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('advertisement_type', sa.Enum('banner', 'rewarded', name='advertisement_type'), nullable=False),
        sa.Column('placement', sa.Enum('landing', 'marketplace', 'chat', 'sidebar', name='advertisement_placement'), nullable=False),
        sa.Column('image_path', sa.String(length=255), nullable=True),
        sa.Column('redirect_url', sa.String(length=500), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.Column('priority', sa.Integer(), nullable=False),
        sa.Column('start_at', sa.DateTime(), nullable=True),
        sa.Column('end_at', sa.DateTime(), nullable=True),
        sa.Column('impression_count', sa.Integer(), nullable=False),
        sa.Column('click_count', sa.Integer(), nullable=False),
        sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), autoincrement=True, nullable=False),
        sa.Column('public_id', sa.CHAR(length=36), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('public_id'),
    )
    with op.batch_alter_table('advertisements', schema=None) as batch_op:
        batch_op.create_index('ix_advertisements_created_at', ['created_at'], unique=False)
        batch_op.create_index('ix_advertisements_placement_active', ['placement', 'is_active', 'priority'], unique=False)
        batch_op.create_index(batch_op.f('ix_advertisements_advertisement_type'), ['advertisement_type'], unique=False)
        batch_op.create_index(batch_op.f('ix_advertisements_is_active'), ['is_active'], unique=False)
        batch_op.create_index(batch_op.f('ix_advertisements_placement'), ['placement'], unique=False)
        batch_op.create_index(batch_op.f('ix_advertisements_priority'), ['priority'], unique=False)


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