"""penjualan_redesign

Revision ID: b3a3165ce53d
Revises: sp01_add_sapras
Create Date: 2026-04-18 16:57:52.559372

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = 'b3a3165ce53d'
down_revision: Union[str, Sequence[str], None] = 'sp01_add_sapras'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 1. Add new enum values to PostgreSQL (cannot remove old ones)
    op.execute("ALTER TYPE statuslaporanenum ADD VALUE IF NOT EXISTS 'for_approval'")
    op.execute("ALTER TYPE statuslaporanenum ADD VALUE IF NOT EXISTS 'reconciled'")

    # 2. New columns on laporan_shift
    op.add_column('laporan_shift', sa.Column('for_approval_by_id', sa.Integer(), sa.ForeignKey('master_user.id'), nullable=True))
    op.add_column('laporan_shift', sa.Column('for_approval_at', sa.DateTime(timezone=True), nullable=True))
    op.add_column('laporan_shift', sa.Column('rejected_by_id', sa.Integer(), sa.ForeignKey('master_user.id'), nullable=True))
    op.add_column('laporan_shift', sa.Column('rejected_at', sa.DateTime(timezone=True), nullable=True))
    op.add_column('laporan_shift', sa.Column('rejected_reason', sa.String(), nullable=True))
    op.add_column('laporan_shift', sa.Column('recall_reason', sa.String(), nullable=True))
    op.add_column('laporan_shift', sa.Column('pulled_back_by_id', sa.Integer(), sa.ForeignKey('master_user.id'), nullable=True))
    op.add_column('laporan_shift', sa.Column('pulled_back_at', sa.DateTime(timezone=True), nullable=True))
    op.add_column('laporan_shift', sa.Column('pulled_back_reason', sa.String(), nullable=True))

    # 3. Add tera column to penjualan_nozzle
    op.add_column('penjualan_nozzle', sa.Column('tera', sa.Numeric(precision=15, scale=3), nullable=False, server_default='0'))

    # 4. Create laporan_shift_island_foto table
    op.create_table(
        'laporan_shift_island_foto',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('laporan_shift_id', sa.Integer(), sa.ForeignKey('laporan_shift.id', ondelete='CASCADE'), nullable=False),
        sa.Column('island_id', sa.Integer(), sa.ForeignKey('master_spbu_island.id'), nullable=False),
        sa.Column('foto_url', sa.String(), nullable=False),
        sa.Column('uploaded_by_id', sa.Integer(), sa.ForeignKey('master_user.id'), nullable=False),
        sa.Column('uploaded_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('laporan_shift_id', 'island_id', name='uq_laporan_island_foto'),
    )

    # 5. Create laporan_shift_dokumen table
    op.create_table(
        'laporan_shift_dokumen',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('laporan_shift_id', sa.Integer(), sa.ForeignKey('laporan_shift.id', ondelete='CASCADE'), nullable=False),
        sa.Column('file_url', sa.String(), nullable=False),
        sa.Column('file_name', sa.String(), nullable=False),
        sa.Column('file_type', sa.String(), nullable=False),
        sa.Column('uploaded_by_id', sa.Integer(), sa.ForeignKey('master_user.id'), nullable=False),
        sa.Column('uploaded_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
    )


def downgrade() -> None:
    op.drop_table('laporan_shift_dokumen')
    op.drop_table('laporan_shift_island_foto')
    op.drop_column('penjualan_nozzle', 'tera')
    for col in ['pulled_back_reason', 'pulled_back_at', 'pulled_back_by_id',
                'recall_reason', 'rejected_reason', 'rejected_at', 'rejected_by_id',
                'for_approval_at', 'for_approval_by_id']:
        op.drop_column('laporan_shift', col)
    # Note: cannot remove enum values from PostgreSQL