"""redesign penyetoran: per-shift + batch table

Revision ID: e7h8i9j0k1l2
Revises: d6g7h8i9j0k1
Create Date: 2026-04-12

"""
from alembic import op
import sqlalchemy as sa

revision = 'e7h8i9j0k1l2'
down_revision = 'd6g7h8i9j0k1'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 1. Create penyetoran_batch table first (penyetoran will reference it)
    op.create_table(
        'penyetoran_batch',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('spbu_id', sa.Integer(), sa.ForeignKey('master_spbu.id', ondelete='CASCADE'), nullable=False),
        sa.Column('tanggal_from', sa.Date(), nullable=False),
        sa.Column('tanggal_to', sa.Date(), nullable=False),
        sa.Column('total_amount', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('catatan', sa.Text(), nullable=True),
        sa.Column('status', sa.String(20), nullable=False, server_default='submitted'),
        sa.Column('submitted_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('submitted_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('reviewed_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), 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('unlocked_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('unlocked_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
    )
    op.create_index('ix_penyetoran_batch_spbu', 'penyetoran_batch', ['spbu_id'])

    # 2. Drop old penyetoran table and recreate
    op.drop_table('penyetoran')

    op.create_table(
        'penyetoran',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('spbu_id', sa.Integer(), sa.ForeignKey('master_spbu.id', ondelete='CASCADE'), nullable=False),
        sa.Column('laporan_shift_id', sa.Integer(), sa.ForeignKey('laporan_shift.id', ondelete='CASCADE'), nullable=False),
        sa.Column('tanggal', sa.Date(), nullable=False),
        sa.Column('shift_id', sa.Integer(), sa.ForeignKey('master_spbu_shift.id', ondelete='RESTRICT'), nullable=False),
        sa.Column('jumlah_kas', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('jumlah_non_kas', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('total_penjualan', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('catatan', sa.Text(), nullable=True),
        sa.Column('bukti_url', sa.String(500), nullable=True),
        sa.Column('status', sa.String(20), nullable=False, server_default='draft'),
        sa.Column('batch_id', sa.Integer(), sa.ForeignKey('penyetoran_batch.id', ondelete='SET NULL'), nullable=True),
        sa.Column('created_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
        sa.UniqueConstraint('laporan_shift_id', name='uq_penyetoran_laporan_shift'),
    )
    op.create_index('ix_penyetoran_spbu_tanggal', 'penyetoran', ['spbu_id', 'tanggal'])
    op.create_index('ix_penyetoran_batch_id', 'penyetoran', ['batch_id'])


def downgrade() -> None:
    op.drop_table('penyetoran')
    op.drop_table('penyetoran_batch')
    # Recreate original penyetoran table
    op.create_table(
        'penyetoran',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('spbu_id', sa.Integer(), sa.ForeignKey('master_spbu.id', ondelete='CASCADE'), nullable=False),
        sa.Column('tanggal', sa.Date(), nullable=False),
        sa.Column('total_penjualan', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('total_expenses', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('jumlah_setor', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('catatan', sa.Text(), nullable=True),
        sa.Column('bukti_url', sa.String(500), nullable=True),
        sa.Column('status', sa.String(20), nullable=False, server_default='draft'),
        sa.Column('created_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('confirmed_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('confirmed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('catatan_konfirmasi', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
        sa.UniqueConstraint('spbu_id', 'tanggal', name='uq_penyetoran_spbu_tanggal'),
    )