#!/usr/bin/env python3
"""
L20: Merge snwolley_alpha chat_history (original + incremental) into single CSV
"""
import gzip, csv, os, sys
from datetime import datetime

csv.field_size_limit(sys.maxsize)

BASE = "/root/ir-assessment/redteam/gitlab_npontutechnologies_com"
ORIG = f"{BASE}/exfil_all/pg_snwolley_alpha/chat_history.csv.gz"
INCR = f"{BASE}/exfil_all/pg_snwolley_alpha/chat_history_incremental_20260831.csv.gz"
OUT = f"{BASE}/L20_chat_history_full.csv"

print("=== MERGING CHAT HISTORY ===")
print(f"Original: {ORIG}")
print(f"Incremental: {INCR}")
print(f"Output: {OUT}")

# Read original
print("\n[*] Reading original...")
with gzip.open(ORIG, 'rt', errors='ignore') as f:
    reader = csv.reader(f)
    header = next(reader)
    orig_rows = list(reader)
print(f"  Original rows: {len(orig_rows)}")

# Read incremental
print("[*] Reading incremental...")
with gzip.open(INCR, 'rt', errors='ignore') as f:
    reader = csv.reader(f)
    incr_header = next(reader)
    incr_rows = list(reader)
print(f"  Incremental rows: {len(incr_rows)}")

# Check headers match
if header != incr_header:
    print(f"WARNING: headers differ!")
    print(f"  Original: {header[:5]}")
    print(f"  Incremental: {incr_header[:5]}")

# Merge
print("\n[*] Merging...")
all_rows = orig_rows + incr_rows
print(f"  Total rows: {len(all_rows)}")

# Sort by id
print("[*] Sorting by id...")
all_rows.sort(key=lambda x: int(x[0]) if x[0].isdigit() else 0)

# Write output
print(f"\n[*] Writing {OUT}...")
with open(OUT, 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(header)
    writer.writerows(all_rows)

# Stats
sz = os.path.getsize(OUT) / 1024 / 1024
print(f"\n[+] DONE: {OUT}")
print(f"  Size: {sz:.1f} MB")
print(f"  Rows: {len(all_rows)}")
print(f"  Columns: {len(header)}")
print(f"  Header: {header}")

# Date range
dates = []
for row in all_rows:
    if len(row) >= 8 and row[7]:
        try:
            dates.append(datetime.fromisoformat(row[7].replace('Z', '+00:00')))
        except:
            pass

if dates:
    print(f"\n  Date range: {min(dates)} to {max(dates)}")
    print(f"  Span: {(max(dates) - min(dates)).days} days")

print("\n=== READABLE FORMAT ===")
print(f"CSV file: {OUT}")
print(f"Open with: pandas, Excel, or any CSV reader")