"""Add vendor portal models

Revision ID: 695569b912d2
Revises: 8035782e3e9c
Create Date: 2026-06-25 13:18:13.890162

"""
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 = '695569b912d2'
down_revision: Union[str, None] = '8035782e3e9c'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('inspection_document',
    sa.Column('doc_id', sa.UUID(), nullable=False),
    sa.Column('entity_type', sa.String(length=50), nullable=False),
    sa.Column('entity_id', sa.UUID(), nullable=False),
    sa.Column('doc_type', sa.String(length=50), nullable=False),
    sa.Column('file_path', sa.Text(), nullable=False),
    sa.Column('uploaded_by', sa.UUID(), nullable=False),
    sa.Column('uploaded_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('doc_id')
    )
    op.create_table('login_users',
    sa.Column('user_id', sa.UUID(), nullable=False),
    sa.Column('employee_code', sa.String(length=30), nullable=True),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('mobile', sa.String(length=20), nullable=True),
    sa.Column('email', sa.String(length=255), nullable=True),
    sa.Column('password_hash', sa.String(length=255), nullable=False),
    sa.Column('role_code', sa.String(length=50), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('user_id'),
    sa.UniqueConstraint('employee_code')
    )
    op.create_table('rejection_reason_master',
    sa.Column('reason_code', sa.String(length=50), nullable=False),
    sa.Column('description', sa.String(length=255), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.PrimaryKeyConstraint('reason_code')
    )
    op.create_table('notification_log',
    sa.Column('notification_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=True),
    sa.Column('recipient', sa.String(length=255), nullable=False),
    sa.Column('notification_type', sa.String(length=50), nullable=False),
    sa.Column('subject', sa.String(length=255), nullable=True),
    sa.Column('content', sa.Text(), nullable=True),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('sent_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('notification_id')
    )
    op.create_table('vendor_dispute_log',
    sa.Column('dispute_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('entity_type', sa.String(length=30), nullable=False),
    sa.Column('entity_id', sa.UUID(), nullable=False),
    sa.Column('dispute_type', sa.String(length=50), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('resolution_note', sa.Text(), nullable=True),
    sa.Column('resolved_by', sa.UUID(), nullable=True),
    sa.Column('resolved_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('dispute_id')
    )
    op.create_table('vendor_portal_action_log',
    sa.Column('action_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('action_type', sa.String(length=50), nullable=False),
    sa.Column('entity_type', sa.String(length=50), nullable=True),
    sa.Column('entity_id', sa.UUID(), nullable=True),
    sa.Column('action_details', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('ip_address', sa.String(length=50), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('action_id')
    )
    op.create_table('vendor_portal_admin_action_log',
    sa.Column('action_id', sa.UUID(), nullable=False),
    sa.Column('admin_user_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=True),
    sa.Column('action_type', sa.String(length=50), nullable=False),
    sa.Column('action_details', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('ip_address', sa.String(length=50), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('action_id')
    )
    op.create_table('vendor_portal_log',
    sa.Column('log_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('session_token', sa.String(length=128), nullable=False),
    sa.Column('ip_address', sa.String(length=50), nullable=True),
    sa.Column('user_agent', sa.Text(), nullable=True),
    sa.Column('issued_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('invalidated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('log_id'),
    sa.UniqueConstraint('session_token')
    )
    op.create_table('vendor_scorecard',
    sa.Column('scorecard_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('period_start', sa.Date(), nullable=False),
    sa.Column('period_end', sa.Date(), nullable=False),
    sa.Column('deliveries_total', sa.Integer(), nullable=False),
    sa.Column('deliveries_on_time', sa.Integer(), nullable=False),
    sa.Column('batches_total', sa.Integer(), nullable=False),
    sa.Column('batches_first_pass', sa.Integer(), nullable=False),
    sa.Column('rejection_count', sa.Integer(), nullable=False),
    sa.Column('ncr_count', sa.Integer(), nullable=False),
    sa.Column('avg_lead_time_days', sa.Numeric(precision=10, scale=2), nullable=True),
    sa.Column('calculated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('scorecard_id')
    )
    op.create_table('asn_log',
    sa.Column('asn_id', sa.UUID(), nullable=False),
    sa.Column('asn_number', sa.String(length=50), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('po_id', sa.UUID(), nullable=False),
    sa.Column('expected_date', sa.Date(), nullable=False),
    sa.Column('vehicle_number', sa.String(length=50), nullable=True),
    sa.Column('driver_name', sa.String(length=100), nullable=True),
    sa.Column('arrival_window', sa.String(length=50), nullable=True),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('asn_id'),
    sa.UniqueConstraint('asn_number')
    )
    op.create_table('po_tracking_log',
    sa.Column('tracking_id', sa.UUID(), nullable=False),
    sa.Column('po_id', sa.UUID(), nullable=False),
    sa.Column('event_type', sa.String(length=50), nullable=False),
    sa.Column('event_date', sa.DateTime(timezone=True), nullable=False),
    sa.Column('tracking_ref', sa.String(length=100), nullable=True),
    sa.Column('notes', sa.Text(), nullable=True),
    sa.Column('created_by', sa.UUID(), nullable=True),
    sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], ),
    sa.PrimaryKeyConstraint('tracking_id')
    )
    op.create_table('asn_detail',
    sa.Column('asn_detail_id', sa.UUID(), nullable=False),
    sa.Column('asn_id', sa.UUID(), nullable=False),
    sa.Column('po_detail_id', sa.UUID(), nullable=False),
    sa.Column('qty_shipped', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('vendor_batch_ref', sa.String(length=100), nullable=True),
    sa.ForeignKeyConstraint(['asn_id'], ['asn_log.asn_id'], ),
    sa.ForeignKeyConstraint(['po_detail_id'], ['rm_purchase_order_detail.po_detail_id'], ),
    sa.PrimaryKeyConstraint('asn_detail_id')
    )
    op.create_table('rejection_log',
    sa.Column('rejection_id', sa.UUID(), nullable=False),
    sa.Column('grn_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('rln', sa.String(length=50), nullable=False),
    sa.Column('reason_code', sa.String(length=50), nullable=False),
    sa.Column('total_qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('disposition_deadline', sa.DateTime(timezone=True), nullable=True),
    sa.Column('vendor_response', sa.String(length=50), nullable=True),
    sa.Column('vendor_responded_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['grn_id'], ['rm_receiving_log.grn_id'], ),
    sa.ForeignKeyConstraint(['reason_code'], ['rejection_reason_master.reason_code'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('rejection_id'),
    sa.UniqueConstraint('rln')
    )
    op.create_table('debit_note',
    sa.Column('debit_note_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('rejection_id', sa.UUID(), nullable=True),
    sa.Column('dn_number', sa.String(length=50), nullable=False),
    sa.Column('amount', sa.Numeric(precision=14, scale=2), nullable=False),
    sa.Column('reason', sa.Text(), nullable=False),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rejection_id'], ['rejection_log.rejection_id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('debit_note_id'),
    sa.UniqueConstraint('dn_number')
    )
    op.create_table('ncr_log',
    sa.Column('ncr_id', sa.UUID(), nullable=False),
    sa.Column('rejection_id', sa.UUID(), nullable=False),
    sa.Column('vendor_id', sa.UUID(), nullable=False),
    sa.Column('ncr_number', sa.String(length=50), nullable=False),
    sa.Column('defect_description', sa.Text(), nullable=False),
    sa.Column('disposition_code', sa.String(length=50), nullable=True),
    sa.Column('car_due', sa.DateTime(timezone=True), nullable=True),
    sa.Column('car_submitted_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('car_root_cause', sa.Text(), nullable=True),
    sa.Column('car_action_taken', sa.Text(), nullable=True),
    sa.Column('car_prevention', sa.Text(), nullable=True),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rejection_id'], ['rejection_log.rejection_id'], ),
    sa.ForeignKeyConstraint(['vendor_id'], ['vendor_master.vendor_id'], ),
    sa.PrimaryKeyConstraint('ncr_id'),
    sa.UniqueConstraint('ncr_number')
    )
    op.create_table('rejection_log_detail',
    sa.Column('rejection_detail_id', sa.UUID(), nullable=False),
    sa.Column('rejection_id', sa.UUID(), nullable=False),
    sa.Column('grn_detail_id', sa.UUID(), nullable=False),
    sa.Column('qty', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('defect_detail', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['grn_detail_id'], ['grn_detail.grn_detail_id'], ),
    sa.ForeignKeyConstraint(['rejection_id'], ['rejection_log.rejection_id'], ),
    sa.PrimaryKeyConstraint('rejection_detail_id')
    )
    op.create_table('rejection_photo',
    sa.Column('photo_id', sa.UUID(), nullable=False),
    sa.Column('rejection_id', sa.UUID(), nullable=False),
    sa.Column('file_path', sa.Text(), nullable=False),
    sa.Column('uploaded_by', sa.UUID(), nullable=False),
    sa.Column('uploaded_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['rejection_id'], ['rejection_log.rejection_id'], ),
    sa.PrimaryKeyConstraint('photo_id')
    )
    op.drop_index('ix_ecfv_entity', table_name='entity_custom_field_values')
    op.drop_table('entity_custom_field_values')
    op.drop_table('entity_custom_fields')
    op.add_column('vendor_master', sa.Column('vendor_code', sa.String(length=50), nullable=True))
    op.add_column('vendor_master', sa.Column('portal_enabled', sa.Boolean(), nullable=False, server_default='false'))
    op.add_column('vendor_master', sa.Column('portal_username', sa.String(length=100), nullable=True))
    op.add_column('vendor_master', sa.Column('portal_password_hash', sa.String(length=255), nullable=True))
    op.create_unique_constraint(None, 'vendor_master', ['portal_username'])
    op.create_unique_constraint(None, 'vendor_master', ['vendor_code'])
    # ### end Alembic commands ###

    # Add Immutable Triggers
    op.execute("""
        CREATE OR REPLACE FUNCTION fn_block_immutable()
        RETURNS trigger AS $$
        BEGIN
            RAISE EXCEPTION 'Updates and deletes are not allowed on this immutable table.';
        END;
        $$ LANGUAGE plpgsql;
    """)
    op.execute("""
        CREATE TRIGGER trg_vendor_portal_action_log_immutable
        BEFORE UPDATE OR DELETE ON vendor_portal_action_log
        FOR EACH ROW EXECUTE FUNCTION fn_block_immutable();
    """)
    op.execute("""
        CREATE TRIGGER trg_vendor_portal_admin_action_log_immutable
        BEFORE UPDATE OR DELETE ON vendor_portal_admin_action_log
        FOR EACH ROW EXECUTE FUNCTION fn_block_immutable();
    """)


def downgrade() -> None:
    # Drop Immutable Triggers
    op.execute("DROP TRIGGER IF EXISTS trg_vendor_portal_admin_action_log_immutable ON vendor_portal_admin_action_log;")
    op.execute("DROP TRIGGER IF EXISTS trg_vendor_portal_action_log_immutable ON vendor_portal_action_log;")

    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'vendor_master', type_='unique')
    op.drop_constraint(None, 'vendor_master', type_='unique')
    op.drop_column('vendor_master', 'portal_password_hash')
    op.drop_column('vendor_master', 'portal_username')
    op.drop_column('vendor_master', 'portal_enabled')
    op.drop_column('vendor_master', 'vendor_code')
    op.create_table('entity_custom_field_values',
    sa.Column('value_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('field_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('entity_type', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('entity_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('field_value', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['field_id'], ['entity_custom_fields.field_id'], name='entity_custom_field_values_field_id_fkey'),
    sa.PrimaryKeyConstraint('value_id', name='entity_custom_field_values_pkey')
    )
    op.create_index('ix_ecfv_entity', 'entity_custom_field_values', ['entity_type', 'entity_id'], unique=False)
    op.create_table('entity_custom_fields',
    sa.Column('field_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('entity_type', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('field_key', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('field_label', sa.VARCHAR(length=150), autoincrement=False, nullable=False),
    sa.Column('field_type', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('dropdown_options', postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.Column('is_required', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('is_visible_in_list', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('sort_order', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('field_id', name='entity_custom_fields_pkey')
    )
    op.drop_table('rejection_photo')
    op.drop_table('rejection_log_detail')
    op.drop_table('ncr_log')
    op.drop_table('debit_note')
    op.drop_table('rejection_log')
    op.drop_table('asn_detail')
    op.drop_table('po_tracking_log')
    op.drop_table('asn_log')
    op.drop_table('vendor_scorecard')
    op.drop_table('vendor_portal_log')
    op.drop_table('vendor_portal_admin_action_log')
    op.drop_table('vendor_portal_action_log')
    op.drop_table('vendor_dispute_log')
    op.drop_table('notification_log')
    op.drop_table('rejection_reason_master')
    op.drop_table('login_users')
    op.drop_table('inspection_document')
    # ### end Alembic commands ###