"""One-off cleanup of duplicated outbound messages.

Before the sync-time claim fix (see claim_local_outbound in sync_service.py),
every reply sent from the UI ended up stored twice: a local optimistic row
(provider_message_id NULL) inserted at send time, plus the real message
re-imported from the Addons API by the sync.

This script deletes the local copy when a synced twin exists in the same
thread within CLAIM_WINDOW_SECONDS and with an identical normalized body.
Dependent rows (attachments, topic segments, AI suggestions, translations)
are repointed to the twin first.

Usage:
    venv/bin/python scripts/cleanup_duplicate_outbound.py            # dry run
    venv/bin/python scripts/cleanup_duplicate_outbound.py --apply    # delete
"""

import os
import sys

sys.path.insert(0, os.path.join(os.path.dirname(__file__), ".."))

from dotenv import load_dotenv

load_dotenv()

import psycopg2

from supporthub.app.services.sync_service import (
    CLAIM_WINDOW_SECONDS,
    normalize_for_match,
)

APPLY = "--apply" in sys.argv


def cleanup_schema(cur, schema):
    cur.execute("SET search_path TO %s" % schema)
    cur.execute(
        """
        SELECT id, thread_id, created_at, body_text
        FROM addons_messages
        WHERE direction='outbound' AND provider_message_id IS NULL
        ORDER BY id
        """
    )
    local_rows = cur.fetchall()
    if not local_rows:
        print(f"{schema}: no local outbound rows")
        return

    deleted = kept = 0
    claimed_twins = set()  # a synced row can absorb only one local copy
    for loc_id, thread_id, created_at, body_text in local_rows:
        cur.execute(
            """
            SELECT id, created_at, body_text
            FROM addons_messages
            WHERE thread_id=%s AND direction='outbound'
              AND provider_message_id IS NOT NULL
              AND created_at BETWEEN %s - interval '%s seconds'
                                 AND %s + interval '%s seconds'
            """,
            (thread_id, created_at, CLAIM_WINDOW_SECONDS, created_at, CLAIM_WINDOW_SECONDS),
        )
        target = normalize_for_match(body_text)
        twins = [
            (tid, tcreated)
            for tid, tcreated, tbody in cur.fetchall()
            if tid not in claimed_twins and normalize_for_match(tbody) == target
        ]
        if not twins:
            kept += 1
            continue
        twin_id = min(twins, key=lambda t: abs((t[1] - created_at).total_seconds()))[0]
        claimed_twins.add(twin_id)

        if APPLY:
            # Repoint attachments unless the twin already has the same filename
            cur.execute(
                """
                DELETE FROM addons_attachments a
                WHERE a.message_id=%s
                  AND EXISTS (SELECT 1 FROM addons_attachments t
                              WHERE t.message_id=%s AND t.filename=a.filename)
                """,
                (loc_id, twin_id),
            )
            cur.execute(
                "UPDATE addons_attachments SET message_id=%s WHERE message_id=%s",
                (twin_id, loc_id),
            )
            cur.execute(
                "UPDATE ai_suggestions SET message_id=%s WHERE message_id=%s",
                (twin_id, loc_id),
            )
            cur.execute(
                "UPDATE message_translations SET message_id=%s WHERE message_id=%s",
                (twin_id, loc_id),
            )
            cur.execute(
                "UPDATE message_topic_segments SET start_message_id=%s WHERE start_message_id=%s",
                (twin_id, loc_id),
            )
            cur.execute(
                "UPDATE message_topic_segments SET end_message_id=%s WHERE end_message_id=%s",
                (twin_id, loc_id),
            )
            cur.execute("DELETE FROM addons_messages WHERE id=%s", (loc_id,))
        deleted += 1

    mode = "deleted" if APPLY else "would delete"
    print(f"{schema}: {mode} {deleted} duplicate local rows, kept {kept} (no twin found)")


def main():
    conn = psycopg2.connect(
        host=os.environ.get("DB_HOST", "localhost"),
        port=os.environ.get("DB_PORT", "5432"),
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
        dbname=os.environ["DB_NAME"],
    )
    cur = conn.cursor()
    cur.execute(
        """
        SELECT schema_name FROM information_schema.schemata
        WHERE schema_name LIKE 'tenant_%'
        """
    )
    schemas = [r[0] for r in cur.fetchall()]
    print(f"Mode: {'APPLY' if APPLY else 'DRY RUN'} — schemas: {schemas}")
    for schema in schemas:
        cleanup_schema(cur, schema)
    if APPLY:
        conn.commit()
        print("Committed.")
    else:
        conn.rollback()


if __name__ == "__main__":
    main()