3cx-telefonie-middleware/tools/import_zammad_history.py

#!/usr/bin/env python3

import json
import os
import re
import sqlite3
import time
from html import unescape
from pathlib import Path
from urllib.parse import urljoin

import requests


BASE = Path(".")
ENV = BASE / ".env"
DB = BASE / "data/zammad_history.sqlite3"

PER_PAGE = 100


def load_env():
    if not ENV.exists():
        raise SystemExit(".env fehlt")

    for line in ENV.read_text().splitlines():
        line = line.strip()

        if not line or line.startswith("#") or "=" not in line:
            continue

        key, value = line.split("=", 1)
        value = value.strip().strip('"').strip("'")
        os.environ.setdefault(key, value)


def clean_html(value):
    if not value:
        return ""

    value = re.sub(
        r"<(script|style).*?</\1>",
        " ",
        value,
        flags=re.I | re.S,
    )

    value = re.sub(r"<br\s*/?>", "\n", value, flags=re.I)
    value = re.sub(r"</p\s*>", "\n", value, flags=re.I)
    value = re.sub(r"</div\s*>", "\n", value, flags=re.I)
    value = re.sub(r"<[^>]+>", " ", value)

    value = unescape(value)

    value = re.sub(r"[ \t]+", " ", value)
    value = re.sub(r"\n\s*\n+", "\n\n", value)

    return value.strip()


def api(session, url, params=None):
    for attempt in range(5):
        response = session.get(
            url,
            params=params,
            timeout=60,
        )

        if response.status_code == 429:
            wait = int(
                response.headers.get(
                    "Retry-After",
                    "5",
                )
            )
            print(f"Rate limit – warte {wait}s")
            time.sleep(wait)
            continue

        response.raise_for_status()
        return response.json()

    raise RuntimeError(f"API nicht erreichbar: {url}")


def init_db(con):
    con.executescript("""
    CREATE TABLE IF NOT EXISTS tickets (
        id INTEGER PRIMARY KEY,
        number TEXT,
        title TEXT,
        group_name TEXT,
        state TEXT,
        state_id INTEGER,
        priority TEXT,
        priority_id INTEGER,
        customer_id INTEGER,
        customer_email TEXT,
        owner_id INTEGER,
        organization_id INTEGER,
        created_at TEXT,
        updated_at TEXT,
        close_at TEXT,
        tags_json TEXT,
        custom_fields_json TEXT,
        raw_json TEXT,
        imported_at TEXT DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS articles (
        id INTEGER PRIMARY KEY,
        ticket_id INTEGER NOT NULL,
        type TEXT,
        sender TEXT,
        sender_id INTEGER,
        from_address TEXT,
        to_address TEXT,
        subject TEXT,
        internal INTEGER,
        created_at TEXT,
        body_html TEXT,
        body_text TEXT,
        content_type TEXT,
        attachments_json TEXT,
        raw_json TEXT,
        imported_at TEXT DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_articles_ticket
        ON articles(ticket_id);

    CREATE INDEX IF NOT EXISTS idx_tickets_updated
        ON tickets(updated_at);
    """)


def main():
    load_env()

    base_url = os.environ["ZAMMAD_URL"].rstrip("/")
    user = os.environ["ZAMMAD_USER"]
    password = os.environ["ZAMMAD_PASSWORD"]

    session = requests.Session()
    session.auth = (user, password)
    session.headers.update({
        "Accept": "application/json",
        "Content-Type": "application/json",
        "User-Agent": "Schmid-Telefonie-Taxonomy-Importer/1.0",
    })

    DB.parent.mkdir(parents=True, exist_ok=True)

    con = sqlite3.connect(DB)
    init_db(con)

    print("=" * 72)
    print("ZAMMAD HISTORIENIMPORT")
    print("=" * 72)
    print(f"Server: {base_url}")
    print(f"Ziel:   {DB}")
    print()

    page = 1
    total_tickets = 0
    total_articles = 0

    while True:
        tickets = api(
            session,
            f"{base_url}/api/v1/tickets",
            {
                "page": page,
                "per_page": PER_PAGE,
                "order_by": "id",
                "order_direction": "asc",
            },
        )

        if not tickets:
            break

        print(
            f"Seite {page}: "
            f"{len(tickets)} Tickets"
        )

        for ticket in tickets:
            ticket_id = ticket["id"]

            con.execute("""
                INSERT OR REPLACE INTO tickets (
                    id,
                    number,
                    title,
                    group_name,
                    state,
                    state_id,
                    priority,
                    priority_id,
                    customer_id,
                    customer_email,
                    owner_id,
                    organization_id,
                    created_at,
                    updated_at,
                    close_at,
                    tags_json,
                    custom_fields_json,
                    raw_json
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                ticket_id,
                ticket.get("number"),
                ticket.get("title"),
                ticket.get("group"),
                ticket.get("state"),
                ticket.get("state_id"),
                ticket.get("priority"),
                ticket.get("priority_id"),
                ticket.get("customer_id"),
                ticket.get("customer"),
                ticket.get("owner_id"),
                ticket.get("organization_id"),
                ticket.get("created_at"),
                ticket.get("updated_at"),
                ticket.get("close_at"),
                json.dumps(
                    ticket.get("tags", []),
                    ensure_ascii=False,
                ),
                json.dumps(
                    ticket.get("preferences", {}),
                    ensure_ascii=False,
                ),
                json.dumps(
                    ticket,
                    ensure_ascii=False,
                ),
            ))

            articles = api(
                session,
                f"{base_url}/api/v1/ticket_articles/by_ticket/{ticket_id}",
            )

            for article in articles:
                body = article.get("body") or ""

                con.execute("""
                    INSERT OR REPLACE INTO articles (
                        id,
                        ticket_id,
                        type,
                        sender,
                        sender_id,
                        from_address,
                        to_address,
                        subject,
                        internal,
                        created_at,
                        body_html,
                        body_text,
                        content_type,
                        attachments_json,
                        raw_json
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    article["id"],
                    ticket_id,
                    article.get("type"),
                    article.get("sender"),
                    article.get("sender_id"),
                    article.get("from"),
                    article.get("to"),
                    article.get("subject"),
                    int(bool(article.get("internal"))),
                    article.get("created_at"),
                    body,
                    clean_html(body),
                    article.get("content_type"),
                    json.dumps(
                        article.get("attachments", []),
                        ensure_ascii=False,
                    ),
                    json.dumps(
                        article,
                        ensure_ascii=False,
                    ),
                ))

                total_articles += 1

            total_tickets += 1

            if total_tickets % 100 == 0:
                con.commit()
                print(
                    f"  {total_tickets} Tickets / "
                    f"{total_articles} Artikel"
                )

        con.commit()

        if len(tickets) < PER_PAGE:
            break

        page += 1

    con.commit()

    ticket_count = con.execute(
        "SELECT COUNT(*) FROM tickets"
    ).fetchone()[0]

    article_count = con.execute(
        "SELECT COUNT(*) FROM articles"
    ).fetchone()[0]

    con.close()

    print()
    print("=" * 72)
    print("IMPORT FERTIG")
    print("=" * 72)
    print(f"Tickets: {ticket_count}")
    print(f"Artikel: {article_count}")
    print(f"DB:     {DB}")


if __name__ == "__main__":
    main()