================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 2 SOLUTION
Chapter 4: Building db_utils.py
Challenge: Insert a Test Subtopic
================================================================================

PROBLEM:
Create a script that:
1. Connects using db_utils
2. Uses get_or_create_subtopic() to create a test course
3. Shows the returned subtopic_id
4. Calls it again to verify it returns the same ID (no duplicate)

SOLUTION:
================================================================================

import sys
from pathlib import Path

# Add db_utils.py to Python path
sys.path.insert(0, str(Path(__file__).parent))

from db_utils import DatabaseConnection, get_or_create_subtopic

def test_subtopic_creation():
    """Test creating and retrieving subtopics."""

    print("=" * 60)
    print("TESTING SUBTOPIC CREATION & RETRIEVAL")
    print("=" * 60 + "\n")

    # Connect to database
    db = DatabaseConnection()
    if not db.connect():
        print("❌ Failed to connect to database")
        return False

    try:
        # First: Create a test subtopic
        print("📌 Step 1: Creating test subtopic...\n")

        subject_id = 3  # Programming
        subtopic_name = "Test Course - Python Utilities"
        description = "A test course for learning how db_utils.py works"

        subtopic_id_1 = get_or_create_subtopic(
            db,
            subject_id=subject_id,
            subtopic_name=subtopic_name,
            description=description
        )

        print(f"✅ Result: Created/retrieved subtopic with ID: {subtopic_id_1}\n")

        if subtopic_id_1 is None:
            print("❌ Failed to create subtopic")
            return False

        # Second: Call again to verify it returns the SAME ID
        print("📌 Step 2: Calling get_or_create_subtopic() again...\n")
        print("   (Should return the SAME ID, not create a duplicate)\n")

        subtopic_id_2 = get_or_create_subtopic(
            db,
            subject_id=subject_id,
            subtopic_name=subtopic_name,
            description=description
        )

        print(f"✅ Result: Retrieved subtopic with ID: {subtopic_id_2}\n")

        # Third: Verify they're the same
        print("📌 Step 3: Verification\n")

        if subtopic_id_1 == subtopic_id_2:
            print(f"✅ SUCCESS: Both calls returned the SAME ID ({subtopic_id_1})")
            print("   This means no duplicate was created!")
        else:
            print(f"❌ FAILURE: IDs don't match!")
            print(f"   First call:  {subtopic_id_1}")
            print(f"   Second call: {subtopic_id_2}")
            return False

        # Fourth: Verify the subtopic exists in the database
        print("\n📌 Step 4: Querying database to verify...\n")

        cursor = db.connection.cursor()
        query = "SELECT id, name, subject_id, description FROM subtopics WHERE id = %s"
        cursor.execute(query, (subtopic_id_1,))
        result = cursor.fetchone()
        cursor.close()

        if result:
            id, name, subj_id, desc = result
            print(f"✅ Found in database:")
            print(f"   ID: {id}")
            print(f"   Name: {name}")
            print(f"   Subject ID: {subj_id}")
            print(f"   Description: {desc}")
        else:
            print(f"❌ Could not find subtopic {subtopic_id_1} in database!")
            return False

        print("\n" + "=" * 60)
        print("✅ TEST PASSED!")
        print("=" * 60)
        print("\nWhat this test verified:")
        print("  - get_or_create_subtopic() works correctly")
        print("  - Duplicate prevention works (same ID returned)")
        print("  - Database transaction commits properly")
        print("  - We can query back what we inserted")

        return True

    except Exception as e:
        print(f"❌ Error: {e}")
        return False

    finally:
        db.disconnect()


if __name__ == "__main__":
    success = test_subtopic_creation()
    sys.exit(0 if success else 1)

================================================================================

HOW IT WORKS:
================================================================================

1. FIRST CALL - CREATE OR RETRIEVE
   subtopic_id_1 = get_or_create_subtopic(
       db,
       subject_id=3,
       subtopic_name="Test Course - Python Utilities",
       description="A test course for learning how db_utils.py works"
   )

   - Calls get_or_create_subtopic() from db_utils
   - Function checks if subtopic with that name exists
   - If not found: INSERTs new row, returns new ID
   - If found: returns existing ID
   - Returns None if error occurs

2. SECOND CALL - VERIFY NO DUPLICATE
   subtopic_id_2 = get_or_create_subtopic(...)

   - Calls the same function with same parameters
   - Should find the subtopic this time (we just created it)
   - Should return the SAME ID as first call
   - Proves duplicate prevention works

3. VERIFY IDS MATCH
   if subtopic_id_1 == subtopic_id_2:
       print("SUCCESS")

   - Simple equality check
   - If they match: no duplicate was created
   - If they don't match: something went wrong

