"""Add date indexes for transaction reports.

Revision ID: 027_txn_report_idx
Revises: 026_stock_report_idx
Create Date: 2026-08-14
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "027_txn_report_idx"
down_revision: Union[str, None] = "026_stock_report_idx"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

INDEXES = (
    ("sales_invoices", "ix_sales_inv_co_date", ["company_id", "invoice_date"]),
    ("vendor_bills", "ix_vendor_bills_co_date", ["company_id", "bill_date"]),
    ("sales_payments", "ix_sales_pay_co_date", ["company_id", "payment_date"]),
    ("purchase_payments", "ix_purch_pay_co_date", ["company_id", "payment_date"]),
    ("stock_transfers", "ix_stock_xfer_co_date", ["company_id", "transfer_date"]),
    ("item_transactions", "ix_item_txn_co_date", ["company_id", "txn_date"]),
)


def upgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    for table, name, cols in INDEXES:
        existing = {idx["name"] for idx in inspector.get_indexes(table)}
        if name not in existing:
            op.create_index(name, table, cols, unique=False)


def downgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    for table, name, _cols in INDEXES:
        existing = {idx["name"] for idx in inspector.get_indexes(table)}
        if name in existing:
            op.drop_index(name, table_name=table)