"""Bedrock per-profile (UsedBy tag) cost breakdown — isolated fact table.

Adds an additive, blast-radius-isolated store for the AWS Bedrock
"profile" dimension (the ``UsedBy`` cost-allocation tag — e.g.
``investibook-dev`` / ``aiops`` — that subdivides one IAM-principal
owner's Bedrock spend). It is DELIBERATELY separate from
``cmdb_cost_facts`` so it cannot perturb the totals/by-model/dashboard
queries that sum that delicate, heavily-tuned table:

  * cmdb_bedrock_profile_facts — one row per
    (date × integration × scope × owner × profile × service)

…and seeds the per-integration opt-in setting so the cost-facts cron
knows which secondary tag to break down by:

  * cost_facts.aws_profile_tag.aws-sg = "UsedBy"

No foreign keys — ``integration_id`` is a plain String(36) to dodge
TiDB's CHAR/VARCHAR + collation FK strictness (same call the finance
spend-ledger + AI-spending period rollup made). Idempotent: the CREATE
is guarded on table-absence and the seed INSERT on a NOT EXISTS check,
so a re-run converges.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "298"
down_revision = "297"
branch_labels = None
depends_on = None


def _table_exists(bind, table: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()

    if not _table_exists(bind, "cmdb_bedrock_profile_facts"):
        op.create_table(
            "cmdb_bedrock_profile_facts",
            sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
            sa.Column("fact_key", sa.String(length=64), nullable=False),
            sa.Column("date", sa.Date(), nullable=False),
            sa.Column("integration_id", sa.String(length=36), nullable=False),
            # AWS 12-digit linked-account id the row's CE query was pinned
            # to — the natural per-(scope, date) eviction key for the
            # idempotent delete-then-upsert ingest.
            sa.Column("scope_external_id", sa.String(length=64), nullable=True),
            # iamPrincipal/Owner tag value (owner email) — the same axis
            # cmdb_cost_facts attributes Bedrock spend on, so the per-AK
            # detail endpoint can filter to one key's owner.
            sa.Column("owner_email", sa.String(length=256), nullable=False),
            # The UsedBy tag value (the "profile"); "(unallocated)" when
            # the principal had no UsedBy tag on that line item.
            sa.Column("profile", sa.String(length=256), nullable=False),
            # Bedrock model/edition (CE SERVICE dimension), e.g.
            # "Claude Sonnet 4.6 (Amazon Bedrock Edition)".
            sa.Column("service", sa.String(length=128), nullable=False),
            sa.Column("amount_usd", sa.Numeric(18, 6), nullable=False),
            sa.Column("amount_native", sa.Numeric(18, 6), nullable=True),
            sa.Column(
                "currency", sa.String(length=8), nullable=False,
                server_default="USD",
            ),
            sa.Column("usage_quantity", sa.Numeric(30, 6), nullable=True),
            sa.Column("usage_unit", sa.String(length=64), nullable=True),
            sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False,
            ),
            sa.Column(
                "updated_at", sa.DateTime(timezone=True),
                server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint(
                "fact_key", name="uq_bedrock_profile_facts_fact_key",
            ),
        )
        # Pins the per-AK detail query
        # (integration_id == X AND owner_email == Y AND date BETWEEN ...).
        op.create_index(
            "ix_bpf_int_owner_date", "cmdb_bedrock_profile_facts",
            ["integration_id", "owner_email", "date"],
        )
        # Pins the ingest pre-window DELETE
        # (integration_id == X AND scope_external_id == S AND date == D).
        op.create_index(
            "ix_bpf_int_scope_date", "cmdb_bedrock_profile_facts",
            ["integration_id", "scope_external_id", "date"],
        )

    # ── Seed the per-integration profile-tag opt-in (idempotent) ──
    # Prod's "AWS SG (Singapore)" integration (slug ``aws-sg``, account
    # 841162690310) activated the ``UsedBy`` cost-allocation tag in the
    # payer Billing Console. On a fresh/local DB this row is harmless —
    # the cost-facts cron only acts on it for an integration whose slug
    # matches and whose primary aws_principal_tag is also set.
    if _table_exists(bind, "platform_settings"):
        bind.execute(
            sa.text(
                "INSERT INTO platform_settings "
                "(`key`, value, description, created_at, updated_at) "
                "SELECT :key, :value, :desc, NOW(), NOW() FROM DUAL "
                "WHERE NOT EXISTS (SELECT 1 FROM platform_settings "
                "WHERE `key` = :key)"
            ),
            {
                "key": "cost_facts.aws_profile_tag.aws-sg",
                "value": '"UsedBy"',
                "desc": (
                    "AWS Bedrock per-profile breakdown: the secondary "
                    "iamPrincipal cost-allocation tag that subdivides one "
                    "owner's Bedrock spend (UsedBy => investibook-dev / "
                    "aiops / ...). Drives cmdb_bedrock_profile_facts."
                ),
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "platform_settings"):
        bind.execute(
            sa.text(
                "DELETE FROM platform_settings "
                "WHERE `key` = 'cost_facts.aws_profile_tag.aws-sg'"
            )
        )
    if _table_exists(bind, "cmdb_bedrock_profile_facts"):
        op.drop_table("cmdb_bedrock_profile_facts")