"""Add linked_account_id and owner_breakdown to cmdb_cloud_billing_daily.

Revision ID: 161
Revises: 160
Create Date: 2026-05-13

Two bugs fixed by this migration:

(1) TOTAL MISMATCH — cmdb_cloud_billing_daily only stored costs for
    *registered* scopes.  AWS linked accounts that exist in the org but
    haven't been added to Helix as cmdb_cloud_scopes were silently dropped
    client-side in the driver, causing the billing overview total to
    understate reality.  Fix: the sync now does one reconciliation pass
    per integration (scope=None) and stores unregistered-account rows
    with scope_id NULL and linked_account_id = the actual 12-digit AWS
    account ID.  linked_account_id is also backfilled for existing
    registered-scope rows so the column is immediately informative.

(2) OWNER ATTRIBUTION GAP — the iamPrincipal/Owner tag breakdown visible
    in the AWS Cost Explorer console had no counterpart in Helix.
    Fix: the billing daily sync now performs a third CE pass (grouped by
    iamPrincipal/Owner) per registered scope and stores the result in
    owner_breakdown JSON, which the billing overview endpoint aggregates
    and returns.

Schema changes:
  * linked_account_id VARCHAR(36) NOT NULL DEFAULT '' — cloud provider's
    account identifier.  Registered scopes → scope.external_id (backfilled).
    Unregistered accounts → raw account ID.  Non-per-account rows → ''.
  * owner_breakdown JSON NULL — {owner_tag_value: cost_usd_float}.
    Populated for AWS scopes with CE principal-tag access; NULL otherwise.
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy import text


revision = "161"
down_revision = "160"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 1. Add linked_account_id — NOT NULL DEFAULT '' so ON DUPLICATE KEY
    #    UPDATE works correctly (no NULL-uniqueness ambiguity in TiDB/MySQL).
    op.add_column(
        "cmdb_cloud_billing_daily",
        sa.Column(
            "linked_account_id",
            sa.String(36),
            nullable=False,
            server_default="",
        ),
    )

    # 2. Backfill linked_account_id for existing registered-scope rows by
    #    joining through cmdb_cloud_scopes.  Unregistered / NULL-scope rows
    #    (none exist today) stay as ''.
    op.execute(text("""
        UPDATE cmdb_cloud_billing_daily b
        JOIN cmdb_cloud_scopes s ON b.scope_id = s.id
        SET b.linked_account_id = s.external_id
        WHERE b.scope_id IS NOT NULL
          AND b.linked_account_id = ''
    """))

    # 3. owner_breakdown — nullable JSON; NULL = not yet populated.
    op.add_column(
        "cmdb_cloud_billing_daily",
        sa.Column("owner_breakdown", sa.JSON, nullable=True),
    )


def downgrade() -> None:
    op.drop_column("cmdb_cloud_billing_daily", "owner_breakdown")
    op.drop_column("cmdb_cloud_billing_daily", "linked_account_id")