================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 3 SOLUTION
Chapter 5: Building rebuild_typescript_course.py
Challenge: Complete Rebuild Script
================================================================================

PROBLEM:
Create a script that:
1. Finds all chapter files
2. Parses metadata from HTML
3. Connects to database using db_utils
4. Creates subtopic and inserts all chapters
5. Reports final count

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

import sys
from pathlib import Path
import re

# Import from db_utils
sys.path.insert(0, str(Path(__file__).parent))
from db_utils import DatabaseConnection, get_or_create_subtopic, insert_chapter_with_content

# Constants
SUBJECT_ID = 3  # Programming
SUBJECT_NAME = "Programming"
SUBTOPIC_NAME = "TypeScript Fundamentals"

# Paths
SCRIPT_DIR = Path(__file__).resolve().parent
CHAPTERS_DIR = SCRIPT_DIR / "courses" / "Programming" / "TypeScript" / "html"


def find_chapters():
    """Find all chapter HTML files."""
    if not CHAPTERS_DIR.exists():
        print(f"❌ Chapters directory not found: {CHAPTERS_DIR}")
        return []

    html_files = sorted(CHAPTERS_DIR.glob("ts1-*.html"))
    if not html_files:
        print(f"⚠️ No chapter files found in {CHAPTERS_DIR}")
        return []

    print(f"✅ Found {len(html_files)} chapter files\n")
    return html_files


def parse_chapter(html_file):
    """Extract chapter data from HTML file."""
    try:
        html_content = html_file.read_text(encoding='utf-8')

        # Extract chapter number
        match = re.match(r"ts1-(\d+)", html_file.stem)
        if not match:
            return None
        chapter_num = int(match.group(1))

        # Extract title from <h2>
        title_match = re.search(r'<h2>(.*?)</h2>', html_content)
        chapter_title = title_match.group(1).strip() if title_match else f"Chapter {chapter_num}"

        return {
            'number': chapter_num,
            'title': chapter_title,
            'html': html_content,
            'sort_order': chapter_num * 10
        }

    except Exception as e:
        print(f"❌ Error parsing {html_file.name}: {e}")
        return None


def main():
    """Main rebuild workflow."""
    print("=" * 60)
    print("🚀 TypeScript Course Rebuild")
    print("=" * 60 + "\n")

    # Step 1: Find chapters
    print("📚 Step 1: Finding chapter files...")
    html_files = find_chapters()
    if not html_files:
        print("❌ No chapters found. Exiting.")
        return False

    # Step 2: Connect to database
    print("📚 Step 2: Connecting to database...")
    db = DatabaseConnection()
    if not db.connect():
        print("❌ Database connection failed")
        return False

    try:
        # Step 3: Create or get subtopic
        print(f"\n📚 Step 3: Creating subtopic '{SUBTOPIC_NAME}'...\n")
        subtopic_id = get_or_create_subtopic(
            db,
            subject_id=SUBJECT_ID,
            subtopic_name=SUBTOPIC_NAME,
            description="Master TypeScript from fundamentals to advanced patterns"
        )

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

        # Step 4: Insert chapters
        print(f"\n📚 Step 4: Inserting {len(html_files)} chapters...\n")

        chapters_inserted = 0
        chapters_failed = 0

        for html_file in html_files:
            chapter = parse_chapter(html_file)
            if not chapter:
                chapters_failed += 1
                continue

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

            if page_id:
                chapters_inserted += 1
            else:
                chapters_failed += 1

        # Step 5: Report results
        print("\n" + "=" * 60)
        print("✅ REBUILD COMPLETE!")
        print("=" * 60)
        print(f"\n📊 Results:")
        print(f"   Course: {SUBTOPIC_NAME}")
        print(f"   Subject: {SUBJECT_NAME}")
        print(f"   Chapters Inserted: {chapters_inserted}")
        print(f"   Chapters Failed: {chapters_failed}")
        print(f"   Total Found: {len(html_files)}")

        if chapters_failed > 0:
            print(f"\n⚠️ Some chapters failed to insert. Check output above.")

        return chapters_failed == 0

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

    finally:
        db.disconnect()


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

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

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

