================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 3 SOLUTION
Chapter 4: Building db_utils.py
Challenge: Complete Course Integration
================================================================================

PROBLEM:
Create a script that:
1. Creates a test subtopic
2. Inserts 3 test chapters with HTML content
3. Verifies chapters are in database by querying them back
4. Reports success with all IDs

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, insert_chapter_with_content

def create_test_chapters():
    """Create a complete test course with chapters."""

    print("=" * 60)
    print("COMPLETE COURSE INTEGRATION TEST")
    print("=" * 60 + "\n")

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

    created_ids = {
        'subtopic_id': None,
        'chapter_ids': []
    }

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

        subtopic_id = get_or_create_subtopic(
            db,
            subject_id=3,  # Programming
            subtopic_name="Test Course - Complete Integration",
            description="Testing complete course creation workflow"
        )

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

        created_ids['subtopic_id'] = subtopic_id
        print(f"✅ Subtopic created with ID: {subtopic_id}\n")

        # Step 2: Create test chapters
        print("📌 Step 2: Inserting test chapters...\n")

        test_chapters = [
            {
                'title': 'Test Chapter 1: Introduction',
                'content': '''
                    <h2>Chapter 1: Introduction</h2>
                    <p>This is a test chapter for verifying db_utils.py functionality.</p>
                    <p>It contains basic HTML content to test database storage.</p>
                ''',
                'sort_order': 10
            },
            {
                'title': 'Test Chapter 2: Main Content',
                'content': '''
                    <h2>Chapter 2: Main Content</h2>
                    <h3>Section 2.1</h3>
                    <p>This section covers the main content.</p>
                    <ul>
                        <li>Point 1</li>
                        <li>Point 2</li>
                        <li>Point 3</li>
                    </ul>
                ''',
                'sort_order': 20
            },
            {
                'title': 'Test Chapter 3: Conclusion',
                'content': '''
                    <h2>Chapter 3: Conclusion</h2>
                    <p>This is the final chapter.</p>
                    <p>It demonstrates that multiple chapters can be inserted successfully.</p>
                    <code>print("Database integration works!")</code>
                ''',
                'sort_order': 30
            }
        ]

        # Insert each chapter
        for chapter in test_chapters:
            print(f"   Inserting: {chapter['title']}...")

            page_id = insert_chapter_with_content(
                db,
                subtopic_id=subtopic_id,
                chapter_title=chapter['title'],
                html_content=chapter['content'],
                sort_order=chapter['sort_order']
            )

            if page_id is None:
                print(f"   ❌ Failed to insert chapter: {chapter['title']}")
                return False

            created_ids['chapter_ids'].append(page_id)
            print(f"   ✅ Chapter created with page_id: {page_id}\n")

        # Step 3: Verify all chapters exist in database
        print("📌 Step 3: Verifying chapters in database...\n")

        cursor = db.connection.cursor()

        query = """
            SELECT p.id, p.title, p.slug, p.sort_order, LENGTH(pc.body) as content_length
            FROM pages p
            LEFT JOIN page_content pc ON p.id = pc.page_id
            WHERE p.subtopic_id = %s
            ORDER BY p.sort_order
        """

        cursor.execute(query, (subtopic_id,))
        chapters_in_db = cursor.fetchall()
        cursor.close()

        if not chapters_in_db:
            print("❌ No chapters found in database!")
            return False

        print(f"✅ Found {len(chapters_in_db)} chapters in database:\n")

        for page_id, title, slug, sort_order, content_length in chapters_in_db:
            print(f"   ├─ ID: {page_id}")
            print(f"   ├─ Title: {title}")
            print(f"   ├─ Slug: {slug}")
            print(f"   ├─ Sort Order: {sort_order}")
            print(f"   ├─ Content Size: {content_length} bytes")
            print()

        # Step 4: Verify correct number of chapters
        print("📌 Step 4: Verification Summary...\n")

        expected_count = len(test_chapters)
        actual_count = len(chapters_in_db)

        print(f"   Expected chapters: {expected_count}")
        print(f"   Actual chapters: {actual_count}")

        if expected_count == actual_count:
            print(f"   ✅ All chapters created successfully!\n")
        else:
            print(f"   ❌ Mismatch! Expected {expected_count}, found {actual_count}\n")
            return False

        # Final report
        print("=" * 60)
        print("✅ INTEGRATION TEST PASSED!")
        print("=" * 60 + "\n")

        print("📊 FINAL REPORT:")
        print(f"   Subtopic ID: {created_ids['subtopic_id']}")
        print(f"   Chapter IDs: {', '.join(map(str, created_ids['chapter_ids']))}")
        print(f"\n   Total entities created: {1 + len(created_ids['chapter_ids'])}")
        print(f"   (1 subtopic + {len(created_ids['chapter_ids'])} chapters)")

        print("\n✅ The following workflow was verified:")
        print("   1. Create subtopic (course)")
        print("   2. Insert multiple chapters with content")
        print("   3. Query chapters from database")
        print("   4. Verify data integrity and count")

        return True

    except Exception as e:
        print(f"❌ Unexpected error: {e}")
        import traceback
        traceback.print_exc()
        return False

    finally:
        db.disconnect()


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

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

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

1. CREATE SUBTOPIC
   subtopic_id = get_or_create_subtopic(...)
   - Creates or retrieves a test course
   - Stores ID for use with chapters

