298 lines
9.0 KiB
Python
298 lines
9.0 KiB
Python
"""
|
||
Test script for Phase 3.1 - Chore Completion Logging
|
||
|
||
This script tests the new completion logging system without needing the frontend.
|
||
Run this after running the migration to verify everything works.
|
||
"""
|
||
import sqlite3
|
||
import sys
|
||
from pathlib import Path
|
||
from datetime import datetime, timedelta
|
||
|
||
# Add parent directory to path
|
||
sys.path.insert(0, str(Path(__file__).parent))
|
||
|
||
def test_database_structure():
|
||
"""Test that the database table was created correctly."""
|
||
print("\n" + "="*70)
|
||
print("TEST 1: Database Structure")
|
||
print("="*70)
|
||
|
||
db_path = Path(__file__).parent / "backend" / "data" / "family_hub.db"
|
||
|
||
if not db_path.exists():
|
||
print(f"❌ Database not found at: {db_path}")
|
||
return False
|
||
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
# Check table exists
|
||
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='chore_completion_logs'")
|
||
table = cursor.fetchone()
|
||
|
||
if not table:
|
||
print("❌ Table 'chore_completion_logs' not found!")
|
||
return False
|
||
|
||
print("✅ Table 'chore_completion_logs' exists")
|
||
|
||
# Check structure
|
||
cursor.execute("PRAGMA table_info(chore_completion_logs)")
|
||
columns = cursor.fetchall()
|
||
|
||
expected_columns = [
|
||
'id', 'chore_id', 'user_id', 'completed_at',
|
||
'notes', 'verified_by_user_id', 'created_at'
|
||
]
|
||
|
||
actual_columns = [col[1] for col in columns]
|
||
|
||
print(f"\n📋 Table Columns:")
|
||
for col in columns:
|
||
print(f" - {col[1]} ({col[2]})")
|
||
|
||
missing = set(expected_columns) - set(actual_columns)
|
||
if missing:
|
||
print(f"\n❌ Missing columns: {missing}")
|
||
return False
|
||
|
||
print("\n✅ All expected columns present")
|
||
|
||
# Check indexes
|
||
cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='chore_completion_logs'")
|
||
indexes = cursor.fetchall()
|
||
|
||
print(f"\n📊 Indexes ({len(indexes)}):")
|
||
for idx in indexes:
|
||
print(f" - {idx[0]}")
|
||
|
||
if len(indexes) >= 3:
|
||
print("✅ Expected indexes created")
|
||
else:
|
||
print(f"⚠️ Expected at least 3 indexes, found {len(indexes)}")
|
||
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"❌ Error: {e}")
|
||
return False
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def test_sample_data_insertion():
|
||
"""Test inserting sample completion logs."""
|
||
print("\n" + "="*70)
|
||
print("TEST 2: Sample Data Insertion")
|
||
print("="*70)
|
||
|
||
db_path = Path(__file__).parent / "backend" / "data" / "family_hub.db"
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
# Get first user and first chore
|
||
cursor.execute("SELECT id, username FROM users LIMIT 1")
|
||
user = cursor.fetchone()
|
||
|
||
cursor.execute("SELECT id, title FROM chores LIMIT 1")
|
||
chore = cursor.fetchone()
|
||
|
||
if not user or not chore:
|
||
print("⚠️ No users or chores found - create some first!")
|
||
return False
|
||
|
||
print(f"📝 Using User: {user[1]} (ID: {user[0]})")
|
||
print(f"📝 Using Chore: {chore[1]} (ID: {chore[0]})")
|
||
|
||
# Insert a test completion
|
||
now = datetime.utcnow()
|
||
cursor.execute("""
|
||
INSERT INTO chore_completion_logs
|
||
(chore_id, user_id, completed_at, notes, created_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
""", (chore[0], user[0], now, "Test completion from validation script", now))
|
||
|
||
log_id = cursor.lastrowid
|
||
conn.commit()
|
||
|
||
print(f"✅ Inserted test completion log (ID: {log_id})")
|
||
|
||
# Verify insertion
|
||
cursor.execute("SELECT * FROM chore_completion_logs WHERE id = ?", (log_id,))
|
||
log = cursor.fetchone()
|
||
|
||
if log:
|
||
print(f"✅ Verified: Log can be retrieved")
|
||
print(f" - ID: {log[0]}")
|
||
print(f" - Chore ID: {log[1]}")
|
||
print(f" - User ID: {log[2]}")
|
||
print(f" - Completed At: {log[3]}")
|
||
print(f" - Notes: {log[4]}")
|
||
else:
|
||
print("❌ Could not retrieve inserted log")
|
||
return False
|
||
|
||
# Clean up test data
|
||
cursor.execute("DELETE FROM chore_completion_logs WHERE id = ?", (log_id,))
|
||
conn.commit()
|
||
print(f"🧹 Cleaned up test data")
|
||
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"❌ Error: {e}")
|
||
conn.rollback()
|
||
return False
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def test_query_performance():
|
||
"""Test query performance with existing data."""
|
||
print("\n" + "="*70)
|
||
print("TEST 3: Query Performance")
|
||
print("="*70)
|
||
|
||
db_path = Path(__file__).parent / "backend" / "data" / "family_hub.db"
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
# Count total logs
|
||
cursor.execute("SELECT COUNT(*) FROM chore_completion_logs")
|
||
total_logs = cursor.fetchone()[0]
|
||
|
||
print(f"📊 Total completion logs: {total_logs}")
|
||
|
||
if total_logs == 0:
|
||
print("ℹ️ No completion logs yet - this is expected for new installation")
|
||
return True
|
||
|
||
# Test date range query
|
||
week_ago = datetime.utcnow() - timedelta(days=7)
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM chore_completion_logs
|
||
WHERE completed_at >= ?
|
||
""", (week_ago,))
|
||
|
||
week_logs = cursor.fetchone()[0]
|
||
print(f"📊 Completions in last 7 days: {week_logs}")
|
||
|
||
# Test user grouping
|
||
cursor.execute("""
|
||
SELECT user_id, COUNT(*) as count
|
||
FROM chore_completion_logs
|
||
GROUP BY user_id
|
||
ORDER BY count DESC
|
||
""")
|
||
|
||
user_stats = cursor.fetchall()
|
||
if user_stats:
|
||
print(f"\n📊 Completions by User:")
|
||
for user_id, count in user_stats:
|
||
cursor.execute("SELECT username FROM users WHERE id = ?", (user_id,))
|
||
username = cursor.fetchone()
|
||
if username:
|
||
print(f" - {username[0]}: {count} completions")
|
||
|
||
print("\n✅ Query performance looks good!")
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"❌ Error: {e}")
|
||
return False
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def test_foreign_key_constraints():
|
||
"""Test that foreign key constraints are working."""
|
||
print("\n" + "="*70)
|
||
print("TEST 4: Foreign Key Constraints")
|
||
print("="*70)
|
||
|
||
db_path = Path(__file__).parent / "backend" / "data" / "family_hub.db"
|
||
conn = sqlite3.connect(db_path)
|
||
# Enable foreign keys
|
||
conn.execute("PRAGMA foreign_keys = ON")
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
# Try to insert with invalid user_id
|
||
now = datetime.utcnow()
|
||
try:
|
||
cursor.execute("""
|
||
INSERT INTO chore_completion_logs
|
||
(chore_id, user_id, completed_at, created_at)
|
||
VALUES (999999, 999999, ?, ?)
|
||
""", (now, now))
|
||
conn.commit()
|
||
print("⚠️ Foreign key constraints may not be enforced")
|
||
# Clean up
|
||
cursor.execute("DELETE FROM chore_completion_logs WHERE user_id = 999999")
|
||
conn.commit()
|
||
except sqlite3.IntegrityError:
|
||
print("✅ Foreign key constraints are working!")
|
||
print(" (Correctly rejected invalid user_id)")
|
||
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"❌ Error: {e}")
|
||
return False
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def main():
|
||
"""Run all tests."""
|
||
print("\n" + "="*70)
|
||
print("PHASE 3.1 - CHORE COMPLETION LOGGING")
|
||
print("Validation Test Suite")
|
||
print("="*70)
|
||
|
||
tests = [
|
||
test_database_structure,
|
||
test_sample_data_insertion,
|
||
test_query_performance,
|
||
test_foreign_key_constraints
|
||
]
|
||
|
||
passed = 0
|
||
failed = 0
|
||
|
||
for test in tests:
|
||
try:
|
||
if test():
|
||
passed += 1
|
||
else:
|
||
failed += 1
|
||
except Exception as e:
|
||
print(f"❌ Test failed with exception: {e}")
|
||
failed += 1
|
||
|
||
print("\n" + "="*70)
|
||
print("TEST RESULTS")
|
||
print("="*70)
|
||
print(f"✅ Passed: {passed}")
|
||
print(f"❌ Failed: {failed}")
|
||
print(f"📊 Total: {passed + failed}")
|
||
|
||
if failed == 0:
|
||
print("\n🎉 All tests passed! Phase 3.1 backend is ready!")
|
||
print("\nNext steps:")
|
||
print("1. Restart your backend: stop-all.bat && start-backend.bat")
|
||
print("2. Test the API endpoints (see PHASE_3_1_COMPLETE.md)")
|
||
print("3. Start building frontend features!")
|
||
else:
|
||
print("\n⚠️ Some tests failed. Please review the output above.")
|
||
|
||
print("="*70 + "\n")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|