#!/usr/bin/env python3
"""
Анализ похожести структур БД по снятому инвентарю.
Вход: columns.tsv (db|tbl|col|typ|max_len), tables.tsv (db|tbl)
Выход: SIMILARITY_RESULTS.md, similarity_matrix.txt, cluster_groups/*.tsv, core_tables.csv
Метрики: Jaccard по множествам имён таблиц, cosine по векторам колонок,
         ядро таблиц (присутствуют во всех/большинстве БД), кластеризация по порогу.
"""
import math
import os
from collections import defaultdict
from pathlib import Path

BASE = Path("/root/ir-assessment/redteam/irelydata/schema_inventory")
COLS = BASE / "columns.tsv"
TABLES = BASE / "tables.tsv"
OUT_MD = BASE / "SIMILARITY_RESULTS.md"
OUT_MATRIX = BASE / "similarity_matrix.txt"
OUT_CLUSTERS = BASE / "cluster_groups"
OUT_CORE = BASE / "core_tables.csv"

# ---------- загрузка ----------
db_tables = defaultdict(set)          # db -> {table}
db_cols = defaultdict(int)            # db -> total column count
tbl_cols = {}                         # (db,tbl) -> {col: typ}
db_col_names = defaultdict(set)       # db -> set of (table.col) signatures? слишком тяжко; используем набор имён колонок

with COLS.open() as f:
    for line in f:
        parts = line.rstrip("\n").split("|")
        if len(parts) != 5:
            continue
        db, tbl, col, typ, _ = parts
        db_tables[db].add(tbl)
        db_cols[db] += 1
        db_col_names[db].add((tbl, col, typ))

dbs = sorted(db_tables)
n = len(dbs)
print(f"БД: {n}, уникальных имён таблиц: {len(set().union(*db_tables.values()))}")

# ---------- ядро ----------
tbl_presence = defaultdict(int)
for db in dbs:
    for t in db_tables[db]:
        tbl_presence[t] += 1
core_all = {t for t, c in tbl_presence.items() if c == n}
core_90 = {t for t, c in tbl_presence.items() if c >= 0.9 * n}
print(f"Ядро (в 100% БД): {len(core_all)} таблиц; ядро >=90%: {len(core_90)}")

with OUT_CORE.open("w") as f:
    f.write("table,present_in_dbs,total_dbs\n")
    for t, c in sorted(tbl_presence.items(), key=lambda x: (-x[1], x[0])):
        f.write(f"{t},{c},{n}\n")

# ---------- Jaccard + cosine ----------
def jaccard(a, b):
    inter = len(a & b)
    return inter / (len(a) + len(b) - inter) if (len(a) + len(b) - inter) else 1.0

def cosine(ca, cb):
    # по числу колонок как норма — грубо; лучше по пересечению сигнатур (tbl,col,typ)
    return None

sig_norm = {db: math.sqrt(len(db_col_names[db])) for db in dbs}

matrix = {}
with OUT_MATRIX.open("w") as f:
    f.write("db_a\tdb_b\tjaccard\tcommon_tables\tcos_cols\n")
    for i in range(n):
        for j in range(i + 1, n):
            a, b = dbs[i], dbs[j]
            jac = jaccard(db_tables[a], db_tables[b])
            inter_sig = len(db_col_names[a] & db_col_names[b])
            cos = inter_sig / (sig_norm[a] * sig_norm[b])
            matrix[(a, b)] = (jac, cos, len(db_tables[a] & db_tables[b]))
            f.write(f"{a}\t{b}\t{jac:.4f}\t{len(db_tables[a] & db_tables[b])}\t{cos:.4f}\n")

jacs = [v[0] for v in matrix.values()]
print(f"Jaccard: min={min(jacs):.3f} max={max(jacs):.3f} avg={sum(jacs)/len(jacs):.3f}")

# топ самых похожих пар
top_pairs = sorted(matrix.items(), key=lambda x: -x[1][0])[:15]
print("\nТОП-15 самых похожих пар:")
for (a, b), (jac, cos, inter) in top_pairs:
    print(f"  {jac:.3f}  {a} <-> {b}  (общих таблиц: {inter})")

# самые непохожие
bot_pairs = sorted(matrix.items(), key=lambda x: x[1][0])[:10]
print("\nТОП-10 самых НЕпохожих пар:")
for (a, b), (jac, cos, inter) in bot_pairs:
    print(f"  {jac:.3f}  {a} <-> {b}")

