"""feature_updates_phase1

Revision ID: bdf7427ccfa3
Revises: 05bf96a9e18b
Create Date: 2025-06-09 18:00:11.083651

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = 'bdf7427ccfa3'
down_revision: Union[str, None] = '05bf96a9e18b'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('financial_audit_log',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('action_type', sa.String(), nullable=False),
    sa.Column('entity_type', sa.String(), nullable=False),
    sa.Column('entity_id', sa.Integer(), nullable=False),
    sa.Column('details', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_financial_audit_log_action_type'), 'financial_audit_log', ['action_type'], unique=False)
    op.create_index(op.f('ix_financial_audit_log_id'), 'financial_audit_log', ['id'], unique=False)
    op.create_table('categories',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('group_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name', 'user_id', 'group_id', name='uq_category_scope')
    )
    op.create_index(op.f('ix_categories_id'), 'categories', ['id'], unique=False)
    op.create_index(op.f('ix_categories_name'), 'categories', ['name'], unique=False)
    op.create_table('time_entries',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('chore_assignment_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('start_time', sa.DateTime(timezone=True), nullable=False),
    sa.Column('end_time', sa.DateTime(timezone=True), nullable=True),
    sa.Column('duration_seconds', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['chore_assignment_id'], ['chore_assignments.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_time_entries_id'), 'time_entries', ['id'], unique=False)
    op.add_column('chores', sa.Column('parent_chore_id', sa.Integer(), nullable=True))
    op.create_index(op.f('ix_chores_parent_chore_id'), 'chores', ['parent_chore_id'], unique=False)
    op.create_foreign_key(None, 'chores', 'chores', ['parent_chore_id'], ['id'])
    op.add_column('items', sa.Column('category_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'items', 'categories', ['category_id'], ['id'])
    op.add_column('lists', sa.Column('archived_at', sa.DateTime(timezone=True), nullable=True))
    op.create_index(op.f('ix_lists_archived_at'), 'lists', ['archived_at'], unique=False)
    op.add_column('users', sa.Column('is_guest', sa.Boolean(), nullable=False, server_default='f'))
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('users', 'is_guest')
    op.drop_index(op.f('ix_lists_archived_at'), table_name='lists')
    op.drop_column('lists', 'archived_at')
    op.drop_constraint(None, 'items', type_='foreignkey')
    op.drop_column('items', 'category_id')
    op.drop_constraint(None, 'chores', type_='foreignkey')
    op.drop_index(op.f('ix_chores_parent_chore_id'), table_name='chores')
    op.drop_column('chores', 'parent_chore_id')
    op.drop_index(op.f('ix_time_entries_id'), table_name='time_entries')
    op.drop_table('time_entries')
    op.drop_index(op.f('ix_categories_name'), table_name='categories')
    op.drop_index(op.f('ix_categories_id'), table_name='categories')
    op.drop_table('categories')
    op.drop_index(op.f('ix_financial_audit_log_id'), table_name='financial_audit_log')
    op.drop_index(op.f('ix_financial_audit_log_action_type'), table_name='financial_audit_log')
    op.drop_table('financial_audit_log')
    # ### end Alembic commands ###