2. PREPARE TEST DATA
   test_chapters = [
       {
           'title': '...',
           'content': '...',
           'sort_order': 10
       },
       ...
   ]
   - List of dictionaries with chapter data
   - Each has title, HTML content, and sort order
   - Sort order determines chapter sequence (10, 20, 30)

3. INSERT CHAPTERS
   for chapter in test_chapters:
       page_id = insert_chapter_with_content(...)

   - Loop through each test chapter
   - Call insert_chapter_with_content() for each
   - Stores page_id for later verification
   - Immediately checks for errors (None return)

4. VERIFY IN DATABASE
   cursor.execute("""
       SELECT p.id, p.title, p.slug, ...
       FROM pages p
       LEFT JOIN page_content pc ON p.id = pc.page_id
       WHERE p.subtopic_id = %s
   """)

   - Queries pages and page_content tables
   - Joins to get content size
   - Filters to only chapters in our subtopic
   - Verifies all data was actually stored

5. DISPLAY RESULTS
   - Shows each chapter's details
   - Compares expected vs actual count
   - Reports success/failure

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

UNDERSTANDING THE JOIN QUERY:
================================================================================

SELECT p.id, p.title, p.slug, p.sort_order, LENGTH(pc.body) as content_length
FROM pages p
LEFT JOIN page_content pc ON p.id = pc.page_id
WHERE p.subtopic_id = %s
ORDER BY p.sort_order

Breaking it down:

- FROM pages p: Start with pages table, alias it as 'p'

- LEFT JOIN page_content pc: Join with page_content
  - LEFT JOIN means: keep all pages, even if no content
  - ON p.id = pc.page_id: Match by page ID

- LENGTH(pc.body): Show content size in bytes
  - Useful for verification (should not be empty)

- WHERE p.subtopic_id = %s: Filter to our subtopic
  - %s is a parameter (prevents SQL injection)

- ORDER BY p.sort_order: Sort by chapter order
  - 10, 20, 30 → Chapter 1, 2, 3

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

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

python test_integration.py

Output:
============================================================
COMPLETE COURSE INTEGRATION TEST
============================================================

📌 Step 1: Creating test subtopic...

✅ Created subtopic 'Test Course - Complete Integration' with ID 25
✅ Subtopic created with ID: 25

📌 Step 2: Inserting test chapters...

   Inserting: Test Chapter 1: Introduction...
   ✅ Chapter created with page_id: 101

   Inserting: Test Chapter 2: Main Content...
   ✅ Chapter created with page_id: 102

   Inserting: Test Chapter 3: Conclusion...
   ✅ Chapter created with page_id: 103

📌 Step 3: Verifying chapters in database...

✅ Found 3 chapters in database:

   ├─ ID: 101
   ├─ Title: Test Chapter 1: Introduction
   ├─ Slug: test-chapter-1-introduction
   ├─ Sort Order: 10
   ├─ Content Size: 147 bytes

   ├─ ID: 102
   ├─ Title: Test Chapter 2: Main Content
   ├─ Slug: test-chapter-2-main-content
   ├─ Sort Order: 20
   ├─ Content Size: 298 bytes

   ├─ ID: 103
   ├─ Title: Test Chapter 3: Conclusion
   ├─ Slug: test-chapter-3-conclusion
   ├─ Sort Order: 30
   ├─ Content Size: 289 bytes

📌 Step 4: Verification Summary...

   Expected chapters: 3
   Actual chapters: 3
   ✅ All chapters created successfully!

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

📊 FINAL REPORT:
   Subtopic ID: 25
   Chapter IDs: 101, 102, 103

   Total entities created: 4
   (1 subtopic + 3 chapters)

✅ The following workflow was verified:
   1. Create subtopic (course)
   2. Insert multiple chapters with content
   3. Query chapters from database
   4. Verify data integrity and count

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

WHAT THIS TESTS:
================================================================================

This is a COMPLETE END-TO-END test:

✅ Database connectivity
✅ Subtopic creation
✅ Chapter insertion
✅ HTML content storage
✅ Slug generation
✅ Transaction commit
✅ Querying relationships (pages + page_content)
✅ Data integrity (everything is there)
✅ Sorting (chapters in correct order)

Every single part of the workflow is tested.

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

COMPARING TO REAL USAGE:
================================================================================

This test script is almost identical to how rebuild_typescript_course.py works:

TEST SCRIPT:
1. Create subtopic "Test Course - Complete Integration"
2. Insert 3 test chapters
3. Verify all data in database

REBUILD SCRIPT:
1. Create subtopic "TypeScript Fundamentals"
2. Insert 12 real chapters from HTML files
3. Report success

The only differences:
- Real script reads HTML files (we hardcode test HTML)
- Real script has more chapters (we have 3 for testing)
- Real script has logging and progress (we have reporting)

The db_utils functions are IDENTICAL!

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

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

✅ How to create a complete course with chapters
✅ How to verify database relationships
✅ How to test end-to-end workflows
✅ How to use LEFT JOIN for verification
✅ How to measure content (LENGTH(body))
✅ How to order results and verify sort order

The pattern for complete integration:
1. Create parent entity (subtopic)
2. Create child entities (chapters)
3. Query relationships (pages + page_content)
4. Verify counts match
5. Report all IDs created

This is the pattern used in all database workflows!

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