"""Dual teller readings and new threshold fields.

Replaces:
  - master_spbu.losses_threshold_liter
    → losses_threshold_penerimaan_pct (default 0.150)
    → losses_threshold_penjualan_pct  (default 0.500)
    → teller_discrepancy_threshold_pct (default 0.300)

  - master_spbu_nozzle.teller_terakhir
    → teller_terakhir_manual
    → teller_terakhir_digital
    + primary_teller VARCHAR(10) DEFAULT 'manual'

  - penjualan_nozzle.teller_awal / teller_akhir
    → teller_awal_manual / teller_akhir_manual
    → teller_awal_digital / teller_akhir_digital

Revision ID: d4e5f6a1b2c3
Revises: c3d4e5f6a1b2
Create Date: 2026-04-02
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = 'd4e5f6a1b2c3'
down_revision = 'c3d4e5f6a1b2'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── master_spbu: replace losses_threshold_liter with 3 pct thresholds ──
    op.add_column('master_spbu', sa.Column(
        'losses_threshold_penerimaan_pct',
        sa.Numeric(precision=6, scale=3),
        nullable=False,
        server_default='0.150',
    ))
    op.add_column('master_spbu', sa.Column(
        'losses_threshold_penjualan_pct',
        sa.Numeric(precision=6, scale=3),
        nullable=False,
        server_default='0.500',
    ))
    op.add_column('master_spbu', sa.Column(
        'teller_discrepancy_threshold_pct',
        sa.Numeric(precision=6, scale=3),
        nullable=False,
        server_default='0.300',
    ))
    op.drop_column('master_spbu', 'losses_threshold_liter')

    # ── master_spbu_nozzle: replace teller_terakhir with manual/digital ──
    op.add_column('master_spbu_nozzle', sa.Column(
        'teller_terakhir_manual',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    op.add_column('master_spbu_nozzle', sa.Column(
        'teller_terakhir_digital',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    op.add_column('master_spbu_nozzle', sa.Column(
        'primary_teller',
        sa.String(length=10),
        nullable=False,
        server_default='manual',
    ))
    # Migrate existing data: old teller_terakhir → manual column
    op.execute(
        "UPDATE master_spbu_nozzle SET teller_terakhir_manual = teller_terakhir"
    )
    op.drop_column('master_spbu_nozzle', 'teller_terakhir')

    # ── penjualan_nozzle: replace 2 columns with 4 ──
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_awal_manual',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_akhir_manual',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_awal_digital',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_akhir_digital',
        sa.Numeric(precision=15, scale=3),
        nullable=True,
    ))
    # Migrate existing rows: old teller_awal/akhir → manual columns; digital = 0
    op.execute("""
        UPDATE penjualan_nozzle
        SET teller_awal_manual   = teller_awal,
            teller_akhir_manual  = teller_akhir,
            teller_awal_digital  = 0,
            teller_akhir_digital = 0
    """)
    # Make NOT NULL after data migration
    op.alter_column('penjualan_nozzle', 'teller_awal_manual', nullable=False)
    op.alter_column('penjualan_nozzle', 'teller_akhir_manual', nullable=False)
    op.alter_column('penjualan_nozzle', 'teller_awal_digital', nullable=False)
    op.alter_column('penjualan_nozzle', 'teller_akhir_digital', nullable=False)
    op.drop_column('penjualan_nozzle', 'teller_awal')
    op.drop_column('penjualan_nozzle', 'teller_akhir')


def downgrade() -> None:
    # ── penjualan_nozzle: restore 2 columns ──
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_awal', sa.Numeric(precision=15, scale=3), nullable=True,
    ))
    op.add_column('penjualan_nozzle', sa.Column(
        'teller_akhir', sa.Numeric(precision=15, scale=3), nullable=True,
    ))
    op.execute("""
        UPDATE penjualan_nozzle
        SET teller_awal  = teller_awal_manual,
            teller_akhir = teller_akhir_manual
    """)
    op.alter_column('penjualan_nozzle', 'teller_awal', nullable=False)
    op.alter_column('penjualan_nozzle', 'teller_akhir', nullable=False)
    op.drop_column('penjualan_nozzle', 'teller_awal_manual')
    op.drop_column('penjualan_nozzle', 'teller_akhir_manual')
    op.drop_column('penjualan_nozzle', 'teller_awal_digital')
    op.drop_column('penjualan_nozzle', 'teller_akhir_digital')

    # ── master_spbu_nozzle: restore single teller_terakhir ──
    op.add_column('master_spbu_nozzle', sa.Column(
        'teller_terakhir', sa.Numeric(precision=15, scale=3), nullable=True,
    ))
    op.execute(
        "UPDATE master_spbu_nozzle SET teller_terakhir = teller_terakhir_manual"
    )
    op.drop_column('master_spbu_nozzle', 'teller_terakhir_manual')
    op.drop_column('master_spbu_nozzle', 'teller_terakhir_digital')
    op.drop_column('master_spbu_nozzle', 'primary_teller')

    # ── master_spbu: restore losses_threshold_liter ──
    op.add_column('master_spbu', sa.Column(
        'losses_threshold_liter',
        sa.Numeric(precision=15, scale=3),
        nullable=False,
        server_default='50.000',
    ))
    op.drop_column('master_spbu', 'losses_threshold_penerimaan_pct')
    op.drop_column('master_spbu', 'losses_threshold_penjualan_pct')
    op.drop_column('master_spbu', 'teller_discrepancy_threshold_pct')