4. QUERY DATABASE
   cursor.execute("SELECT ... FROM subtopics WHERE id = %s", (subtopic_id_1,))
   result = cursor.fetchone()

   - Confirms the subtopic actually exists
   - Verifies the data we inserted is correct
   - Proves the database transaction committed

================================================================================

UNDERSTANDING get_or_create_subtopic():
================================================================================

Here's what happens inside get_or_create_subtopic():

Step 1: Check if exists
SELECT id FROM subtopics WHERE name = %s AND subject_id = %s
- Looks for subtopic with exact name and subject_id
- If found: returns the ID immediately

Step 2: If not found, create it
INSERT INTO subtopics (subject_id, name, description) VALUES (...)
- Inserts new row with provided data
- Gets the new ID with cursor.lastrowid

Step 3: Commit
db.connection.commit()
- Commits the database transaction
- Makes the change permanent

Step 4: Error handling
except Error: rollback() and return None
- If any error occurs, rollback undoes changes
- Returns None to signal failure

================================================================================

TESTING THE SOLUTION:
================================================================================

python test_subtopic.py

Output:
============================================================
TESTING SUBTOPIC CREATION & RETRIEVAL
============================================================

📌 Step 1: Creating test subtopic...

✅ Created subtopic 'Test Course - Python Utilities' with ID 15
✅ Result: Created/retrieved subtopic with ID: 15

📌 Step 2: Calling get_or_create_subtopic() again...

   (Should return the SAME ID, not create a duplicate)

✅ Result: Retrieved subtopic with ID: 15

📌 Step 3: Verification

✅ SUCCESS: Both calls returned the SAME ID (15)
   This means no duplicate was created!

📌 Step 4: Querying database to verify...

✅ Found in database:
   ID: 15
   Name: Test Course - Python Utilities
   Subject ID: 3
   Description: A test course for learning how db_utils.py works

============================================================
✅ TEST PASSED!
============================================================

What this test verified:
  - get_or_create_subtopic() works correctly
  - Duplicate prevention works (same ID returned)
  - Database transaction commits properly
  - We can query back what we inserted

================================================================================

WHY THIS TEST IS IMPORTANT:
================================================================================

This test verifies one of the most critical patterns:

IDEMPOTENCE - Running the same operation multiple times
produces the same result without creating duplicates.

Why this matters:
1. Scripts might run multiple times
2. Network issues might cause retries
3. You don't want duplicate courses in the database
4. The function should be safe to call repeatedly

Testing idempotence:
- Call function once → ID 15
- Call function again → ID 15 (same!)
- Not ID 16, 17, 18, ...

This guarantees the function is safe to use in production.

================================================================================

ADVANCED: TESTING WITH DIFFERENT VALUES:
================================================================================

You could extend this test to verify:

1. Different subjects create different subtopics
   subtopic_id_1 = get_or_create_subtopic(db, subject_id=3, name="Course A")
   subtopic_id_2 = get_or_create_subtopic(db, subject_id=4, name="Course A")
   # Should have different IDs (different subjects)

2. Case sensitivity
   subtopic_id_1 = get_or_create_subtopic(db, subject_id=3, name="Python")
   subtopic_id_2 = get_or_create_subtopic(db, subject_id=3, name="python")
   # Might be different depending on database collation

3. Special characters in names
   subtopic_id = get_or_create_subtopic(
       db,
       subject_id=3,
       name="Python & Advanced Concepts (v2.0)"
   )
   # Test that special characters are handled correctly

================================================================================

REAL-WORLD USAGE:
================================================================================

This is exactly how rebuild_typescript_course.py uses db_utils:

# From rebuild_typescript_course.py
subtopic_id = get_or_create_subtopic(
    db,
    subject_id=3,
    subtopic_name="TypeScript Fundamentals",
    description="Learn TypeScript from basics to generics"
)

# Then insert chapters:
for chapter_file in chapter_files:
    page_id = insert_chapter_with_content(
        db,
        subtopic_id=subtopic_id,
        chapter_title=title,
        html_content=content,
        sort_order=sort_order
    )

The rebuild script calls get_or_create_subtopic() ONCE,
then insert_chapter_with_content() MANY TIMES.

================================================================================

KEY TAKEAWAYS:
================================================================================

✅ How to use db_utils helper functions
✅ How idempotence (no duplicates) is tested
✅ How to verify database changes by querying them back
✅ How to handle None return values (error indication)
✅ How transactions work (commit on success, rollback on error)
✅ How to structure verification tests

The pattern for database testing:
1. Perform operation → get a result (ID, count, etc.)
2. Perform operation again → verify same result
3. Query database → verify data is really there
4. Check error handling → verify rollback on errors

This is a safe, repeatable testing pattern!

================================================================================