5935f302dc
- Set up project structure and FastAPI application - Create database models with SQLAlchemy - Implement authentication with JWT - Add CRUD operations for products, inventory, categories - Implement purchase order and sales functionality - Create reporting endpoints - Set up Alembic for database migrations - Add comprehensive documentation in README.md
168 lines
7.8 KiB
Python
168 lines
7.8 KiB
Python
"""Initial migration
|
|
|
|
Revision ID: 001
|
|
Revises:
|
|
Create Date: 2023-08-12 00:00:00.000000
|
|
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '001'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
# Create user table
|
|
op.create_table('user',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('full_name', sa.String(), nullable=True),
|
|
sa.Column('email', sa.String(), nullable=False),
|
|
sa.Column('hashed_password', sa.String(), nullable=False),
|
|
sa.Column('is_active', sa.Boolean(), nullable=True),
|
|
sa.Column('is_admin', sa.Boolean(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_user_email'), 'user', ['email'], unique=True)
|
|
op.create_index(op.f('ix_user_full_name'), 'user', ['full_name'], unique=False)
|
|
op.create_index(op.f('ix_user_id'), 'user', ['id'], unique=False)
|
|
|
|
# Create category table
|
|
op.create_table('category',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('description', sa.Text(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_category_id'), 'category', ['id'], unique=False)
|
|
op.create_index(op.f('ix_category_name'), 'category', ['name'], unique=False)
|
|
|
|
# Create product table
|
|
op.create_table('product',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('description', sa.Text(), nullable=True),
|
|
sa.Column('sku', sa.String(), nullable=True),
|
|
sa.Column('barcode', sa.String(), nullable=True),
|
|
sa.Column('unit_price', sa.Numeric(precision=10, scale=2), nullable=False),
|
|
sa.Column('cost_price', sa.Numeric(precision=10, scale=2), nullable=False),
|
|
sa.Column('category_id', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['category_id'], ['category.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_product_barcode'), 'product', ['barcode'], unique=True)
|
|
op.create_index(op.f('ix_product_id'), 'product', ['id'], unique=False)
|
|
op.create_index(op.f('ix_product_name'), 'product', ['name'], unique=False)
|
|
op.create_index(op.f('ix_product_sku'), 'product', ['sku'], unique=True)
|
|
|
|
# Create inventory table
|
|
op.create_table('inventory',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('product_id', sa.Integer(), nullable=False),
|
|
sa.Column('quantity', sa.Integer(), nullable=False),
|
|
sa.Column('location', sa.String(), nullable=True),
|
|
sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_inventory_id'), 'inventory', ['id'], unique=False)
|
|
|
|
# Create inventory transaction table
|
|
op.create_table('inventorytransaction',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('product_id', sa.Integer(), nullable=False),
|
|
sa.Column('quantity', sa.Integer(), nullable=False),
|
|
sa.Column('transaction_type', sa.String(), nullable=False),
|
|
sa.Column('reference_id', sa.Integer(), nullable=True),
|
|
sa.Column('reason', sa.String(), nullable=True),
|
|
sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
|
|
sa.Column('location', sa.String(), nullable=True),
|
|
sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_inventorytransaction_id'), 'inventorytransaction', ['id'], unique=False)
|
|
|
|
# Create purchase order table
|
|
op.create_table('purchaseorder',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('supplier_name', sa.String(), nullable=False),
|
|
sa.Column('notes', sa.Text(), nullable=True),
|
|
sa.Column('status', sa.String(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(['created_by'], ['user.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_purchaseorder_id'), 'purchaseorder', ['id'], unique=False)
|
|
|
|
# Create purchase order item table
|
|
op.create_table('purchaseorderitem',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('purchase_order_id', sa.Integer(), nullable=False),
|
|
sa.Column('product_id', sa.Integer(), nullable=False),
|
|
sa.Column('quantity', sa.Integer(), nullable=False),
|
|
sa.Column('unit_price', sa.Numeric(precision=10, scale=2), nullable=False),
|
|
sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
|
|
sa.ForeignKeyConstraint(['purchase_order_id'], ['purchaseorder.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_purchaseorderitem_id'), 'purchaseorderitem', ['id'], unique=False)
|
|
|
|
# Create sale table
|
|
op.create_table('sale',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('customer_name', sa.String(), nullable=True),
|
|
sa.Column('notes', sa.Text(), nullable=True),
|
|
sa.Column('status', sa.String(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(['created_by'], ['user.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_sale_id'), 'sale', ['id'], unique=False)
|
|
|
|
# Create sale item table
|
|
op.create_table('saleitem',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('sale_id', sa.Integer(), nullable=False),
|
|
sa.Column('product_id', sa.Integer(), nullable=False),
|
|
sa.Column('quantity', sa.Integer(), nullable=False),
|
|
sa.Column('unit_price', sa.Numeric(precision=10, scale=2), nullable=False),
|
|
sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
|
|
sa.ForeignKeyConstraint(['sale_id'], ['sale.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_saleitem_id'), 'saleitem', ['id'], unique=False)
|
|
|
|
|
|
def downgrade():
|
|
# Drop tables in reverse order of creation
|
|
op.drop_index(op.f('ix_saleitem_id'), table_name='saleitem')
|
|
op.drop_table('saleitem')
|
|
op.drop_index(op.f('ix_sale_id'), table_name='sale')
|
|
op.drop_table('sale')
|
|
op.drop_index(op.f('ix_purchaseorderitem_id'), table_name='purchaseorderitem')
|
|
op.drop_table('purchaseorderitem')
|
|
op.drop_index(op.f('ix_purchaseorder_id'), table_name='purchaseorder')
|
|
op.drop_table('purchaseorder')
|
|
op.drop_index(op.f('ix_inventorytransaction_id'), table_name='inventorytransaction')
|
|
op.drop_table('inventorytransaction')
|
|
op.drop_index(op.f('ix_inventory_id'), table_name='inventory')
|
|
op.drop_table('inventory')
|
|
op.drop_index(op.f('ix_product_sku'), table_name='product')
|
|
op.drop_index(op.f('ix_product_name'), table_name='product')
|
|
op.drop_index(op.f('ix_product_id'), table_name='product')
|
|
op.drop_index(op.f('ix_product_barcode'), table_name='product')
|
|
op.drop_table('product')
|
|
op.drop_index(op.f('ix_category_name'), table_name='category')
|
|
op.drop_index(op.f('ix_category_id'), table_name='category')
|
|
op.drop_table('category')
|
|
op.drop_index(op.f('ix_user_id'), table_name='user')
|
|
op.drop_index(op.f('ix_user_full_name'), table_name='user')
|
|
op.drop_index(op.f('ix_user_email'), table_name='user')
|
|
op.drop_table('user') |