================================================================================
PRACTICAL PYTHON SCRIPTING - CHALLENGE 2 SOLUTION
Chapter 6: Building Your Own Utilities
Challenge: Data Transformation Utility
================================================================================

PROBLEM:
Build a utility that:
1. Reads a CSV file
2. Transforms data (cleanup, validation)
3. Writes to database
4. Generates report

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

import sys
from pathlib import Path
import csv

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

SCRIPT_DIR = Path(__file__).resolve().parent
DATA_FILE = SCRIPT_DIR / "users.csv"

class DataTransformer:
    """Transform and validate CSV data."""

    def __init__(self):
        self.data = []
        self.errors = []
        self.warnings = []

    def read_csv(self, filepath):
        """Read CSV file."""
        try:
            with open(filepath, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)
                self.data = list(reader)
            print(f"✅ Read {len(self.data)} rows from {filepath.name}")
            return True
        except Exception as e:
            print(f"❌ Error reading CSV: {e}")
            return False

    def validate_row(self, row, row_num):
        """Validate a single row."""
        required = ['email', 'name', 'role']

        for field in required:
            if not row.get(field) or not row[field].strip():
                self.errors.append(f"Row {row_num}: Missing {field}")
                return False

        # Validate email format
        if '@' not in row['email']:
            self.errors.append(f"Row {row_num}: Invalid email")
            return False

        return True

    def transform_row(self, row):
        """Clean and transform row data."""
        return {
            'email': row['email'].strip().lower(),
            'name': row['name'].strip(),
            'role': row['role'].strip(),
            'status': row.get('status', 'active').strip().lower()
        }

    def process(self):
        """Process all data."""
        print(f"\n📝 Transforming {len(self.data)} rows...\n")

        valid_data = []
        for i, row in enumerate(self.data, 1):
            if not self.validate_row(row, i):
                continue

            transformed = self.transform_row(row)
            valid_data.append(transformed)

        print(f"✅ Processed {len(valid_data)} valid rows")
        print(f"⚠️ Skipped {len(self.errors)} invalid rows")

        return valid_data

    def report(self):
        """Show transformation report."""
        print("\n" + "=" * 60)
        print("TRANSFORMATION REPORT")
        print("=" * 60 + "\n")

        print(f"Total rows processed: {len(self.data)}")
        print(f"Valid rows: {len(self.data) - len(self.errors)}")
        print(f"Invalid rows: {len(self.errors)}\n")

        if self.errors:
            print("❌ Errors:")
            for error in self.errors[:10]:  # Show first 10
                print(f"   {error}")
            if len(self.errors) > 10:
                print(f"   ... and {len(self.errors) - 10} more")


def insert_into_database(db, data):
    """Insert transformed data into database."""
    cursor = db.connection.cursor()

    try:
        for row in data:
            query = """
                INSERT INTO users (email, name, role, status)
                VALUES (%s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    role = VALUES(role),
                    status = VALUES(status)
            """
            cursor.execute(query, (
                row['email'],
                row['name'],
                row['role'],
                row['status']
            ))

        db.connection.commit()
        cursor.close()
        print(f"\n✅ Inserted {len(data)} records into database")
        return True

    except Exception as e:
        print(f"❌ Database error: {e}")
        db.connection.rollback()
        return False


def main():
    """Main transformation workflow."""
    print("=" * 60)
    print("🚀 Data Transformation Utility")
    print("=" * 60 + "\n")

    # Step 1: Read CSV
    transformer = DataTransformer()
    if not transformer.read_csv(DATA_FILE):
        return False

    # Step 2: Transform data
    valid_data = transformer.process()

    # Step 3: Connect to database
    print(f"\n🔌 Connecting to database...")
    db = DatabaseConnection()
    if not db.connect():
        return False

    try:
        # Step 4: Insert into database
        if not insert_into_database(db, valid_data):
            return False

        # Step 5: Report
        transformer.report()

        print("\n" + "=" * 60)
        print("✅ TRANSFORMATION COMPLETE!")
        print("=" * 60)
        return True

    finally:
        db.disconnect()


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

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

KEY CONCEPTS:
================================================================================

1. CSV READING
   import csv
   reader = csv.DictReader(f)
   - DictReader returns dicts (easier than lists)
   - Automatically uses first row as headers

2. DATA VALIDATION
   - Check required fields
   - Validate format (email, phone, etc.)
   - Log errors for user

3. DATA TRANSFORMATION
   - Normalize: .strip(), .lower()
   - Convert types: int(), float()
   - Map values: status codes, etc.

4. ERROR HANDLING
   - Collect errors instead of failing immediately
   - Show summary to user
   - Use ON DUPLICATE KEY UPDATE for idempotence

5. REPORTING
   - Show what was processed
   - Show what failed
   - Give user actionable feedback

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

EXAMPLE CSV FILE:
================================================================================

users.csv:

email,name,role,status
alice@example.com,Alice Smith,admin,active
bob@example.com,Bob Jones,user,active
charlie@example.com,Charlie Brown,user,inactive

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

PATTERNS APPLIED:
================================================================================

✅ Class-based organization (better than functions for related operations)
✅ Error collection (don't fail on first error, show all)
✅ Data validation (check before transforming)
✅ Database idempotence (ON DUPLICATE KEY UPDATE)
✅ Comprehensive reporting (what worked, what failed)

This is a reusable pattern for ANY CSV import!

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