"""Add monthly procurement planning tables.

Revision ID: f6a1b2c3d4e5
Revises: e72c304d4222
Create Date: 2026-09-04

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    tables = inspector.get_table_names()
    if 'monthly_procurement_plan' not in tables:
        op.create_table(
            'monthly_procurement_plan',
            sa.Column('plan_id', sa.UUID(), nullable=False),
            sa.Column('vendor_id', sa.UUID(), nullable=False),
            sa.Column('plan_year', sa.Integer(), nullable=False),
            sa.Column('plan_month', sa.Integer(), nullable=False),
            sa.Column('notes', sa.Text(), nullable=True),
            sa.Column('created_by', sa.UUID(), nullable=True),
            sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
            sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id']),
            sa.PrimaryKeyConstraint('plan_id'),
            sa.UniqueConstraint('vendor_id', 'plan_year', 'plan_month'),
        )
    if 'monthly_procurement_plan_line' not in tables:
        op.create_table(
            'monthly_procurement_plan_line',
            sa.Column('line_id', sa.UUID(), nullable=False),
            sa.Column('plan_id', sa.UUID(), nullable=False),
            sa.Column('rm_id', sa.UUID(), nullable=False),
            sa.Column('planned_qty', sa.Numeric(precision=14, scale=3), nullable=False),
            sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(['plan_id'], ['monthly_procurement_plan.plan_id']),
            sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id']),
            sa.PrimaryKeyConstraint('line_id'),
            sa.UniqueConstraint('plan_id', 'rm_id'),
        )


def downgrade() -> None:
    op.drop_table('monthly_procurement_plan_line')
    op.drop_table('monthly_procurement_plan')