"""Add end_to_end_cycle table.

Revision ID: o1e2f3a4b5c6
Revises: n0d1e2f3a4b5
Create Date: 2026-04-03
"""
from alembic import op
import sqlalchemy as sa

revision = 'o1e2f3a4b5c6'
down_revision = 'n0d1e2f3a4b5'
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        'end_to_end_cycle',
        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('tangki_id', sa.Integer(), sa.ForeignKey('master_spbu_tangki.id', ondelete='RESTRICT'), nullable=False),
        sa.Column('status', sa.String(10), nullable=False, server_default='open'),
        sa.Column('tanggal_mulai', sa.Date(), nullable=False),
        sa.Column('tanggal_selesai', sa.Date(), nullable=True),
        sa.Column('dead_stock_awal', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('dead_stock_akhir', sa.Numeric(15, 3), nullable=True),
        sa.Column('total_penerimaan', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('total_penjualan', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('total_pemindahan_in', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('total_pemindahan_out', sa.Numeric(15, 3), nullable=False, server_default='0'),
        sa.Column('losses_aktual', sa.Numeric(15, 3), nullable=True),
        sa.Column('losses_pct', sa.Numeric(8, 4), nullable=True),
        sa.Column('started_by_id', sa.Integer(), sa.ForeignKey('master_user.id', ondelete='SET NULL'), nullable=True),
        sa.Column('closed_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.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    )
    op.create_index('ix_e2e_spbu_id', 'end_to_end_cycle', ['spbu_id'])
    # Partial unique index: max 1 open cycle per tank
    op.create_index(
        'ix_e2e_tangki_open',
        'end_to_end_cycle',
        ['tangki_id'],
        unique=True,
        postgresql_where=sa.text("status = 'open'"),
    )


def downgrade() -> None:
    op.drop_index('ix_e2e_tangki_open', table_name='end_to_end_cycle')
    op.drop_index('ix_e2e_spbu_id', table_name='end_to_end_cycle')
    op.drop_table('end_to_end_cycle')