"""add multi-tenant models: school, token, ticket

Revision ID: 0f60a53bed92
Revises: ac94c9116a87
Create Date: 2026-03-10 05:33:19.617709

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '0f60a53bed92'
down_revision = 'ac94c9116a87'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('schools',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('address', sa.Text(), nullable=True),
    sa.Column('phone', sa.String(length=20), nullable=True),
    sa.Column('email', sa.String(length=100), nullable=False),
    sa.Column('admin_email', sa.String(length=100), nullable=False),
    sa.Column('status', sa.Enum('PENDING', 'VERIFIED', 'ACTIVE', 'SUSPENDED', name='schoolstatus'), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('approved_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('schools', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_schools_slug'), ['slug'], unique=True)

    op.create_table('email_verification_tokens',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('token', sa.String(length=64), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('school_id', sa.Integer(), nullable=True),
    sa.Column('expires_at', sa.DateTime(), nullable=False),
    sa.Column('used_at', sa.DateTime(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['school_id'], ['schools.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('email_verification_tokens', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_email_verification_tokens_token'), ['token'], unique=True)

    op.create_table('password_reset_tokens',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('token', sa.String(length=64), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('expires_at', sa.DateTime(), nullable=False),
    sa.Column('used_at', sa.DateTime(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('password_reset_tokens', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_password_reset_tokens_token'), ['token'], unique=True)

    op.create_table('tickets',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('ticket_number', sa.String(length=20), nullable=False),
    sa.Column('title', sa.String(length=200), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.Column('category', sa.Enum('TECHNICAL', 'ACCOUNT', 'COURSE', 'QUIZ', 'GENERAL', name='ticketcategory'), nullable=False),
    sa.Column('status', sa.Enum('OPEN', 'IN_QUEUE', 'IN_PROGRESS', 'WAITING_USER', 'RESOLVED', 'CLOSED', name='ticketstatus'), nullable=False),
    sa.Column('priority', sa.Enum('LOW', 'MEDIUM', 'HIGH', 'URGENT', name='ticketpriority'), nullable=False),
    sa.Column('school_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('resolved_at', sa.DateTime(), nullable=True),
    sa.Column('closed_at', sa.DateTime(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['school_id'], ['schools.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('tickets', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_tickets_school_id'), ['school_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_tickets_ticket_number'), ['ticket_number'], unique=True)

    op.create_table('ticket_messages',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('ticket_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('content', sa.Text(), nullable=False),
    sa.Column('is_internal', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['ticket_id'], ['tickets.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('ticket_messages', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_ticket_messages_ticket_id'), ['ticket_id'], unique=False)

    with op.batch_alter_table('academic_years', schema=None) as batch_op:
        batch_op.add_column(sa.Column('school_id', sa.Integer(), nullable=True))
        batch_op.drop_index(batch_op.f('year'))
        batch_op.create_index(batch_op.f('ix_academic_years_school_id'), ['school_id'], unique=False)
        batch_op.create_unique_constraint('uq_academic_year_school', ['year', 'school_id'])
        batch_op.create_foreign_key(None, 'schools', ['school_id'], ['id'])

    with op.batch_alter_table('activity_logs', schema=None) as batch_op:
        batch_op.add_column(sa.Column('school_id', sa.Integer(), nullable=True))
        batch_op.create_index(batch_op.f('ix_activity_logs_school_id'), ['school_id'], unique=False)
        batch_op.create_foreign_key(None, 'schools', ['school_id'], ['id'])

    with op.batch_alter_table('courses', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_courses_class_code'))
        batch_op.create_index(batch_op.f('ix_courses_class_code'), ['class_code'], unique=False)

    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.add_column(sa.Column('email_verified', sa.Boolean(), nullable=False))
        batch_op.add_column(sa.Column('school_id', sa.Integer(), nullable=True))
        batch_op.create_index(batch_op.f('ix_users_school_id'), ['school_id'], unique=False)
        batch_op.create_foreign_key(None, 'schools', ['school_id'], ['id'])

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_index(batch_op.f('ix_users_school_id'))
        batch_op.drop_column('school_id')
        batch_op.drop_column('email_verified')

    with op.batch_alter_table('courses', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_courses_class_code'))
        batch_op.create_index(batch_op.f('ix_courses_class_code'), ['class_code'], unique=True)

    with op.batch_alter_table('activity_logs', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_index(batch_op.f('ix_activity_logs_school_id'))
        batch_op.drop_column('school_id')

    with op.batch_alter_table('academic_years', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('uq_academic_year_school', type_='unique')
        batch_op.drop_index(batch_op.f('ix_academic_years_school_id'))
        batch_op.create_index(batch_op.f('year'), ['year'], unique=True)
        batch_op.drop_column('school_id')

    with op.batch_alter_table('ticket_messages', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_ticket_messages_ticket_id'))

    op.drop_table('ticket_messages')
    with op.batch_alter_table('tickets', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_tickets_ticket_number'))
        batch_op.drop_index(batch_op.f('ix_tickets_school_id'))

    op.drop_table('tickets')
    with op.batch_alter_table('password_reset_tokens', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_password_reset_tokens_token'))

    op.drop_table('password_reset_tokens')
    with op.batch_alter_table('email_verification_tokens', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_email_verification_tokens_token'))

    op.drop_table('email_verification_tokens')
    with op.batch_alter_table('schools', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_schools_slug'))

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