"""create_all_rm_inventory_tables

Revision ID: f8f08f7751dc
Revises: 
Create Date: 2026-06-10 17:27:10.586063

"""
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 = 'f8f08f7751dc'
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:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('entity_custom_fields',
    sa.Column('field_id', sa.UUID(), nullable=False),
    sa.Column('entity_type', sa.String(length=100), nullable=False),
    sa.Column('field_key', sa.String(length=100), nullable=False),
    sa.Column('field_label', sa.String(length=150), nullable=False),
    sa.Column('field_type', sa.String(length=50), nullable=False),
    sa.Column('dropdown_options', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('is_required', sa.Boolean(), nullable=False),
    sa.Column('is_visible_in_list', sa.Boolean(), nullable=True),
    sa.Column('sort_order', sa.Integer(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('field_id')
    )
    op.create_table('material_type_master',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('po_status_master',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('code', sa.String(length=50), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code')
    )
    op.create_table('procurement_source_master',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('store_master',
    sa.Column('store_id', sa.UUID(), nullable=False),
    sa.Column('store_name', sa.String(length=150), nullable=False),
    sa.Column('location', sa.String(length=200), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('store_id')
    )
    op.create_table('vendor_master',
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('contact_person', sa.String(length=150), nullable=True),
    sa.Column('phone', sa.String(length=20), nullable=True),
    sa.Column('email', sa.String(length=255), nullable=True),
    sa.Column('gst_number', sa.String(length=30), nullable=True),
    sa.Column('address_line1', sa.String(length=255), nullable=True),
    sa.Column('city', sa.String(length=100), nullable=True),
    sa.Column('state', sa.String(length=100), nullable=True),
    sa.Column('payment_terms', sa.String(length=100), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('vendor_id'),
    sa.UniqueConstraint('gst_number')
    )
    op.create_table('entity_custom_field_values',
    sa.Column('value_id', sa.UUID(), nullable=False),
    sa.Column('field_id', sa.UUID(), nullable=True),
    sa.Column('entity_type', sa.String(length=100), nullable=False),
    sa.Column('entity_id', sa.UUID(), nullable=False),
    sa.Column('field_value', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['field_id'], ['entity_custom_fields.field_id'], ),
    sa.PrimaryKeyConstraint('value_id')
    )
    op.create_index('ix_ecfv_entity', 'entity_custom_field_values', ['entity_type', 'entity_id'], unique=False)
    op.create_table('rm_master',
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('part_no', sa.String(length=100), nullable=True),
    sa.Column('unit_of_measurement', sa.String(length=50), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('material_type_id', sa.UUID(), nullable=True),
    sa.Column('procurement_source_id', sa.UUID(), nullable=True),
    sa.Column('minimum_stock', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('lead_time_days', sa.Integer(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['material_type_id'], ['material_type_master.id'], ),
    sa.ForeignKeyConstraint(['procurement_source_id'], ['procurement_source_master.id'], ),
    sa.PrimaryKeyConstraint('rm_id'),
    sa.UniqueConstraint('part_no')
    )
    op.create_table('rm_purchase_order',
    sa.Column('po_id', sa.UUID(), nullable=False),
    sa.Column('po_number', sa.String(length=50), nullable=True),
    sa.Column('vendor_id', sa.UUID(), nullable=True),
    sa.Column('order_date', sa.Date(), nullable=False),
    sa.Column('expected_delivery_date', sa.Date(), nullable=True),
    sa.Column('status_id', sa.UUID(), nullable=True),
    sa.Column('total_amount', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('notes', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['status_id'], ['po_status_master.id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('po_id'),
    sa.UniqueConstraint('po_number')
    )
    op.create_table('rm_consumption_log',
    sa.Column('consumption_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('store_id', sa.UUID(), nullable=False),
    sa.Column('qty_used', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('consumption_type', sa.String(length=50), nullable=False),
    sa.Column('consumed_date', sa.Date(), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('remarks', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['store_id'], ['store_master.store_id'], ),
    sa.PrimaryKeyConstraint('consumption_id')
    )
    op.create_table('rm_inventory',
    sa.Column('inventory_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('store_id', sa.UUID(), nullable=False),
    sa.Column('current_qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('reserved_qty', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('in_transit_qty', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('last_updated', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['store_id'], ['store_master.store_id'], ),
    sa.PrimaryKeyConstraint('inventory_id'),
    sa.UniqueConstraint('rm_id', 'store_id')
    )
    op.create_table('rm_inventory_log',
    sa.Column('log_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('store_id', sa.UUID(), nullable=False),
    sa.Column('transaction_type', sa.String(length=50), nullable=False),
    sa.Column('qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('balance_before', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('balance_after', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('reference_type', sa.String(length=50), nullable=True),
    sa.Column('reference_id', sa.UUID(), nullable=True),
    sa.Column('remarks', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['store_id'], ['store_master.store_id'], ),
    sa.PrimaryKeyConstraint('log_id')
    )
    op.create_table('rm_purchase_order_detail',
    sa.Column('po_detail_id', sa.UUID(), nullable=False),
    sa.Column('po_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('order_qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('received_qty', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('unit_price', sa.Numeric(precision=14, scale=2), nullable=False),
    sa.Column('gst_percent', sa.Numeric(precision=5, scale=2), nullable=True),
    sa.Column('line_amount', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('line_status', sa.String(length=30), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], ),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.PrimaryKeyConstraint('po_detail_id')
    )
    op.create_table('rm_receiving_log',
    sa.Column('grn_id', sa.UUID(), nullable=False),
    sa.Column('grn_number', sa.String(length=50), nullable=True),
    sa.Column('po_id', sa.UUID(), nullable=True),
    sa.Column('vendor_id', sa.UUID(), nullable=True),
    sa.Column('received_date', sa.Date(), nullable=False),
    sa.Column('vehicle_number', sa.String(length=50), nullable=True),
    sa.Column('grn_status', sa.String(length=30), nullable=True),
    sa.Column('remarks', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('grn_id'),
    sa.UniqueConstraint('grn_number')
    )
    op.create_table('rm_store_mapping',
    sa.Column('mapping_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('store_id', sa.UUID(), nullable=False),
    sa.Column('min_stock_level', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('max_stock_level', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['store_id'], ['store_master.store_id'], ),
    sa.PrimaryKeyConstraint('mapping_id'),
    sa.UniqueConstraint('rm_id', 'store_id')
    )
    op.create_table('rm_vendor_mapping',
    sa.Column('mapping_id', sa.UUID(), nullable=False),
    sa.Column('rm_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('standard_cost', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('mapping_id'),
    sa.UniqueConstraint('rm_id', 'vendor_id')
    )
    op.create_table('grn_detail',
    sa.Column('grn_detail_id', sa.UUID(), nullable=False),
    sa.Column('grn_id', sa.UUID(), nullable=True),
    sa.Column('po_detail_id', sa.UUID(), nullable=True),
    sa.Column('rm_id', sa.UUID(), nullable=True),
    sa.Column('received_qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('accepted_qty', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('rejected_qty', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('rejection_reason', sa.Text(), nullable=True),
    sa.Column('store_id', sa.UUID(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['grn_id'], ['rm_receiving_log.grn_id'], ),
    sa.ForeignKeyConstraint(['po_detail_id'], ['rm_purchase_order_detail.po_detail_id'], ),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], ),
    sa.ForeignKeyConstraint(['store_id'], ['store_master.store_id'], ),
    sa.PrimaryKeyConstraint('grn_detail_id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('grn_detail')
    op.drop_table('rm_vendor_mapping')
    op.drop_table('rm_store_mapping')
    op.drop_table('rm_receiving_log')
    op.drop_table('rm_purchase_order_detail')
    op.drop_table('rm_inventory_log')
    op.drop_table('rm_inventory')
    op.drop_table('rm_consumption_log')
    op.drop_table('rm_purchase_order')
    op.drop_table('rm_master')
    op.drop_index('ix_ecfv_entity', table_name='entity_custom_field_values')
    op.drop_table('entity_custom_field_values')
    op.drop_table('vendor_master')
    op.drop_table('store_master')
    op.drop_table('procurement_source_master')
    op.drop_table('po_status_master')
    op.drop_table('material_type_master')
    op.drop_table('entity_custom_fields')
    # ### end Alembic commands ###