"""Add department+date indexes for department reports.

Revision ID: 028_dept_report_idx
Revises: 027_txn_report_idx
Create Date: 2026-08-14
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

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

INDEXES = (
    (
        "department_issues",
        "ix_dept_issue_co_dept_date",
        ["company_id", "department_id", "issue_date"],
    ),
    ("purchase_orders", "ix_po_co_date", ["company_id", "order_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)