"""bank_details table (INDL-XX Bank Details Settings)

Revision ID: 0078
Revises: 0077
Create Date: 2026-08-26

Tenant-scoped bank/EFT payment instructions used to populate the
contract_signed email and the contract PDF's Payment Details section.
One row per tenant, enforced via UNIQUE(tenant_id) — this is an
upsert-in-place resource (like Account/BrandingConfig), not a soft-deleted
list resource, so there is no deleted_at column.

account_number is never stored in plaintext: account_number_encrypted holds
a Fernet ciphertext (src/core/encryption.py), and account_number_last4 holds
only the last 4 digits in plaintext for cheap masked reads (GET responses,
PDF rendering) without a decrypt call. See src/apps/settings/models/bank_details.py
for the ORM model and src/apps/settings/services/bank_details_service.py for
the one call site (get_decrypted_for_tenant, used only by the contract_signed
email job) permitted to decrypt this column.

Chains off 0077 (proposal_templates_closing_line), the current develop head.
Idempotent (IF NOT EXISTS) so it is safe to re-run.
"""
from alembic import op
import sqlalchemy as sa

revision = "0078"
down_revision = "0077"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS bank_details (
            id                          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id                   UUID         NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

            account_holder_name         VARCHAR(255) NOT NULL,
            bank_name                   VARCHAR(255) NOT NULL,
            institution_number          VARCHAR(3)   NOT NULL,
            transit_number               VARCHAR(5)   NOT NULL,

            account_number_encrypted    TEXT         NOT NULL,
            account_number_last4        VARCHAR(4)   NOT NULL,

            account_type                VARCHAR(20)  NOT NULL,
            currency                    VARCHAR(3)   NOT NULL DEFAULT 'CAD',
            branch_address              TEXT         NOT NULL,

            created_at                  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
            updated_at                  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

            CONSTRAINT uq_bank_details_tenant UNIQUE (tenant_id)
        )
        """
    )
    # No standalone index statement: UNIQUE(tenant_id) above already creates
    # the unique B-tree index this table is exclusively queried by (see DBA
    # doc "Index Strategy" — this is a 1-row-per-tenant table, no other
    # access pattern exists).


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS bank_details")