"""add_laporan_shift_penjualan

Revision ID: c3d4e5f6a1b2
Revises: b2c3d4e5f6a1
Create Date: 2026-04-02 00:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = 'c3d4e5f6a1b2'
down_revision: Union[str, Sequence[str], None] = 'b2c3d4e5f6a1'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Create the PostgreSQL enum type for status_laporan
    statuslaporanenum = sa.Enum(
        'draft', 'submitted', 'approved', 'rejected', 'locked',
        name='statuslaporanenum',
    )
    statuslaporanenum.create(op.get_bind(), checkfirst=True)

    # Create laporan_shift table
    op.create_table(
        'laporan_shift',
        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('created_at', sa.DateTime(), nullable=False, server_default=sa.text('now()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('now()')),
        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.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('spbu_id', 'shift_id', 'tanggal', name='uq_laporan_shift_spbu_shift_tanggal'),
    )
    op.create_index('ix_laporan_shift_spbu_id', 'laporan_shift', ['spbu_id'])
    op.create_index('ix_laporan_shift_shift_id', 'laporan_shift', ['shift_id'])
    op.create_index('ix_laporan_shift_spbu_tanggal', 'laporan_shift', ['spbu_id', 'tanggal'])

    # Create penjualan_nozzle table
    op.create_table(
        'penjualan_nozzle',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('laporan_shift_id', sa.Integer(), nullable=False),
        sa.Column('nozzle_id', sa.Integer(), nullable=False),
        sa.Column('teller_awal', sa.Numeric(precision=15, scale=3), nullable=False),
        sa.Column('teller_akhir', sa.Numeric(precision=15, scale=3), nullable=False),
        sa.Column('flag_reset_teller', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('volume', sa.Numeric(precision=15, scale=3), nullable=False),
        sa.Column('harga_jual', sa.Numeric(precision=15, scale=3), nullable=False),
        sa.Column('nilai', sa.Numeric(precision=15, scale=3), nullable=False),
        sa.ForeignKeyConstraint(['laporan_shift_id'], ['laporan_shift.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['nozzle_id'], ['master_spbu_nozzle.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_penjualan_nozzle_laporan_shift_id', 'penjualan_nozzle', ['laporan_shift_id'])


def downgrade() -> None:
    op.drop_index('ix_penjualan_nozzle_laporan_shift_id', table_name='penjualan_nozzle')
    op.drop_table('penjualan_nozzle')

    op.drop_index('ix_laporan_shift_spbu_tanggal', table_name='laporan_shift')
    op.drop_index('ix_laporan_shift_shift_id', table_name='laporan_shift')
    op.drop_index('ix_laporan_shift_spbu_id', table_name='laporan_shift')
    op.drop_table('laporan_shift')

    sa.Enum(name='statuslaporanenum').drop(op.get_bind(), checkfirst=True)