import sqlite3
conn = sqlite3.connect('/opt/struktur/youtube-research/knowledge.db')
c = conn.cursor()
# total open AA-043 queue lines (not done)
c.execute("""
SELECT COUNT(*) 
FROM graphiti_import_queue 
WHERE reconciliation_record_id LIKE 'aa043:%' 
  AND graphiti_status != 'done'
""")
open_queue = c.fetchone()[0]
print(f'Open AA-043 queue lines (not done): {open_queue}')
# distinct source_identity among open (need to join source_versions)
c.execute("""
SELECT COUNT(DISTINCT sv.source_identity) 
FROM graphiti_import_queue g
JOIN source_versions sv ON g.source_version_id = sv.version_id
WHERE g.reconciliation_record_id LIKE 'aa043:%'
  AND g.graphiti_status != 'done'
""")
distinct_src_open = c.fetchone()[0]
print(f'Distinct source_identity (open): {distinct_src_open}')
# distinct source_version_id among open
c.execute("""
SELECT COUNT(DISTINCT g.source_version_id) 
FROM graphiti_import_queue g
WHERE g.reconciliation_record_id LIKE 'aa043:%'
  AND g.graphiti_status != 'done'
""")
distinct_ver_open = c.fetchone()[0]
print(f'Distinct source_version_id (open): {distinct_ver_open}')
# distinct obsidian_path among open
c.execute("""
SELECT COUNT(DISTINCT g.obsidian_path 
FROM graphiti_import_queue g
WHERE g.reconciliation_record_id LIKE 'aa043:%'
  AND g.graphiti_status != 'done'
""")
# Actually we need count distinct
c.execute("""
SELECT COUNT(DISTINCT g.obsidian_path) 
FROM graphiti_import_queue g
WHERE g.reconciliation_record_id LIKE 'aa043:%'
  AND g.graphiti_status != 'done'
""")
distinct_path_open = c.fetchone()[0]
print(f'Distinct obsidian_path (open): {distinct_path_open}')
# breakdown by status for all aa043 items
print('\nStatus breakdown for all AA-043 items:')
c.execute("""
SELECT graphiti_status, COUNT(*) AS cnt
FROM graphiti_import_queue
WHERE reconciliation_record_id LIKE 'aa043:%'
GROUP BY graphiti_status
ORDER BY cnt DESC
""")
for status, cnt in c.fetchall():
    print(f'  {status}: {cnt}')
# For done items, check remote_outcome to differentiate WRITE_CONFIRMED vs POLICY_BLOCK etc.
print('\nDone items breakdown by remote_outcome:')
c.execute("""
SELECT COALESCE(remote_outcome, '(null)') AS outcome, COUNT(*) AS cnt
FROM graphiti_import_queue
WHERE reconciliation_record_id LIKE 'aa043:%'
  AND graphiti_status = 'done'
GROUP BY outcome
ORDER BY cnt DESC
""")
for outcome, cnt in c.fetchall():
    print(f'  {outcome}: {cnt}')
# Promotion policy breakdown for open items (if column exists)
c.execute("PRAGMA table_info(graphiti_import_queue)")
cols = [row[1] for row in c.fetchall()]
if 'promotion_policy' in cols:
    print('\nPromotion policy breakdown (open items):')
    c.execute("""
    SELECT promotion_policy, COUNT(*) AS cnt
    FROM graphiti_import_queue
    WHERE reconciliation_record_id LIKE 'aa043:%'
      AND graphiti_status != 'done'
    GROUP BY promotion_policy
    ORDER BY cnt DESC
    """)
    for pol, cnt in c.fetchall():
        print(f'  {pol}: {cnt}')
    # distinct source_identity per promotion policy (open)
    print('\nDistinct source_identity per promotion policy (open):')
    c.execute("""
    SELECT promotion_policy, COUNT(DISTINCT sv.source_identity) AS cnt
    FROM graphiti_import_queue g
    JOIN source_versions sv ON g.source_version_id = sv.version_id
    WHERE g.reconciliation_record_id LIKE 'aa043:%'
      AND g.graphiti_status != 'done'
    GROUP BY promotion_policy
    ORDER BY cnt DESC
    """)
    for pol, cnt in c.fetchall():
        print(f'  {pol}: {cnt}')
else:
    print('\nPromotion policy column not present in graphiti_import_queue')
conn.close()