"""add uuid column to meter table

Adds a stable ``uuid`` (UUID v4 string) column to the ``meter`` table so that
each meter epoch has a durable identity anchor suitable for use as an HA
Discovery ``unique_id``.

**Migration strategy (SQLite-safe)**:

SQLite does not support adding a NOT NULL + UNIQUE column to a non-empty table
in a single ``ALTER TABLE ADD COLUMN`` statement (adding a NOT NULL column
without a default value is rejected if the table already has rows).  The
safe approach used here is:

  1. Add ``uuid`` as a **nullable** column (SQLite allows this).
  2. **Back-fill** every existing ``meter`` row with a distinct ``str(uuid4())``
     value.  Each row gets its *own* random UUID — not a shared value — so the
     subsequent UNIQUE constraint is satisfied.
  3. Use ``batch_alter_table`` (which re-creates the table under the hood in
     SQLite) to alter the column to ``NOT NULL`` and add a UNIQUE constraint.

**Idempotency**: only rows where ``uuid IS NULL`` are back-filled; rows that
already have a uuid (e.g. from a repeated upgrade after a partial failure) are
left untouched.

**Audit**: after back-fill, the count of rows with ``uuid IS NULL`` must be
exactly zero; if not, the migration raises ``RuntimeError`` and rolls back.

**Data safety**: this migration is additive only — no existing rows are deleted
or overwritten; it only adds a new column and fills it in.

Revision ID: 20260625_14_meter_uuid
Revises: 20260625_13_meter_table
Create Date: 2026-06-25 00:00:00.000000
"""

import uuid as _uuid
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "20260625_14_meter_uuid"
down_revision: Union[str, None] = "20260625_13_meter_table"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


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

    # ------------------------------------------------------------------ #
    # 1. Add uuid as a nullable column.                                    #
    # ------------------------------------------------------------------ #
    with op.batch_alter_table("meter", schema=None) as batch_op:
        batch_op.add_column(
            sa.Column("uuid", sa.String(length=36), nullable=True)
        )

    # ------------------------------------------------------------------ #
    # 2. Back-fill: assign a distinct UUID to every row that has           #
    #    uuid IS NULL.  Each row gets its own random value so that the     #
    #    subsequent UNIQUE constraint is satisfied.                        #
    # ------------------------------------------------------------------ #
    rows = conn.execute(sa.text("SELECT id FROM meter WHERE uuid IS NULL")).fetchall()
    for (meter_id,) in rows:
        new_uuid = str(_uuid.uuid4())
        conn.execute(
            sa.text("UPDATE meter SET uuid = :uuid WHERE id = :mid"),
            {"uuid": new_uuid, "mid": meter_id},
        )

    # ------------------------------------------------------------------ #
    # 3. Audit: verify no rows remain with uuid IS NULL.                   #
    # ------------------------------------------------------------------ #
    null_count_row = conn.execute(
        sa.text("SELECT COUNT(*) FROM meter WHERE uuid IS NULL")
    ).fetchone()
    null_count: int = null_count_row[0] if null_count_row else 0

    if null_count != 0:
        raise RuntimeError(
            f"meter.uuid back-fill audit failed: {null_count} meter row(s) still have "
            "uuid IS NULL after back-fill.  Migration aborted to protect data integrity."
        )

    # ------------------------------------------------------------------ #
    # 4. Alter column to NOT NULL + UNIQUE (requires batch on SQLite).     #
    #    batch_alter_table re-creates the table, so the UNIQUE constraint  #
    #    and NOT NULL are applied atomically.                              #
    # ------------------------------------------------------------------ #
    with op.batch_alter_table("meter", schema=None) as batch_op:
        batch_op.alter_column(
            "uuid",
            existing_type=sa.String(length=36),
            nullable=False,
        )
        batch_op.create_unique_constraint("uq_meter_uuid", ["uuid"])


def downgrade() -> None:
    # Drop the UNIQUE constraint and the uuid column (batch on SQLite).
    with op.batch_alter_table("meter", schema=None) as batch_op:
        batch_op.drop_constraint("uq_meter_uuid", type_="unique")
        batch_op.drop_column("uuid")