415 lines
14 KiB
Python
415 lines
14 KiB
Python
from datetime import datetime
|
|
from html import unescape
|
|
from pathlib import Path
|
|
import re
|
|
import sys
|
|
|
|
import psycopg2
|
|
from psycopg2.extras import execute_values
|
|
|
|
|
|
ROOT_DIR = Path(__file__).resolve().parents[1]
|
|
CONN_DIR = ROOT_DIR / "conn"
|
|
if str(CONN_DIR) not in sys.path:
|
|
sys.path.insert(0, str(CONN_DIR))
|
|
|
|
import config
|
|
import mysql5
|
|
|
|
|
|
TAG_RE = re.compile(r"<[^>]+>")
|
|
SPACE_RE = re.compile(r"\s+")
|
|
CODE_RE = re.compile(r"\b[A-Z]{1,8}[-_/]?\d{2,}[A-Z0-9._/-]*\b|\b[A-Z0-9_]{3,}\.[A-Z0-9_.$-]+\b", re.IGNORECASE)
|
|
|
|
|
|
def get_pg_connection():
|
|
return psycopg2.connect(**config.POSTGRES_CONFIG)
|
|
|
|
|
|
def clean_text(value):
|
|
if value is None:
|
|
return ""
|
|
text = unescape(str(value))
|
|
text = TAG_RE.sub(" ", text)
|
|
text = text.replace("\x00", " ")
|
|
return SPACE_RE.sub(" ", text).strip()
|
|
|
|
|
|
def short_text(value, max_len=420):
|
|
text = clean_text(value)
|
|
if len(text) <= max_len:
|
|
return text
|
|
return text[:max_len].rsplit(" ", 1)[0] + "..."
|
|
|
|
|
|
def extract_codes(*values):
|
|
codes = set()
|
|
for value in values:
|
|
for match in CODE_RE.findall(clean_text(value).upper()):
|
|
codes.add(match.strip(".,;:()[]{}"))
|
|
return sorted(code for code in codes if code)
|
|
|
|
|
|
def build_index_document(row):
|
|
subject = clean_text(row.get("subject"))
|
|
problem = clean_text(row.get("problem"))
|
|
solution = clean_text(row.get("solution"))
|
|
metadata = " ".join(
|
|
clean_text(row.get(name))
|
|
for name in ("competence", "product", "area", "subarea", "client", "gravity", "status", "program", "program_type")
|
|
if row.get(name) is not None
|
|
)
|
|
codes = extract_codes(subject, problem, solution, metadata)
|
|
search_text = " ".join(part for part in (subject, problem, solution, metadata, " ".join(codes)) if part)
|
|
return {
|
|
"ticket_id": int(row["ticket_id"]),
|
|
"subject_text": subject,
|
|
"problem_text": problem,
|
|
"solution_text": solution,
|
|
"solution_preview": short_text(solution),
|
|
"metadata_text": metadata,
|
|
"search_text": search_text,
|
|
"codes": codes,
|
|
}
|
|
|
|
|
|
def ensure_ai_schema():
|
|
connection = get_pg_connection()
|
|
cursor = connection.cursor()
|
|
|
|
cursor.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS ticket_ai_index (
|
|
ticket_id BIGINT PRIMARY KEY,
|
|
subject_text TEXT NOT NULL DEFAULT '',
|
|
problem_text TEXT NOT NULL DEFAULT '',
|
|
solution_text TEXT NOT NULL DEFAULT '',
|
|
solution_preview TEXT NOT NULL DEFAULT '',
|
|
metadata_text TEXT NOT NULL DEFAULT '',
|
|
search_text TEXT NOT NULL DEFAULT '',
|
|
codes TEXT[] NOT NULL DEFAULT '{}',
|
|
competence_id INTEGER,
|
|
competence TEXT,
|
|
product_id INTEGER,
|
|
product TEXT,
|
|
area_id INTEGER,
|
|
area TEXT,
|
|
subarea_id INTEGER,
|
|
subarea TEXT,
|
|
client_id INTEGER,
|
|
client TEXT,
|
|
gravity_id INTEGER,
|
|
gravity TEXT,
|
|
status_id INTEGER,
|
|
status TEXT,
|
|
opened_at TIMESTAMP,
|
|
closed_at TIMESTAMP,
|
|
program TEXT,
|
|
program_type TEXT,
|
|
version_id INTEGER,
|
|
patch_id INTEGER,
|
|
classification_id INTEGER,
|
|
os_id INTEGER,
|
|
search_vector TSVECTOR,
|
|
source_updated_at TIMESTAMP,
|
|
indexed_at TIMESTAMP NOT NULL DEFAULT now()
|
|
)
|
|
"""
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE OR REPLACE FUNCTION ticket_ai_index_search_vector_update()
|
|
RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.search_vector :=
|
|
setweight(to_tsvector('simple', COALESCE(NEW.subject_text, '')), 'A') ||
|
|
setweight(to_tsvector('simple', COALESCE(NEW.problem_text, '')), 'A') ||
|
|
setweight(to_tsvector('simple', COALESCE(NEW.solution_text, '')), 'B') ||
|
|
setweight(to_tsvector('simple', COALESCE(NEW.metadata_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', array_to_string(COALESCE(NEW.codes, '{}'), ' ')), 'A');
|
|
RETURN NEW;
|
|
END
|
|
$$ LANGUAGE plpgsql
|
|
"""
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
DROP TRIGGER IF EXISTS ticket_ai_index_search_vector_trigger ON ticket_ai_index
|
|
"""
|
|
)
|
|
cursor.execute(
|
|
"""
|
|
CREATE TRIGGER ticket_ai_index_search_vector_trigger
|
|
BEFORE INSERT OR UPDATE ON ticket_ai_index
|
|
FOR EACH ROW EXECUTE FUNCTION ticket_ai_index_search_vector_update()
|
|
"""
|
|
)
|
|
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_search_vector_idx ON ticket_ai_index USING GIN (search_vector)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_problem_trgm_idx ON ticket_ai_index USING GIN (problem_text gin_trgm_ops)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_subject_trgm_idx ON ticket_ai_index USING GIN (subject_text gin_trgm_ops)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_codes_idx ON ticket_ai_index USING GIN (codes)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_product_idx ON ticket_ai_index (product_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_area_idx ON ticket_ai_index (area_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_subarea_idx ON ticket_ai_index (subarea_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_competence_idx ON ticket_ai_index (competence_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_index_opened_idx ON ticket_ai_index (opened_at)")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS ticket_ai_query_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
user_id BIGINT,
|
|
query_ticket_id BIGINT NOT NULL,
|
|
suggested_ticket_id BIGINT NOT NULL,
|
|
rank INTEGER NOT NULL,
|
|
total_score DOUBLE PRECISION NOT NULL,
|
|
lexical_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
trigram_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
code_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
metadata_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
feedback_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
confidence TEXT NOT NULL,
|
|
query_text TEXT NOT NULL,
|
|
reasons TEXT[] NOT NULL DEFAULT '{}'
|
|
)
|
|
"""
|
|
)
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_query_log_query_idx ON ticket_ai_query_log (query_ticket_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_query_log_suggestion_idx ON ticket_ai_query_log (suggested_ticket_id)")
|
|
cursor.execute("ALTER TABLE ticket_ai_query_log ADD COLUMN IF NOT EXISTS code_score DOUBLE PRECISION NOT NULL DEFAULT 0")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS ticket_ai_search_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
user_id BIGINT,
|
|
query_kind TEXT NOT NULL DEFAULT 'ticket',
|
|
query_ticket_id BIGINT,
|
|
query_text TEXT NOT NULL DEFAULT '',
|
|
filters JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
result_count INTEGER NOT NULL DEFAULT 0,
|
|
elapsed_ms INTEGER NOT NULL DEFAULT 0
|
|
)
|
|
"""
|
|
)
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_search_log_created_idx ON ticket_ai_search_log (created_at)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_search_log_result_idx ON ticket_ai_search_log (result_count)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_search_log_ticket_idx ON ticket_ai_search_log (query_ticket_id)")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS ticket_ai_feedback (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
user_id BIGINT,
|
|
query_ticket_id BIGINT NOT NULL,
|
|
suggested_ticket_id BIGINT NOT NULL,
|
|
feedback TEXT NOT NULL CHECK (feedback IN ('useful', 'not_useful', 'favorite', 'opened')),
|
|
note TEXT
|
|
)
|
|
"""
|
|
)
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_feedback_pair_idx ON ticket_ai_feedback (query_ticket_id, suggested_ticket_id)")
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS ticket_ai_feedback_suggestion_idx ON ticket_ai_feedback (suggested_ticket_id)")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE MATERIALIZED VIEW IF NOT EXISTS ticket_ai_feedback_summary AS
|
|
SELECT
|
|
suggested_ticket_id AS ticket_id,
|
|
SUM(CASE WHEN feedback IN ('useful', 'favorite', 'opened') THEN 1 ELSE 0 END) AS positive_count,
|
|
SUM(CASE WHEN feedback = 'not_useful' THEN 1 ELSE 0 END) AS negative_count
|
|
FROM ticket_ai_feedback
|
|
GROUP BY suggested_ticket_id
|
|
"""
|
|
)
|
|
cursor.execute("CREATE UNIQUE INDEX IF NOT EXISTS ticket_ai_feedback_summary_ticket_idx ON ticket_ai_feedback_summary (ticket_id)")
|
|
|
|
cursor.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS ticket_bot_user_settings (
|
|
user_id BIGINT PRIMARY KEY,
|
|
notifications_enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
quiet_start_hour INTEGER,
|
|
quiet_end_hour INTEGER,
|
|
digest_minutes INTEGER NOT NULL DEFAULT 0,
|
|
high_gravity_only BOOLEAN NOT NULL DEFAULT FALSE,
|
|
last_notification_at TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
CHECK (quiet_start_hour IS NULL OR (quiet_start_hour >= 0 AND quiet_start_hour <= 23)),
|
|
CHECK (quiet_end_hour IS NULL OR (quiet_end_hour >= 0 AND quiet_end_hour <= 23)),
|
|
CHECK (digest_minutes >= 0)
|
|
)
|
|
"""
|
|
)
|
|
|
|
connection.commit()
|
|
cursor.close()
|
|
connection.close()
|
|
|
|
|
|
def _row_values(row):
|
|
doc = build_index_document(row)
|
|
return (
|
|
doc["ticket_id"],
|
|
doc["subject_text"],
|
|
doc["problem_text"],
|
|
doc["solution_text"],
|
|
doc["solution_preview"],
|
|
doc["metadata_text"],
|
|
doc["search_text"],
|
|
doc["codes"],
|
|
row.get("competence_id"),
|
|
row.get("competence"),
|
|
row.get("product_id"),
|
|
row.get("product"),
|
|
row.get("area_id"),
|
|
row.get("area"),
|
|
row.get("subarea_id"),
|
|
row.get("subarea"),
|
|
row.get("client_id"),
|
|
row.get("client"),
|
|
row.get("gravity_id"),
|
|
row.get("gravity"),
|
|
row.get("status_id"),
|
|
row.get("status"),
|
|
row.get("opened_at"),
|
|
row.get("closed_at"),
|
|
row.get("program"),
|
|
row.get("program_type"),
|
|
row.get("version_id"),
|
|
row.get("patch_id"),
|
|
row.get("classification_id"),
|
|
row.get("os_id"),
|
|
datetime.utcnow(),
|
|
)
|
|
|
|
|
|
def upsert_ticket_batch(rows):
|
|
if not rows:
|
|
return 0
|
|
|
|
values = [_row_values(row) for row in rows]
|
|
connection = get_pg_connection()
|
|
cursor = connection.cursor()
|
|
execute_values(
|
|
cursor,
|
|
"""
|
|
INSERT INTO ticket_ai_index (
|
|
ticket_id,
|
|
subject_text,
|
|
problem_text,
|
|
solution_text,
|
|
solution_preview,
|
|
metadata_text,
|
|
search_text,
|
|
codes,
|
|
competence_id,
|
|
competence,
|
|
product_id,
|
|
product,
|
|
area_id,
|
|
area,
|
|
subarea_id,
|
|
subarea,
|
|
client_id,
|
|
client,
|
|
gravity_id,
|
|
gravity,
|
|
status_id,
|
|
status,
|
|
opened_at,
|
|
closed_at,
|
|
program,
|
|
program_type,
|
|
version_id,
|
|
patch_id,
|
|
classification_id,
|
|
os_id,
|
|
source_updated_at
|
|
) VALUES %s
|
|
ON CONFLICT (ticket_id) DO UPDATE SET
|
|
subject_text = EXCLUDED.subject_text,
|
|
problem_text = EXCLUDED.problem_text,
|
|
solution_text = EXCLUDED.solution_text,
|
|
solution_preview = EXCLUDED.solution_preview,
|
|
metadata_text = EXCLUDED.metadata_text,
|
|
search_text = EXCLUDED.search_text,
|
|
codes = EXCLUDED.codes,
|
|
competence_id = EXCLUDED.competence_id,
|
|
competence = EXCLUDED.competence,
|
|
product_id = EXCLUDED.product_id,
|
|
product = EXCLUDED.product,
|
|
area_id = EXCLUDED.area_id,
|
|
area = EXCLUDED.area,
|
|
subarea_id = EXCLUDED.subarea_id,
|
|
subarea = EXCLUDED.subarea,
|
|
client_id = EXCLUDED.client_id,
|
|
client = EXCLUDED.client,
|
|
gravity_id = EXCLUDED.gravity_id,
|
|
gravity = EXCLUDED.gravity,
|
|
status_id = EXCLUDED.status_id,
|
|
status = EXCLUDED.status,
|
|
opened_at = EXCLUDED.opened_at,
|
|
closed_at = EXCLUDED.closed_at,
|
|
program = EXCLUDED.program,
|
|
program_type = EXCLUDED.program_type,
|
|
version_id = EXCLUDED.version_id,
|
|
patch_id = EXCLUDED.patch_id,
|
|
classification_id = EXCLUDED.classification_id,
|
|
os_id = EXCLUDED.os_id,
|
|
source_updated_at = EXCLUDED.source_updated_at,
|
|
indexed_at = now()
|
|
""",
|
|
values,
|
|
page_size=500,
|
|
)
|
|
connection.commit()
|
|
cursor.close()
|
|
connection.close()
|
|
return len(rows)
|
|
|
|
|
|
def rebuild_index(batch_size=1000, max_rows=None):
|
|
ensure_ai_schema()
|
|
last_ticket = 0
|
|
indexed = 0
|
|
|
|
while True:
|
|
rows = mysql5.fetch_solved_ticket_batch(last_ticket=last_ticket, limit=batch_size)
|
|
if not rows:
|
|
break
|
|
upsert_ticket_batch(rows)
|
|
indexed += len(rows)
|
|
last_ticket = int(rows[-1]["ticket_id"])
|
|
print(f"Indexed {indexed} tickets, last ticket {last_ticket}")
|
|
if max_rows is not None and indexed >= max_rows:
|
|
break
|
|
|
|
refresh_feedback_summary()
|
|
return indexed
|
|
|
|
|
|
def refresh_feedback_summary():
|
|
connection = get_pg_connection()
|
|
cursor = connection.cursor()
|
|
cursor.execute("REFRESH MATERIALIZED VIEW ticket_ai_feedback_summary")
|
|
connection.commit()
|
|
cursor.close()
|
|
connection.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
batch_size = int(sys.argv[1]) if len(sys.argv) > 1 else 1000
|
|
max_rows = int(sys.argv[2]) if len(sys.argv) > 2 else None
|
|
print(f"Starting AI index rebuild with batch_size={batch_size}, max_rows={max_rows}")
|
|
total = rebuild_index(batch_size=batch_size, max_rows=max_rows)
|
|
print(f"Finished indexing {total} solved tickets")
|