"""Add expense management tables

Revision ID: 7f907e255e52
Revises:
Create Date: 2026-02-24 20:37:16.349695

"""
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 = '7f907e255e52'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Create expense category enum
    expense_category = postgresql.ENUM(
        'purchase', 'maintenance', 'upkeep', 'salary', 'marketing',
        'utilities', 'supplies', 'equipment', 'services', 'other',
        name='expensecategory'
    )
    expense_category.create(op.get_bind(), checkfirst=True)

    # Create expense status enum
    expense_status = postgresql.ENUM(
        'draft', 'submitted', 'approved', 'rejected', 'paid',
        name='expensestatus'
    )
    expense_status.create(op.get_bind(), checkfirst=True)

    # Create expenses table
    op.create_table(
        'expenses',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('reference', sa.String(length=50), nullable=False),
        sa.Column('description', sa.String(length=500), nullable=False),
        sa.Column('establishment_id', sa.Integer(), nullable=False),
        sa.Column('department_code', sa.String(length=50), nullable=False),
        sa.Column('category', postgresql.ENUM(
            'purchase', 'maintenance', 'upkeep', 'salary', 'marketing',
            'utilities', 'supplies', 'equipment', 'services', 'other',
            name='expensecategory', create_type=False
        ), nullable=False),
        sa.Column('amount_ht', sa.Float(), nullable=False),
        sa.Column('tax_rate', sa.Float(), nullable=False, server_default='18.0'),
        sa.Column('tax_amount', sa.Float(), nullable=False),
        sa.Column('amount_ttc', sa.Float(), nullable=False),
        sa.Column('currency', sa.String(length=3), nullable=False, server_default='XOF'),
        sa.Column('expense_date', sa.Date(), nullable=False),
        sa.Column('due_date', sa.Date(), nullable=True),
        sa.Column('paid_date', sa.Date(), nullable=True),
        sa.Column('supplier_name', sa.String(length=200), nullable=True),
        sa.Column('supplier_reference', sa.String(length=100), nullable=True),
        sa.Column('status', postgresql.ENUM(
            'draft', 'submitted', 'approved', 'rejected', 'paid',
            name='expensestatus', create_type=False
        ), nullable=False, server_default='draft'),
        sa.Column('submitted_at', sa.DateTime(), nullable=True),
        sa.Column('submitted_by_id', sa.Integer(), nullable=True),
        sa.Column('approved_at', sa.DateTime(), nullable=True),
        sa.Column('approved_by_id', sa.Integer(), nullable=True),
        sa.Column('rejection_reason', sa.Text(), nullable=True),
        sa.Column('notes', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.func.now()),
        sa.ForeignKeyConstraint(['establishment_id'], ['establishments.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['submitted_by_id'], ['users.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['approved_by_id'], ['users.id'], ondelete='SET NULL'),
        sa.PrimaryKeyConstraint('id')
    )

    # Create indexes for expenses
    op.create_index('ix_expenses_reference', 'expenses', ['reference'], unique=True)
    op.create_index('ix_expenses_establishment_id', 'expenses', ['establishment_id'])
    op.create_index('ix_expenses_department_code', 'expenses', ['department_code'])
    op.create_index('ix_expenses_category', 'expenses', ['category'])
    op.create_index('ix_expenses_status', 'expenses', ['status'])
    op.create_index('ix_expenses_expense_date', 'expenses', ['expense_date'])

    # Create expense_attachments table
    op.create_table(
        'expense_attachments',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('expense_id', sa.Integer(), nullable=False),
        sa.Column('file_name', sa.String(length=255), nullable=False),
        sa.Column('file_path', sa.String(length=500), nullable=False),
        sa.Column('file_size', sa.Integer(), nullable=False),
        sa.Column('mime_type', sa.String(length=100), nullable=False),
        sa.Column('uploaded_by_id', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.func.now()),
        sa.ForeignKeyConstraint(['expense_id'], ['expenses.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['uploaded_by_id'], ['users.id'], ondelete='SET NULL'),
        sa.PrimaryKeyConstraint('id')
    )

    # Create index for expense_attachments
    op.create_index('ix_expense_attachments_expense_id', 'expense_attachments', ['expense_id'])


def downgrade() -> None:
    # Drop tables
    op.drop_index('ix_expense_attachments_expense_id', table_name='expense_attachments')
    op.drop_table('expense_attachments')

    op.drop_index('ix_expenses_expense_date', table_name='expenses')
    op.drop_index('ix_expenses_status', table_name='expenses')
    op.drop_index('ix_expenses_category', table_name='expenses')
    op.drop_index('ix_expenses_department_code', table_name='expenses')
    op.drop_index('ix_expenses_establishment_id', table_name='expenses')
    op.drop_index('ix_expenses_reference', table_name='expenses')
    op.drop_table('expenses')

    # Drop enums
    sa.Enum(name='expensestatus').drop(op.get_bind(), checkfirst=True)
    sa.Enum(name='expensecategory').drop(op.get_bind(), checkfirst=True)