tools/check_data.py

import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
os.chdir(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app
from models import EmployeeKpiValue, KpiDefinition, User, EmployeeMapping, KpiValue
from extensions import db
from sqlalchemy import text

app = create_app()
with app.app_context():
    # Check table structure
    result = db.session.execute(text('PRAGMA table_info(employee_kpi_values)'))
    print("=== employee_kpi_values columns ===")
    for row in result:
        print(f"  {row}")
    print()
    
    # Check the other kpi tables too
    for tname in ['kpi_definition', 'kpi_value']:
        try:
            result = db.session.execute(text(f'PRAGMA table_info({tname})'))
            print(f"=== {tname} columns ===")
            for row in result:
                print(f"  {row}")
        except:
            pass
    print()
    
    # Check current week data overview - use correct column names
    result = db.session.execute(text("""
        SELECT ekv.category, ekv.metric_name, COUNT(*) as cnt, 
               ROUND(AVG(CAST(ekv.fact_value AS REAL)),4) as avg_fact,
               ROUND(AVG(CAST(ekv.plan_value AS REAL)),4) as avg_plan
        FROM employee_kpi_values ekv
        WHERE ekv.week_start = '2026-07-06'
        GROUP BY ekv.category, ekv.metric_name
        ORDER BY ekv.category, ekv.metric_name
    """))
    for row in result:
        print(f"  {row.category:15s} | {row.metric_name:45s} | cnt={row.cnt} | fact={row.avg_fact} | plan={row.avg_plan}")
    print()
    
    print("=== Users with data this week ===")
    result = db.session.execute(text("""
        SELECT DISTINCT u.username, u.full_name, u.id
        FROM employee_kpi_values ekv
        JOIN employee_mapping em ON em.user_id = ekv.user_id
        JOIN user u ON u.id = em.user_id
        WHERE ekv.week_start = '2026-07-06'
        ORDER BY u.username
    """))
    for row in result:
        print(f"  id={row.id} username={row.username} name={row.full_name}")
    print()
    
    print("=== All KPI definitions ===")
    metrics = KpiDefinition.query.order_by(KpiDefinition.category, KpiDefinition.name).all()
    for m in metrics:
        print(f"  id={m.id:3d} | {m.category:15s} | {m.name:45s} | unit={m.unit or ''} | db_field={m.db_field or ''} | formula={m.formula or ''}")
    
    print("\n=== KpiValue (kpi_value table) ===")
    kvs = KpiValue.query.order_by(KpiValue.id).all()
    for kv in kvs[:20]:
        print(f"  id={kv.id} name={kv.name} category={kv.category} unit={kv.unit} order={kv.display_order}")
    if len(kvs) > 20:
        print(f"  ... and {len(kvs) - 20} more")
    
    print("\n=== User notes ===")
    print(f"EmployeeKpiValue count: {EmployeeKpiValue.query.count()}")
    print(f"KpiDefinition count: {KpiDefinition.query.count()}")
    print(f"KpiValue count: {KpiValue.query.count()}")
    print(f"User count: {User.query.count()}")