"""create requisitions tables

Revision ID: 6b2c3d4e5f01
Revises: 5f6a7b8c9d0e
Create Date: 2026-08-09

A purchase-requisition module: staff and managers raise orders (with line items),
the admin approves/rejects and later completes them. Includes the status-history
trail so every decision is attributable.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op


revision: str = '6b2c3d4e5f01'
down_revision: Union[str, None] = '5f6a7b8c9d0e'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        'requisitions',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column(
            'req_no',
            sa.String(length=30),
            nullable=False,
        ),
        sa.Column(
            'status',
            sa.Enum('pending', 'approved', 'rejected', 'completed', name='requisition_status'),
            server_default='pending',
            nullable=False,
        ),
        sa.Column('purpose', sa.Text(), nullable=False),
        sa.Column('notes', sa.Text(), nullable=True),
        sa.Column(
            'requested_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('requested_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column(
            'approved_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('approved_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            'rejected_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('rejected_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            'completed_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            'created_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column(
            'is_deleted', sa.Boolean(), server_default=sa.text('false'), nullable=False
        ),
        sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index('ix_requisitions_req_no', 'requisitions', ['req_no'])
    op.create_index('ix_requisitions_status', 'requisitions', ['status'])
    op.create_index('ix_requisitions_requested_by_id', 'requisitions', ['requested_by_id'])
    op.create_index('ix_requisitions_requested_at', 'requisitions', ['requested_at'])
    op.create_index('ix_requisitions_created_at', 'requisitions', ['created_at'])
    op.create_index('ix_requisitions_is_deleted', 'requisitions', ['is_deleted'])
    op.create_index('ix_requisitions_status_requested_at', 'requisitions', ['status', 'requested_at'])
    op.create_index(
        'uq_requisitions_req_no_active',
        'requisitions',
        ['req_no'],
        unique=True,
        postgresql_where=sa.text('is_deleted = false'),
    )

    op.create_table(
        'requisition_items',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column(
            'requisition_id',
            sa.Integer(),
            sa.ForeignKey('requisitions.id', ondelete='CASCADE'),
            nullable=False,
        ),
        sa.Column('item_name', sa.String(length=200), nullable=False),
        sa.Column('quantity', sa.Integer(), nullable=False),
        sa.Column('unit', sa.String(length=20), server_default='pcs', nullable=False),
        sa.CheckConstraint('quantity > 0', name='quantity_positive'),
    )
    op.create_index('ix_requisition_items_requisition_id', 'requisition_items', ['requisition_id'])

    op.create_table(
        'requisition_status_history',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column(
            'requisition_id',
            sa.Integer(),
            sa.ForeignKey('requisitions.id', ondelete='CASCADE'),
            nullable=False,
        ),
        sa.Column(
            'from_status',
            sa.Enum('pending', 'approved', 'rejected', 'completed', name='requisition_status'),
            nullable=True,
        ),
        sa.Column(
            'to_status',
            sa.Enum('pending', 'approved', 'rejected', 'completed', name='requisition_status'),
            nullable=False,
        ),
        sa.Column(
            'changed_by_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='SET NULL'), nullable=True
        ),
        sa.Column('changed_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('remarks', sa.Text(), nullable=True),
    )
    op.create_index(
        'ix_requisition_status_history_requisition_id',
        'requisition_status_history',
        ['requisition_id'],
    )
    op.create_index(
        'ix_requisition_status_history_changed_at',
        'requisition_status_history',
        ['changed_at'],
    )


def downgrade() -> None:
    op.drop_index('ix_requisition_status_history_changed_at', table_name='requisition_status_history')
    op.drop_index('ix_requisition_status_history_requisition_id', table_name='requisition_status_history')
    op.drop_table('requisition_status_history')

    op.drop_index('ix_requisition_items_requisition_id', table_name='requisition_items')
    op.drop_table('requisition_items')

    op.drop_index('uq_requisitions_req_no_active', table_name='requisitions')
    op.drop_index('ix_requisitions_status_requested_at', table_name='requisitions')
    op.drop_index('ix_requisitions_is_deleted', table_name='requisitions')
    op.drop_index('ix_requisitions_created_at', table_name='requisitions')
    op.drop_index('ix_requisitions_requested_at', table_name='requisitions')
    op.drop_index('ix_requisitions_requested_by_id', table_name='requisitions')
    op.drop_index('ix_requisitions_status', table_name='requisitions')
    op.drop_index('ix_requisitions_req_no', table_name='requisitions')
    op.drop_table('requisitions')

    op.execute('DROP TYPE IF EXISTS requisition_status')