"""add_po_history_and_closed_status

Revision ID: 9fcd516d4f56
Revises: fa7094d4cea7
Create Date: 2026-07-07 19:38:44.514569

"""
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 = '9fcd516d4f56'
down_revision: Union[str, None] = 'fa7094d4cea7'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Create rm_po_history table
    op.create_table('rm_po_history',
        sa.Column('history_id', sa.UUID(), nullable=False),
        sa.Column('po_id', sa.UUID(), nullable=True),
        sa.Column('action', sa.String(length=50), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('created_by_name', sa.String(length=100), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], ),
        sa.PrimaryKeyConstraint('history_id')
    )
    op.create_index(op.f('ix_rm_po_history_history_id'), 'rm_po_history', ['history_id'], unique=False)

    # Insert CLOSED status if it doesn't exist
    op.execute(
        "INSERT INTO po_status_master (id, code, name, description, created_at) "
        "SELECT gen_random_uuid(), 'CLOSED', 'Closed', 'Closed status', NOW() "
        "WHERE NOT EXISTS (SELECT 1 FROM po_status_master WHERE code = 'CLOSED')"
    )


def downgrade() -> None:
    # Delete CLOSED status
    op.execute("DELETE FROM po_status_master WHERE code = 'CLOSED'")
    
    # Drop rm_po_history table
    op.drop_index(op.f('ix_rm_po_history_history_id'), table_name='rm_po_history')
    op.drop_table('rm_po_history')