"""Add stock_adjustment and stock_adjustment_item tables.

Revision ID: f1a2b3c4d5e6
Revises: e5f6a1b2c3d4
Create Date: 2026-04-02
"""

from alembic import op
import sqlalchemy as sa

revision = 'f1a2b3c4d5e6'
down_revision = 'e5f6a1b2c3d4'
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        'stock_adjustment',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('spbu_id', sa.Integer(), nullable=False),
        sa.Column('shift_id', sa.Integer(), nullable=False),
        sa.Column('tanggal', sa.Date(), nullable=False),
        sa.Column('status', sa.String(length=20), nullable=False, server_default='draft'),
        sa.Column('submitted_by_id', sa.Integer(), nullable=True),
        sa.Column('submitted_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('reviewed_by_id', sa.Integer(), nullable=True),
        sa.Column('reviewed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('catatan_review', sa.Text(), nullable=True),
        sa.Column('unlock_reason', sa.Text(), nullable=True),
        sa.Column('recalled_by_id', sa.Integer(), nullable=True),
        sa.Column('recalled_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['spbu_id'], ['master_spbu.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['shift_id'], ['master_spbu_shift.id'], ondelete='RESTRICT'),
        sa.ForeignKeyConstraint(['submitted_by_id'], ['master_user.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['reviewed_by_id'], ['master_user.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['recalled_by_id'], ['master_user.id'], ondelete='SET NULL'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('spbu_id', 'shift_id', 'tanggal', name='uq_stock_adj_spbu_shift_tanggal'),
    )
    op.create_index('ix_stock_adj_spbu_tanggal', 'stock_adjustment', ['spbu_id', 'tanggal'])
    op.create_index('ix_stock_adjustment_spbu_id', 'stock_adjustment', ['spbu_id'])
    op.create_index('ix_stock_adjustment_shift_id', 'stock_adjustment', ['shift_id'])

    op.create_table(
        'stock_adjustment_item',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('stock_adjustment_id', sa.Integer(), nullable=False),
        sa.Column('tangki_id', sa.Integer(), nullable=False),
        sa.Column('dipstick_manual_mm', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.Column('volume_manual_liter', sa.Numeric(precision=15, scale=3), nullable=True),
        sa.Column('dipstick_digital_mm', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.Column('volume_digital_liter', sa.Numeric(precision=15, scale=3), nullable=True),
        sa.Column('volume_final_liter', sa.Numeric(precision=15, scale=3), nullable=True),
        sa.ForeignKeyConstraint(['stock_adjustment_id'], ['stock_adjustment.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['tangki_id'], ['master_spbu_tangki.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_stock_adj_item_adj_id', 'stock_adjustment_item', ['stock_adjustment_id'])


def downgrade() -> None:
    op.drop_index('ix_stock_adj_item_adj_id', table_name='stock_adjustment_item')
    op.drop_table('stock_adjustment_item')
    op.drop_index('ix_stock_adjustment_shift_id', table_name='stock_adjustment')
    op.drop_index('ix_stock_adjustment_spbu_id', table_name='stock_adjustment')
    op.drop_index('ix_stock_adj_spbu_tanggal', table_name='stock_adjustment')
    op.drop_table('stock_adjustment')