"""Column-level dispositions for every legacy table (05 step 2.10).

"The reconciliation report enumerates every column with its disposition
— 'not mentioned anywhere' is a failure, not a default."

This module IS that enumeration, written from
01-current-system/schema.md and verified at runtime against the landed
``legacy`` schema: a legacy column missing from this map fails the run;
a mapped column missing from the schema is reported as stale (warning —
the sync path may land a column subset for auth.users only).

Disposition vocabulary:
  carried:<target>       value lands verbatim at <target>
  transformed:<how>      value is re-keyed / derived into the new model
  dropped:<reason>       deliberately not migrated (allowlisted in 05/06)
"""

# legacy.users (auth.users) is deliberately NOT column-enumerated: it is
# identity-map-only (id -> email for re-keying *_by columns) and its
# credential columns are NULLed by the snapshot scrub before we ever see
# them. Only id/email/created_at are consulted.
TABLES_EXEMPT_FROM_COLUMN_CHECK = {"users"}

COLUMN_DISPOSITIONS: dict[str, dict[str, str]] = {
    "organizations": {
        "id": "transformed:org->tenant map (tenant per org)",
        "name": "transformed:tenant party name (merged with same-named operator party)",
        "slug": "carried:parties.slug (tenant party)",
        "default_hourly_rate_usd": "dropped:dead since rate-model v4 (schema.md drop list)",
        "created_at": "dropped:row metadata; tenants get their own",
    },
    "profiles": {
        "id": "transformed:legacy-uid->users map (re-keys every *_by column)",
        "email": "transformed:identity map email->idauth_username (05 step 2.11)",
        "full_name": "dropped:users carry BW-derived identity; party name comes from team_members",
        "is_super_admin": "carried:users.is_super_admin",
        "must_change_password": "dropped:id-auth owns credentials (05 step 2.11)",
        "created_at": "dropped:row metadata; users get their own",
        "updated_at": "dropped:row metadata",
    },
    "organization_members": {
        "organization_id": "transformed:scopes the seeded engagement to the org tenant",
        "user_id": "transformed:owner -> official_partnership;"
        " owner/manager -> can_manage_imports (02 §4)",
        "role": "transformed:engagement-derived grants (02 §4 parity mapping)",
        "created_at": "dropped:row metadata",
    },
    "teams": {
        "id": "dropped:teams dissolve — team scoping is engagement scoping (02 §7)",
        "org_id": "dropped:teams dissolve",
        "owner_user_id": "dropped:teams dissolve",
        "name": "dropped:recorded in the legacy schema only (05 step 2.10)",
        "base_rate_usd": "dropped:dead for money math since proportion elimination (ADR #034)",
        "created_at": "dropped:row metadata",
        "updated_at": "dropped:row metadata",
    },
    "team_members": {
        "id": "transformed:member->party/engagement maps (worker_party_id, via_engagement_id)",
        "org_id": "transformed:tenant scope of the worker engagement",
        "team_id": "dropped:teams dissolve (02 §7)",
        "email": "transformed:person-party dedupe key lower(email); carried to parties.email",
        "display_name": "transformed:parties.name (earliest row wins per deduped party)",
        "rate_proportion": "dropped:proportion machinery retired (ADR #034; T-MIG-009)",
        "hourly_rate_usd": "dropped:dead since rate-model v2 (schema.md drop list)",
        "cost_rate_usd": "transformed:cost-kind compensation term, CAD"
        " (05 step 2.3; NULL = no term)",
        "billout_rate_usd": "transformed:billout-kind compensation term, CAD"
        " (05 step 2.3; NULL = no term)",
        "consolidate_as": "carried:engagements.consolidate_as (import-mapping config, 02 §7)",
        "is_active": "transformed:engagements.ended_on (inactive -> ended at migration date)",
        "notes": "carried:engagements.notes",
        "created_at": "transformed:earliest-row tiebreak for the deduped party name",
        "updated_at": "dropped:row metadata",
    },
    "clockify_imports": {
        "id": "carried:import_batches.id",
        "org_id": "transformed:import_batches.tenant_id",
        "imported_by": "transformed:import_batches.imported_by re-keyed to users"
        " (load-bearing, 05 step 2.5)",
        "imported_at": "carried:import_batches.imported_at",
        "filename": "carried:import_batches.filename",
        "name": "carried:import_batches.name",
        "row_count": "carried:import_batches.row_count",
        "source": "carried:import_batches.source (toggl/backfill admitted verbatim)",
        "notes": "carried:import_batches.notes",
    },
    "time_entries": {
        "id": "carried:time_entries.id (id stability is a harness contract)",
        "org_id": "transformed:time_entries.tenant_id",
        "import_id": "carried:time_entries.import_id",
        "source_user_name": "carried:time_entries.source_user_name",
        "source_user_email": "carried:time_entries.source_user_email",
        "operator": "carried:time_entries.raw_operator (immutable import payload, 02 §3)",
        "client": "carried:time_entries.raw_client",
        "project": "carried:time_entries.raw_project",
        "description": "carried:time_entries.description",
        "billable": "carried:time_entries.billable",
        "start_at": "carried:time_entries.start_at (naive wall-clock, 07 #17)",
        "end_at": "carried:time_entries.end_at",
        "duration_seconds": "carried:time_entries.duration_seconds",
        "source_rate_usd": "carried:time_entries.source_rate (4dp import provenance)",
        "source_amount_usd": "carried:time_entries.source_amount (4dp import provenance)",
        "team_member_id": "transformed:worker_party_id + via_engagement_id (member map)",
        "converted_user": "dropped:Toggl-era concept (ADR #034; 05 allowlist)",
        "converted_duration_seconds": "dropped:Toggl-era concept (ADR #034; 05 allowlist)",
        "billout_cost_usd": "carried:time_entries.cost_amount VERBATIM"
        " + cost_currency CAD (T-MIG-013)",
        "billout_amount_usd": "carried:time_entries.billout_amount VERBATIM"
        " + billout_currency CAD (T-MIG-013)",
        "project_id": "carried:time_entries.project_id (project ids carry unchanged)",
        "transferred_at": "dropped:after the judgment-#22 backfill check (straggler rule)",
        "transferred_by": "dropped:audit already carried to invoice_applied_by"
        " by the 2026-05-25 backfill",
        "transfer_batch_id": "dropped:FK-less Toggl-era uuid (rebuild delta #9)",
        "invoice_id": "transformed:the lock becomes invoice_line_id via a time-kind invoice line",
        "invoice_applied_at": "carried:invoice_lines.attached_at (audit VALUES carry, T-MIG-014)",
        "invoice_applied_by": "carried:invoice_lines.attached_by re-keyed to users (T-MIG-014)",
        "created_at": "carried:time_entries.created_at",
    },
    "comments": {
        "id": "carried:comments.id",
        "org_id": "transformed:comments.tenant_id",
        "author_id": "transformed:comments.author_id re-keyed to users (05 step 2.9)",
        "body": "carried:comments.body",
        "resolved_at": "carried:comments.resolved_at",
        "resolved_by": "transformed:comments.resolved_by re-keyed to users",
        "created_at": "carried:comments.created_at",
    },
    "operators": {
        "id": "transformed:operator->party map",
        "org_id": "transformed:tenant scope",
        "name": "carried:parties.name (org party)",
        "notes": "carried:parties.notes",
        "created_at": "dropped:row metadata; parties get their own",
        "updated_at": "dropped:row metadata",
    },
    "clients": {
        "id": "transformed:client->party map",
        "org_id": "transformed:tenant scope",
        "operator_id": "transformed:direct_client engagement"
        " (client party <-> operator party, 05 step 2.2)",
        "name": "carried:parties.name (org party; merges with same-named operator party)",
        "notes": "carried:parties.notes",
        "created_at": "dropped:row metadata",
        "updated_at": "dropped:row metadata",
    },
    "projects": {
        "id": "carried:projects.id (project ids carry unchanged — harness grouping key)",
        "org_id": "transformed:projects.tenant_id",
        "client_id": "transformed:projects.client_party_id (+ operator_party_id via the chain)",
        "name": "carried:projects.name",
        "income_usd": "carried:projects.income + income_currency CAD",
        "notes": "carried:projects.notes",
        "billout_adjustment_pct": "carried:projects.billout_adjustment_pct (ADR #040)",
        "billout_adjustment_amount": "carried:projects.billout_adjustment_amount (ADR #040)",
        "created_at": "carried:projects.created_at",
        "updated_at": "dropped:row metadata",
    },
    "pending_imports": {
        "id": "dropped:staging data — anything unresolved at cutover is re-uploaded (05 step 2.5)",
        "org_id": "dropped:staging data",
        "uploaded_by": "dropped:staging data",
        "uploaded_at": "dropped:staging data",
        "expires_at": "dropped:staging data",
        "filename": "dropped:staging data",
        "batch_name": "dropped:staging data",
        "notes": "dropped:staging data",
        "source": "dropped:staging data",
        "date_format": "dropped:staging data",
        "time_format": "dropped:staging data",
        "parsed_entries": "dropped:staging data",
    },
    "project_rate_overrides": {
        "id": "carried:rate_overrides.id",
        "org_id": "transformed:rate_overrides.tenant_id",
        "project_id": "carried:rate_overrides.project_id",
        "team_member_id": "transformed:rate_overrides.worker_party_id"
        " (member->party map; NULL stays project-wide)",
        "override_rate_usd": "carried:rate_overrides.override_rate"
        " + currency CAD (per-source-hour semantics)",
        "override_pct": "carried:rate_overrides.override_pct"
        " (signed % on the worker's fallback rate)",
        "notes": "carried:rate_overrides.notes (05 step 2.10)",
        "created_at": "carried:rate_overrides.created_at",
        "updated_at": "dropped:row metadata",
    },
    "invoices": {
        "id": "carried:invoices.id",
        "org_id": "transformed:invoices.tenant_id",
        "name": "carried:invoices.name (unique per tenant)",
        "status": "carried:invoices.status (open/sent/paid verbatim)",
        "operator_id": "transformed:to_party seed input"
        " (display-scope chip, 05 step 2.7 pinned algorithm)",
        "client_id": "transformed:to_party seed input (display-scope chip)",
        "manual_total_usd": "carried:invoices.manual_total"
        " (values verbatim; suffix dies with the currency column)",
        "notes": "carried:invoices.notes",
        "created_by": "transformed:invoices.created_by re-keyed to users",
        "created_at": "carried:invoices.created_at",
        "invoice_date": "carried:invoices.invoice_date",
        "sent_at": "carried:invoices.sent_at",
        "paid_at": "carried:invoices.paid_at",
    },
    "cc_expense_batches": {
        "id": "carried:cc_expense_batches.id",
        "org_id": "transformed:cc_expense_batches.tenant_id",
        "name": "carried:cc_expense_batches.name",
        "created_by": "transformed:cc_expense_batches.created_by re-keyed to users",
        "created_at": "carried:cc_expense_batches.created_at",
    },
    "cc_expense_lines": {
        "id": "carried:cc_expense_lines.id",
        "batch_id": "carried:cc_expense_lines.batch_id",
        "line_index": "carried:cc_expense_lines.line_index"
        " (now unique per batch — rebuild delta #8)",
        "raw_line": "carried:cc_expense_lines.raw_line",
        "expense_date": "carried:cc_expense_lines.expense_date",
        "description": "carried:cc_expense_lines.description",
        "amount_usd": "carried:cc_expense_lines.amount (CAD batch currency)",
        "balance_usd": "carried:cc_expense_lines.balance",
        "project_id": "carried:cc_expense_lines.project_id",
        "assignment_description": "carried:cc_expense_lines.assignment_description",
        "category": "carried:cc_expense_lines.category",
        "auto_assigned": "carried:cc_expense_lines.auto_assigned",
        "created_at": "carried:cc_expense_lines.created_at",
    },
    "cc_highlight_keywords": {
        "id": "carried:cc_highlight_keywords.id",
        "org_id": "transformed:cc_highlight_keywords.tenant_id",
        "keyword": "carried:cc_highlight_keywords.keyword",
        "created_at": "carried:cc_highlight_keywords.created_at",
    },
    "cc_auto_rules": {
        "id": "carried:cc_auto_rules.id",
        "org_id": "transformed:cc_auto_rules.tenant_id",
        "keyword": "carried:cc_auto_rules.keyword",
        "project_id": "carried:cc_auto_rules.project_id",
        "assignment_description": "carried:cc_auto_rules.assignment_description",
        "category": "carried:cc_auto_rules.category",
        "created_at": "carried:cc_auto_rules.created_at",
    },
}


def verify_columns(
    legacy_columns: dict[str, list[str]],
) -> tuple[dict[str, list[str]], dict[str, list[str]], list[str]]:
    """Compare the landed legacy schema against this enumeration.

    Returns (unmapped, stale, unknown_tables): ``unmapped`` columns exist
    in the legacy schema but have no disposition (FAILURE); ``stale``
    dispositions name columns the schema doesn't have (warning);
    ``unknown_tables`` exist in the legacy schema but aren't enumerated
    at all (FAILURE — "not mentioned anywhere is a failure").
    """
    unmapped: dict[str, list[str]] = {}
    stale: dict[str, list[str]] = {}
    unknown_tables: list[str] = []
    for table, columns in legacy_columns.items():
        if table in TABLES_EXEMPT_FROM_COLUMN_CHECK:
            continue
        mapped = COLUMN_DISPOSITIONS.get(table)
        if mapped is None:
            unknown_tables.append(table)
            continue
        missing = [c for c in columns if c not in mapped]
        if missing:
            unmapped[table] = missing
        gone = [c for c in mapped if c not in columns]
        if gone:
            stale[table] = gone
    return unmapped, stale, unknown_tables