"""dedupe host_silence problems + unique active index

Revision ID: 026_host_silence_dedupe
Revises: 025_auto_rdp_flap_disconnect
Create Date: 2026-07-03

"""

from alembic import op

revision = "026_host_silence_dedupe"
down_revision = "025_auto_rdp_flap_disconnect"
branch_labels = None
depends_on = None


def _dedupe_host_silence_problems() -> None:
    op.execute(
        """
        WITH ranked AS (
            SELECT
                p.id,
                ROW_NUMBER() OVER (
                    PARTITION BY p.host_id
                    ORDER BY p.last_seen_at DESC, p.id DESC
                ) AS rn
            FROM problems p
            WHERE p.rule_id = 'rule:host_silence'
              AND p.status IN ('open', 'acknowledged')
        )
        UPDATE problems
        SET status = 'resolved',
            resolved_by = 'auto',
            updated_at = NOW()
        WHERE id IN (SELECT id FROM ranked WHERE rn > 1)
        """
    )
    op.execute(
        """
        WITH ranked AS (
            SELECT
                p.id,
                ROW_NUMBER() OVER (
                    PARTITION BY lower(h.hostname)
                    ORDER BY p.last_seen_at DESC, p.id DESC
                ) AS rn
            FROM problems p
            JOIN hosts h ON h.id = p.host_id
            WHERE p.rule_id = 'rule:host_silence'
              AND p.status IN ('open', 'acknowledged')
        )
        UPDATE problems
        SET status = 'resolved',
            resolved_by = 'auto',
            updated_at = NOW()
        WHERE id IN (SELECT id FROM ranked WHERE rn > 1)
        """
    )


def upgrade() -> None:
    _dedupe_host_silence_problems()
    op.execute(
        """
        CREATE UNIQUE INDEX IF NOT EXISTS uq_problems_host_silence_active
        ON problems (host_id)
        WHERE rule_id = 'rule:host_silence'
          AND status IN ('open', 'acknowledged')
        """
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS uq_problems_host_silence_active")