# ---------- кластеризация (связные компоненты по порогу Jaccard >= 0.40) ----------
THRESHOLD = 0.40
adj = defaultdict(set)
for (a, b), (jac, _, _) in matrix.items():
    if jac >= THRESHOLD:
        adj[a].add(b)
        adj[b].add(a)

seen = set()
clusters = []
for db in dbs:
    if db in seen:
        continue
    stack, comp = [db], []
    seen.add(db)
    while stack:
        cur = stack.pop()
        comp.append(cur)
        for nb in adj[cur]:
            if nb not in seen:
                seen.add(nb)
                stack.append(nb)
    clusters.append(sorted(comp))

clusters.sort(key=len, reverse=True)
OUT_CLUSTERS.mkdir(exist_ok=True)
print(f"\nКластеры (порог Jaccard >= {THRESHOLD}): {len(clusters)}")
for idx, comp in enumerate(clusters, 1):
    print(f"  CLUSTER_{idx}: {len(comp)} БД -> {', '.join(comp[:8])}{'...' if len(comp) > 8 else ''}")
    with (OUT_CLUSTERS / f"cluster_{idx}.tsv").open("w") as f:
        f.write("db\ttables\tcols\n")
        for db in comp:
            f.write(f"{db}\t{len(db_tables[db])}\t{db_cols[db]}\n")
        # ядро кластера
        common = set.intersection(*(db_tables[db] for db in comp))
        f.write(f"\n# общее ядро кластера: {len(common)} таблиц\n")

# ---------- статистика по БД ----------
stats = sorted(((db, len(db_tables[db]), db_cols[db]) for db in dbs), key=lambda x: -x[1])
print("\nТОП-10 БД по числу таблиц:")
for db, t, c in stats[:10]:
    print(f"  {db:<30} {t:>5} таблиц, {c:>7} колонок")
print("\nМИН-5 БД по числу таблиц:")
for db, t, c in stats[-5:]:
    print(f"  {db:<30} {t:>5} таблиц, {c:>7} колонок")

median_t = sorted(x[1] for x in stats)[n // 2]
print(f"\nМедиана таблиц на БД: {median_t}")

# ---------- отчёт ----------
rep = []
rep.append(f"""# Похожесть структур БД — РЕЗУЛЬТАТЫ

Дата анализа: снято live с 50.21.183.111, обработано скриптом analyze_schema_similarity.py

## Ключевые цифры
- БД в анализе: **{n}** (все ONLINE, database_id > 4)
- Уникальных имён таблиц: **{len(tbl_presence)}**
- Медиана таблиц на БД: **{median_t}**
- Максимум: **{stats[0][0]}** — {stats[0][1]} таблиц, {stats[0][2]} колонок
- Минимум: **{stats[-1][0]}** — {stats[-1][1]} таблиц, {stats[-1][2]} колонок

## Ядро i21
- Таблиц, присутствующих в 100% БД: **{len(core_all)}**
- Таблиц, присутствующих в >=90% БД: **{len(core_90)}**

## Jaccard по парам ({len(matrix)} пар)
- min = {min(jacs):.3f}, max = {max(jacs):.3f}, avg = {sum(jacs)/len(jacs):.3f}

## Кластеры (связные компоненты, порог Jaccard >= {THRESHOLD})
""")
for idx, comp in enumerate(clusters, 1):
    common = set.intersection(*(db_tables[db] for db in comp))
    rep.append(f"\n### CLUSTER_{idx} — {len(comp)} БД (ядро {len(common)} таблиц)\n")
    rep.extend(f"- {db} ({len(db_tables[db])} tbl)\n" for db in comp)
rep.append("\n## Топ-15 самых похожих пар\n")
rep.extend(f"- {jac:.3f}  {a} <-> {b} (общих {inter})\n" for (a, b), (jac, cos, inter) in top_pairs)
rep.append("\n## Топ-10 самых непохожих пар\n")
rep.extend(f"- {jac:.3f}  {a} <-> {b}\n" for (a, b), (jac, cos, inter) in bot_pairs)
rep.append("""
## Вывод
Все БД — одна ERP iRely i21; структурное ядро едино. Различия = включённые
клиентские модули. Для анализа данных достаточно схемы-представителя
+ карты модульных отклонений.

## Артефакты
- columns.tsv, tables.tsv, rowcounts.tsv
- core_tables.csv (таблица -> в скольких БД)
- similarity_matrix.txt (пары: jaccard, common_tables, cosine)
- cluster_groups/cluster_N.tsv (состав + ядро каждого кластера)
""")
OUT_MD.write_text("".join(rep))
print(f"\nОтчёт: {OUT_MD}")