"""Initial Schema Creation

Revision ID: 0001_initial_schema
Revises: None
Create Date: 2026-05-29 17:30:00.000000

"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

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

def upgrade() -> None:
    # 1. Lenders Table
    op.create_table(
        'lenders',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=255), nullable=False),
        sa.Column('code', sa.String(length=50), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_lenders_id'), 'lenders', ['id'], unique=False)
    op.create_index(op.f('ix_lenders_code'), 'lenders', ['code'], unique=True)

    # 2. Products Table
    op.create_table(
        'products',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('lender_id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=255), nullable=False),
        sa.Column('code', sa.String(length=100), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True),
        sa.ForeignKeyConstraint(['lender_id'], ['lenders.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_products_id'), 'products', ['id'], unique=False)
    op.create_index(op.f('ix_products_code'), 'products', ['code'], unique=False)

    # 3. Charge Master Table
    op.create_table(
        'charge_master',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('code', sa.String(length=100), nullable=False),
        sa.Column('name', sa.String(length=255), nullable=False),
        sa.Column('description', sa.String(length=500), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_charge_master_id'), 'charge_master', ['id'], unique=False)
    op.create_index(op.f('ix_charge_master_code'), 'charge_master', ['code'], unique=True)

    # Helper function for configuration table columns
    def config_cols():
        return [
            sa.Column('version', sa.Integer(), nullable=False, default=1),
            sa.Column('is_active', sa.Boolean(), nullable=False, default=True),
            sa.Column('effective_from', sa.DateTime(timezone=True), nullable=False),
            sa.Column('effective_to', sa.DateTime(timezone=True), nullable=True),
            sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
            sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
            sa.Column('created_by', sa.String(length=100), nullable=True),
            sa.Column('updated_by', sa.String(length=100), nullable=True),
        ]

    # 4. Product Configurations Table
    op.create_table(
        'product_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_product_configurations_id'), 'product_configurations', ['id'], unique=False)

    # 5. Charge Configurations Table
    op.create_table(
        'charge_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('charge_master_id', sa.Integer(), nullable=False),
        sa.Column('charge_type', sa.String(length=50), nullable=False),
        sa.Column('value', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('is_taxable', sa.Boolean(), nullable=False, default=True),
        sa.Column('tax_percentage', sa.Numeric(precision=5, scale=2), nullable=False, default=18.00),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['charge_master_id'], ['charge_master.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_charge_configurations_id'), 'charge_configurations', ['id'], unique=False)

    # 6. Allocation Configurations Table
    op.create_table(
        'allocation_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('sequence_type', sa.String(length=50), nullable=False),
        sa.Column('priority_sequence', sa.JSON(), nullable=False),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_allocation_configurations_id'), 'allocation_configurations', ['id'], unique=False)

    # 7. Collection Configurations Table
    op.create_table(
        'collection_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('allocation_strategy', sa.String(length=100), nullable=False),
        sa.Column('advance_collection_strategy', sa.String(length=100), nullable=False),
        sa.Column('excess_refund_required', sa.Boolean(), nullable=False, default=True),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_collection_configurations_id'), 'collection_configurations', ['id'], unique=False)

    # 8. Settlement Configurations Table
    op.create_table(
        'settlement_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('max_waiver_percentage', sa.Numeric(precision=5, scale=2), nullable=False),
        sa.Column('maker_checker_required', sa.Boolean(), nullable=False, default=True),
        sa.Column('allowed_write_off_limit', sa.Numeric(precision=15, scale=4), nullable=False),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_settlement_configurations_id'), 'settlement_configurations', ['id'], unique=False)

    # 9. Foreclosure Configurations Table
    op.create_table(
        'foreclosure_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('charge_type', sa.String(length=50), nullable=False),
        sa.Column('charge_value', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('lock_in_period_months', sa.Integer(), nullable=False, default=0),
        sa.Column('rebate_percentage', sa.Numeric(precision=5, scale=2), nullable=False, default=0.00),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_foreclosure_configurations_id'), 'foreclosure_configurations', ['id'], unique=False)

    # 10. NPA Configurations Table
    op.create_table(
        'npa_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('dpd_threshold', sa.Integer(), nullable=False, default=90),
        sa.Column('bucket_classification_rules', sa.JSON(), nullable=False),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_npa_configurations_id'), 'npa_configurations', ['id'], unique=False)

    # 11. Workflow Configurations Table
    op.create_table(
        'workflow_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('workflow_type', sa.String(length=100), nullable=False),
        sa.Column('approval_levels', sa.Integer(), nullable=False, default=1),
        sa.Column('role_hierarchy', sa.JSON(), nullable=False),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_workflow_configurations_id'), 'workflow_configurations', ['id'], unique=False)

    # 12. Disbursement Configurations Table
    op.create_table(
        'disbursement_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('disbursement_logic', sa.String(length=100), nullable=False),
        sa.Column('fixed_day_of_month', sa.Integer(), nullable=True),
        sa.Column('cut_off_hour', sa.Integer(), nullable=False, default=18),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_disbursement_configurations_id'), 'disbursement_configurations', ['id'], unique=False)

    # 13. Interest Configurations Table
    op.create_table(
        'interest_configurations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('calculation_basis', sa.String(length=50), nullable=False),
        sa.Column('emi_type', sa.String(length=50), nullable=False),
        sa.Column('repayment_frequency', sa.String(length=50), nullable=False, default='MONTHLY'),
        sa.Column('grace_period_days', sa.Integer(), nullable=False, default=0),
        *config_cols(),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_interest_configurations_id'), 'interest_configurations', ['id'], unique=False)

    # 14. Loans Table
    op.create_table(
        'loans',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('lender_id', sa.Integer(), nullable=False),
        sa.Column('product_id', sa.Integer(), nullable=False),
        sa.Column('loan_amount', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('approved_amount', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('interest_rate', sa.Numeric(precision=5, scale=2), nullable=False),
        sa.Column('tenure_months', sa.Integer(), nullable=False),
        sa.Column('state', sa.String(length=50), nullable=False, default='DRAFT'),
        sa.Column('disbursed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.ForeignKeyConstraint(['lender_id'], ['lenders.id'], ondelete='RESTRICT'),
        sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_loans_id'), 'loans', ['id'], unique=False)

    # 15. Repayment Schedules Table
    op.create_table(
        'repayment_schedules',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('loan_id', sa.Integer(), nullable=False),
        sa.Column('installment_no', sa.Integer(), nullable=False),
        sa.Column('due_date', sa.Date(), nullable=False),
        sa.Column('principal_due', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('interest_due', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('charges_due', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('principal_paid', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('interest_paid', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('charges_paid', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('state', sa.String(length=50), nullable=False, default='UNPAID'),
        sa.ForeignKeyConstraint(['loan_id'], ['loans.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_repayment_schedules_id'), 'repayment_schedules', ['id'], unique=False)

    # 16. Payments Table
    op.create_table(
        'payments',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('loan_id', sa.Integer(), nullable=False),
        sa.Column('amount', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('payment_date', sa.DateTime(timezone=True), nullable=False),
        sa.Column('payment_mode', sa.String(length=50), nullable=False),
        sa.Column('reference_no', sa.String(length=100), nullable=False),
        sa.Column('state', sa.String(length=50), nullable=False, default='PENDING'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.ForeignKeyConstraint(['loan_id'], ['loans.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_payments_id'), 'payments', ['id'], unique=False)
    op.create_index(op.f('ix_payments_reference_no'), 'payments', ['reference_no'], unique=True)

    # 17. Payment Allocations Table
    op.create_table(
        'payment_allocations',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('payment_id', sa.Integer(), nullable=False),
        sa.Column('repayment_schedule_id', sa.Integer(), nullable=False),
        sa.Column('allocated_principal', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('allocated_interest', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('allocated_charges', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('allocated_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.ForeignKeyConstraint(['payment_id'], ['payments.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['repayment_schedule_id'], ['repayment_schedules.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_payment_allocations_id'), 'payment_allocations', ['id'], unique=False)

    # 18. Charge Entries Table
    op.create_table(
        'charge_entries',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('loan_id', sa.Integer(), nullable=False),
        sa.Column('repayment_schedule_id', sa.Integer(), nullable=True),
        sa.Column('charge_master_id', sa.Integer(), nullable=False),
        sa.Column('amount', sa.Numeric(precision=15, scale=4), nullable=False),
        sa.Column('tax_amount', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('amount_paid', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('tax_paid', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('waived_amount', sa.Numeric(precision=15, scale=4), nullable=False, default=0.00),
        sa.Column('state', sa.String(length=50), nullable=False, default='UNPAID'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.ForeignKeyConstraint(['loan_id'], ['loans.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['repayment_schedule_id'], ['repayment_schedules.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['charge_master_id'], ['charge_master.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_charge_entries_id'), 'charge_entries', ['id'], unique=False)

    # 19. Maker Checker Entries Table
    op.create_table(
        'maker_checker_entries',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('loan_id', sa.Integer(), nullable=False),
        sa.Column('type', sa.String(length=50), nullable=False),
        sa.Column('payload', sa.JSON(), nullable=False),
        sa.Column('proposed_by', sa.String(length=100), nullable=False),
        sa.Column('approved_by', sa.String(length=100), nullable=True),
        sa.Column('state', sa.String(length=50), nullable=False, default='PENDING'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.ForeignKeyConstraint(['loan_id'], ['loans.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_maker_checker_entries_id'), 'maker_checker_entries', ['id'], unique=False)

def downgrade() -> None:
    op.drop_table('maker_checker_entries')
    op.drop_table('charge_entries')
    op.drop_table('payment_allocations')
    op.drop_table('payments')
    op.drop_table('repayment_schedules')
    op.drop_table('loans')
    op.drop_table('interest_configurations')
    op.drop_table('disbursement_configurations')
    op.drop_table('workflow_configurations')
    op.drop_table('npa_configurations')
    op.drop_table('foreclosure_configurations')
    op.drop_table('settlement_configurations')
    op.drop_table('collection_configurations')
    op.drop_table('allocation_configurations')
    op.drop_table('charge_configurations')
    op.drop_table('product_configurations')
    op.drop_table('charge_master')
    op.drop_table('products')
    op.drop_table('lenders')