"""create whatsapp_campaign_metrics table

Revision ID: a7c4d2e9b1f3
Revises: m1s2y3n4c5e6
Create Date: 2026-05-03 13:00:00.000000

Adds a time-series metrics table for the WhatsApp campaigns analytics
dashboard. Each row captures one hourly bucket per campaign with running
counters (sent/delivered/read/replied/failed/opted_out).

The table is upserted on every recipient state transition and is also
backfilled lazily on first analytics read from the existing
``whatsapp_campaign_recipients`` rows, so customers can see charts even
for campaigns that pre-date this migration.

All CREATE statements are idempotent via ``IF NOT EXISTS``.
"""
from __future__ import annotations

from alembic import op

# revision identifiers, used by Alembic.
revision = "a7c4d2e9b1f3"
down_revision = "m1s2y3n4c5e6"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    dialect = bind.dialect.name
    pk = (
        "SERIAL PRIMARY KEY"
        if dialect == "postgresql"
        else "INTEGER PRIMARY KEY AUTOINCREMENT"
    )
    ts_default = (
        "TIMESTAMPTZ NOT NULL DEFAULT NOW()"
        if dialect == "postgresql"
        else "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"
    )
    ts_col = "TIMESTAMPTZ NOT NULL" if dialect == "postgresql" else "TIMESTAMP NOT NULL"

    op.execute(
        f"""
        CREATE TABLE IF NOT EXISTS whatsapp_campaign_metrics (
            id {pk},
            campaign_id INTEGER NOT NULL,
            subscription_id INTEGER NOT NULL,
            user_id VARCHAR(255) NOT NULL,
            bucket_ts {ts_col},
            sent INTEGER NOT NULL DEFAULT 0,
            delivered INTEGER NOT NULL DEFAULT 0,
            read_count INTEGER NOT NULL DEFAULT 0,
            replied INTEGER NOT NULL DEFAULT 0,
            failed INTEGER NOT NULL DEFAULT 0,
            opted_out INTEGER NOT NULL DEFAULT 0,
            created_at {ts_default},
            updated_at {ts_default}
        )
        """
    )
    op.execute(
        "CREATE UNIQUE INDEX IF NOT EXISTS ux_wa_metrics_campaign_bucket "
        "ON whatsapp_campaign_metrics (campaign_id, bucket_ts)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_wa_metrics_subscription "
        "ON whatsapp_campaign_metrics (subscription_id)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_wa_metrics_bucket "
        "ON whatsapp_campaign_metrics (bucket_ts)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_wa_metrics_bucket")
    op.execute("DROP INDEX IF EXISTS ix_wa_metrics_subscription")
    op.execute("DROP INDEX IF EXISTS ux_wa_metrics_campaign_bucket")
    op.execute("DROP TABLE IF EXISTS whatsapp_campaign_metrics")