1. IMPORTS & SETUP
   - Import db_utils functions
   - Define constants for subject/course
   - Calculate paths using Path(__file__)

2. find_chapters()
   - Use CHAPTERS_DIR.glob("ts1-*.html")
   - Return sorted list of HTML files
   - Return empty list if none found

3. parse_chapter()
   - Read HTML file content
   - Extract chapter number from filename
   - Extract title from <h2> tag with regex
   - Return dict with all metadata

4. main() - The Complete Workflow
   Step 1: Find all chapter files
   Step 2: Connect to database
   Step 3: Create subtopic
   Step 4: For each chapter:
           - Parse metadata
           - Insert into database
   Step 5: Report results

5. CLEANUP
   - finally block ensures db.disconnect()
   - Always runs, even if errors occur

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

TESTING:
================================================================================

python rebuild_typescript_course.py

Output:
============================================================
🚀 TypeScript Course Rebuild
============================================================

📚 Step 1: Finding chapter files...
✅ Found 12 chapter files

📚 Step 2: Connecting to database...
✅ Database connection established

📚 Step 3: Creating subtopic 'TypeScript Fundamentals'...

✅ Created subtopic 'TypeScript Fundamentals' with ID 8

📚 Step 4: Inserting 12 chapters...

✅ Created chapter with ID: 101
✅ Created chapter with ID: 102
✅ Created chapter with ID: 103
✅ Created chapter with ID: 104
✅ Created chapter with ID: 105
✅ Created chapter with ID: 106
✅ Created chapter with ID: 107
✅ Created chapter with ID: 108
✅ Created chapter with ID: 109
✅ Created chapter with ID: 110
✅ Created chapter with ID: 111
✅ Created chapter with ID: 112

============================================================
✅ REBUILD COMPLETE!
============================================================

📊 Results:
   Course: TypeScript Fundamentals
   Subject: Programming
   Chapters Inserted: 12
   Chapters Failed: 0
   Total Found: 12

✅ Connection closed

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

EVERYTHING BROUGHT TOGETHER:
================================================================================

This script uses ALL chapters 1-5:

Chapter 1 - Paths & File System:
✅ Path(__file__).resolve().parent
✅ CHAPTERS_DIR.glob("ts1-*.html")
✅ .exists() and .read_text()

Chapter 2 - Configuration:
✅ DatabaseConnection loads .env
✅ Credentials automatic (no hard-coding)

Chapter 3 - Database Connections:
✅ db.connect() / db.disconnect()
✅ try/finally for cleanup

Chapter 4 - db_utils.py:
✅ get_or_create_subtopic()
✅ insert_chapter_with_content()

Chapter 5 - This Script:
✅ Orchestrates the complete workflow
✅ Brings all pieces together

This is a REAL PRODUCTION SCRIPT!

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

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

✅ How to orchestrate a complex workflow
✅ How to integrate multiple modules
✅ How to handle errors at each step
✅ How to report comprehensive results
✅ How to use try/finally for cleanup

The complete workflow:
1. Find files (filesystem)
2. Parse data (regex)
3. Connect to database (db_utils)
4. Create containers (get_or_create_subtopic)
5. Insert data (insert_chapter_with_content)
6. Report results (logging)

This is the pattern for ALL data import/rebuild scripts!

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

NEXT STEPS:
================================================================================

To use this script in production:

1. Copy to your project:
   cp rebuild_typescript_course.py /var/www/myapp/

2. Ensure .env exists with credentials

3. Ensure chapter files exist at:
   /var/www/myapp/courses/Programming/TypeScript/html/ts1-*.html

4. Run it:
   python rebuild_typescript_course.py

5. Verify in database:
   SELECT COUNT(*) FROM pages WHERE subtopic_id = 8;

You now have a complete, production-ready rebuild script!

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