reset_processing.py

"""
Safe database reset utility for YouTube Data Processor.

This utility implements safe reset mechanism for experimentation following PRP patterns:
- Preserves raw data (transcripts, comments, video metadata)
- Clears AI processing results (topic summaries, atomic insights)
- Resets processing status flags to 'pending'
- Confirmation prompts and transaction safety
- Comprehensive logging of reset operations
"""

import sqlite3
import sys
from typing import Dict, Any
from datetime import datetime
from pathlib import Path

# Add src to path for imports
sys.path.append(str(Path(__file__).parent / 'src'))

from src.services.database_service import DatabaseService
from src.utils.logging_config import YouTubeProcessorLogger

logger = YouTubeProcessorLogger().get_logger(__name__)


class ProcessingReset:
    """
    Safe database reset utility with confirmation and logging.
    
    Features:
    - Preserves raw data (channels, videos, transcripts, comments)
    - Clears AI processing results (topics, insights, embeddings)
    - Resets processing status flags
    - Transaction safety with rollback on errors
    - Confirmation prompts for safety
    - Comprehensive logging and statistics
    """
    
    def __init__(self, database_path: str = 'youtube_data.db'):
        """
        Initialize reset utility.
        
        Args:
            database_path: Path to SQLite database file
        """
        self.database_path = database_path
        self.db_service = DatabaseService(database_path)
        
        logger.info(f"🔧 Database reset utility initialized: {database_path}")
    
    def get_current_stats(self) -> Dict[str, Any]:
        """Get current database statistics before reset."""
        try:
            with self.db_service.get_connection() as conn:
                # Raw data statistics
                raw_stats = conn.execute("""
                    SELECT 
                        COUNT(DISTINCT c.channel_id) as channels,
                        COUNT(DISTINCT v.video_id) as videos,
                        COUNT(rt.video_id) as transcripts,
                        COUNT(rc.comment_id) as comments
                    FROM Channels c
                    LEFT JOIN Videos v ON c.channel_id = v.channel_id
                    LEFT JOIN RawTranscripts rt ON v.video_id = rt.video_id
                    LEFT JOIN RawComments rc ON v.video_id = rc.video_id
                """).fetchone()
                
                # AI processing statistics  
                ai_stats = conn.execute("""
                    SELECT 
                        COUNT(ts.summary_id) as topic_summaries,
                        COUNT(ai.insight_id) as atomic_insights,
                        COUNT(CASE WHEN ai.embedding_vector IS NOT NULL THEN 1 END) as embeddings
                    FROM TopicSummaries ts
                    FULL OUTER JOIN AtomicInsights ai ON ts.video_id = ai.video_id
                """).fetchone()
                
                # Processing status statistics
                status_stats = conn.execute("""
                    SELECT 
                        COUNT(CASE WHEN stage_1_status = 'complete' THEN 1 END) as stage1_complete,
                        COUNT(CASE WHEN stage_2_status = 'complete' THEN 1 END) as stage2_complete,
                        COUNT(CASE WHEN embedding_status = 'complete' THEN 1 END) as embeddings_complete
                    FROM VideoProcessingStatus
                """).fetchone()
                
                return {
                    'raw_data': {
                        'channels': raw_stats[0] if raw_stats else 0,
                        'videos': raw_stats[1] if raw_stats else 0,
                        'transcripts': raw_stats[2] if raw_stats else 0,
                        'comments': raw_stats[3] if raw_stats else 0
                    },
                    'ai_processing': {
                        'topic_summaries': ai_stats[0] if ai_stats else 0,
                        'atomic_insights': ai_stats[1] if ai_stats else 0,
                        'embeddings': ai_stats[2] if ai_stats else 0
                    },
                    'processing_status': {
                        'stage1_complete': status_stats[0] if status_stats else 0,
                        'stage2_complete': status_stats[1] if status_stats else 0,
                        'embeddings_complete': status_stats[2] if status_stats else 0
                    }
                }
                
        except Exception as e:
            logger.error(f"❌ Failed to get database statistics: {e}")
            return {'error': str(e)}
    
    def reset_ai_processing(self, confirm: bool = False) -> bool:
        """
        Reset AI processing results while preserving raw data.
        
        Args:
            confirm: Whether user has confirmed the reset operation
            
        Returns:
            bool: True if reset successful, False otherwise
        """
        if not confirm:
            logger.error("❌ Reset operation requires explicit confirmation")
            return False
        
        try:
            logger.info("🔄 Starting AI processing reset operation")
            
            # Get statistics before reset for reference
            self.get_current_stats()
            
            with self.db_service.transaction() as conn:
                # Clear AI processing results
                logger.info("🗑️  Clearing TopicSummaries table...")
                summaries_deleted = conn.execute("DELETE FROM TopicSummaries").rowcount
                
                logger.info("🗑️  Clearing AtomicInsights table...")
                insights_deleted = conn.execute("DELETE FROM AtomicInsights").rowcount
                
                # Reset processing status flags
                logger.info("🔄 Resetting processing status flags...")
                status_updated = conn.execute("""
                    UPDATE VideoProcessingStatus 
                    SET stage_1_status = 'pending',
                        stage_2_status = 'pending',
                        embedding_status = 'pending',
                        last_updated = ?
                    WHERE stage_1_status != 'pending' 
                       OR stage_2_status != 'pending' 
                       OR embedding_status != 'pending'
                """, (datetime.now(),)).rowcount
                
                # Clear processing checkpoints
                logger.info("🗑️  Clearing processing checkpoints...")
                checkpoints_deleted = conn.execute("DELETE FROM ProcessingCheckpoints").rowcount
                
                # Vacuum database to reclaim space
                logger.info("🧹 Optimizing database...")
                conn.execute("VACUUM")
            
            # Get statistics after reset
            after_stats = self.get_current_stats()
            
            # Log reset summary
            logger.info("✅ AI processing reset completed successfully:")
            logger.info(f"   🗑️  Topic summaries deleted: {summaries_deleted}")
            logger.info(f"   🗑️  Atomic insights deleted: {insights_deleted}")
            logger.info(f"   🔄 Processing statuses reset: {status_updated}")
            logger.info(f"   🗑️  Checkpoints cleared: {checkpoints_deleted}")
            logger.info("   📊 Raw data preserved:")
            logger.info(f"      📺 Channels: {after_stats['raw_data']['channels']}")
            logger.info(f"      🎬 Videos: {after_stats['raw_data']['videos']}")
            logger.info(f"      📄 Transcripts: {after_stats['raw_data']['transcripts']}")
            logger.info(f"      💬 Comments: {after_stats['raw_data']['comments']}")
            
            return True
            
        except Exception as e:
            logger.error(f"❌ AI processing reset failed: {e}")
            return False
    
    def reset_raw_data(self, confirm: bool = False, double_confirm: bool = False) -> bool:
        """
        DANGEROUS: Reset all data including raw transcripts and comments.
        
        Args:
            confirm: First confirmation
            double_confirm: Second confirmation required
            
        Returns:
            bool: True if reset successful, False otherwise
        """
        if not (confirm and double_confirm):
            logger.error("❌ Full reset requires double confirmation (confirm=True, double_confirm=True)")
            return False
        
        try:
            logger.warning("⚠️  Starting FULL database reset - THIS WILL DELETE ALL DATA!")
            
            # Get statistics before reset
            before_stats = self.get_current_stats()
            
            with self.db_service.transaction() as conn:
                # Delete all data in dependency order
                logger.warning("🗑️  Deleting AtomicInsights...")
                conn.execute("DELETE FROM AtomicInsights")
                
                logger.warning("🗑️  Deleting TopicSummaries...")
                conn.execute("DELETE FROM TopicSummaries")
                
                logger.warning("🗑️  Deleting VideoProcessingStatus...")
                conn.execute("DELETE FROM VideoProcessingStatus")
                
                logger.warning("🗑️  Deleting RawComments...")
                conn.execute("DELETE FROM RawComments")
                
                logger.warning("🗑️  Deleting RawTranscripts...")
                conn.execute("DELETE FROM RawTranscripts")
                
                logger.warning("🗑️  Deleting Videos...")
                conn.execute("DELETE FROM Videos")
                
                logger.warning("🗑️  Deleting Channels...")
                conn.execute("DELETE FROM Channels")
                
                logger.warning("🗑️  Deleting ProcessingCheckpoints...")
                conn.execute("DELETE FROM ProcessingCheckpoints")
                
                # Reset auto-increment counters
                logger.info("🔄 Resetting auto-increment counters...")
                conn.execute("DELETE FROM sqlite_sequence")
                
                # Vacuum database
                logger.info("🧹 Optimizing database...")
                conn.execute("VACUUM")
            
            logger.warning("✅ FULL database reset completed - all data deleted!")
            logger.warning(f"   📊 Data deleted: {before_stats['raw_data']['channels']} channels, {before_stats['raw_data']['videos']} videos")
            
            return True
            
        except Exception as e:
            logger.error(f"❌ Full database reset failed: {e}")
            return False
    
    def backup_database(self, backup_path: str = None) -> bool:
        """
        Create a backup of the database before reset operations.
        
        Args:
            backup_path: Path for backup file (defaults to timestamped backup)
            
        Returns:
            bool: True if backup successful, False otherwise
        """
        try:
            if not backup_path:
                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
                backup_path = f"{self.database_path}.backup_{timestamp}"
            
            logger.info(f"💾 Creating database backup: {backup_path}")
            
            # Create backup using SQLite backup API
            with sqlite3.connect(self.database_path) as source:
                with sqlite3.connect(backup_path) as backup:
                    source.backup(backup)
            
            # Verify backup
            backup_stats = ProcessingReset(backup_path).get_current_stats()
            if 'error' not in backup_stats:
                logger.info(f"✅ Database backup created successfully: {backup_path}")
                logger.info(f"   📊 Backup contains: {backup_stats['raw_data']['videos']} videos, {backup_stats['ai_processing']['atomic_insights']} insights")
                return True
            else:
                logger.error(f"❌ Backup verification failed: {backup_stats['error']}")
                return False
                
        except Exception as e:
            logger.error(f"❌ Database backup failed: {e}")
            return False
    
    def interactive_reset(self):
        """Interactive reset with user prompts and confirmations."""
        print("\n🔧 YouTube Data Processor - Database Reset Utility")
        print("=" * 60)
        
        # Show current statistics
        print("\n📊 Current Database Statistics:")
        stats = self.get_current_stats()
        
        if 'error' in stats:
            print(f"❌ Error reading database: {stats['error']}")
            return
        
        print(f"   📺 Channels: {stats['raw_data']['channels']}")
        print(f"   🎬 Videos: {stats['raw_data']['videos']}")
        print(f"   📄 Transcripts: {stats['raw_data']['transcripts']}")
        print(f"   💬 Comments: {stats['raw_data']['comments']}")
        print(f"   📝 Topic Summaries: {stats['ai_processing']['topic_summaries']}")
        print(f"   💎 Atomic Insights: {stats['ai_processing']['atomic_insights']}")
        print(f"   🎯 Embeddings: {stats['ai_processing']['embeddings']}")
        
        print("\n🔄 Processing Status:")
        print(f"   Stage 1 Complete: {stats['processing_status']['stage1_complete']}")
        print(f"   Stage 2 Complete: {stats['processing_status']['stage2_complete']}")
        print(f"   Embeddings Complete: {stats['processing_status']['embeddings_complete']}")
        
        print("\n🎯 Reset Options:")
        print("1. AI Processing Reset (RECOMMENDED)")
        print("   - Clears: Topic summaries, atomic insights, embeddings")
        print("   - Preserves: Raw transcripts, comments, video metadata")
        print("   - Resets: Processing status flags to 'pending'")
        print("\n2. Full Database Reset (DANGEROUS)")
        print("   - Clears: ALL DATA including raw transcripts and comments")
        print("   - Use only for complete fresh start")
        
        while True:
            choice = input("\nSelect reset type (1=AI only, 2=Full, q=Quit): ").strip().lower()
            
            if choice == 'q':
                print("👋 Reset cancelled by user")
                return
            elif choice == '1':
                self._ai_processing_reset_flow()
                break
            elif choice == '2':
                self._full_reset_flow()
                break
            else:
                print("❌ Invalid choice. Please enter 1, 2, or q")
    
    def _ai_processing_reset_flow(self):
        """Interactive flow for AI processing reset."""
        print("\n⚠️  AI PROCESSING RESET")
        print("This will delete all AI-generated content but preserve raw data.")
        
        # Offer backup
        backup_choice = input("\n💾 Create backup before reset? (y/N): ").strip().lower()
        if backup_choice in ['y', 'yes']:
            if not self.backup_database():
                print("❌ Backup failed. Aborting reset.")
                return
        
        # Final confirmation
        confirm = input("\n🚨 Confirm AI processing reset? Type 'RESET' to proceed: ").strip()
        
        if confirm == 'RESET':
            if self.reset_ai_processing(confirm=True):
                print("\n✅ AI processing reset completed successfully!")
                print("   You can now re-run the AI processing pipeline.")
            else:
                print("\n❌ AI processing reset failed!")
        else:
            print("❌ Reset cancelled - confirmation not received")
    
    def _full_reset_flow(self):
        """Interactive flow for full database reset."""
        print("\n🚨 FULL DATABASE RESET - DANGER!")
        print("This will delete ALL data including raw transcripts and comments.")
        print("This operation cannot be undone without a backup!")
        
        # Force backup creation
        print("\n💾 Creating mandatory backup...")
        if not self.backup_database():
            print("❌ Backup failed. Cannot proceed with full reset.")
            return
        
        # Double confirmation
        print("\n🚨 FINAL WARNING: This will delete everything!")
        confirm1 = input("Type 'DELETE EVERYTHING' to confirm: ").strip()
        
        if confirm1 == 'DELETE EVERYTHING':
            confirm2 = input("Type 'I UNDERSTAND' to double-confirm: ").strip()
            
            if confirm2 == 'I UNDERSTAND':
                if self.reset_raw_data(confirm=True, double_confirm=True):
                    print("\n✅ Full database reset completed!")
                    print("   Database is now empty and ready for fresh data collection.")
                else:
                    print("\n❌ Full database reset failed!")
            else:
                print("❌ Reset cancelled - second confirmation not received")
        else:
            print("❌ Reset cancelled - first confirmation not received")


def main():
    """Main entry point for reset utility."""
    import argparse
    
    parser = argparse.ArgumentParser(description='YouTube Data Processor - Database Reset Utility')
    parser.add_argument('--database', '-d', default='youtube_data.db', 
                       help='Database file path (default: youtube_data.db)')
    parser.add_argument('--ai-reset', action='store_true',
                       help='Reset AI processing data only (non-interactive)')
    parser.add_argument('--full-reset', action='store_true',
                       help='Full database reset (non-interactive, requires --confirm)')
    parser.add_argument('--confirm', action='store_true',
                       help='Confirm reset operation (required for non-interactive)')
    parser.add_argument('--backup', '-b', metavar='PATH',
                       help='Create backup at specified path')
    parser.add_argument('--stats', '-s', action='store_true',
                       help='Show database statistics only')
    
    args = parser.parse_args()
    
    # Initialize reset utility
    reset_util = ProcessingReset(args.database)
    
    if args.stats:
        # Show stats only
        stats = reset_util.get_current_stats()
        print("\n📊 Database Statistics:")
        print(f"Raw Data: {stats['raw_data']}")
        print(f"AI Processing: {stats['ai_processing']}")
        print(f"Processing Status: {stats['processing_status']}")
        return
    
    if args.backup:
        # Create backup
        if reset_util.backup_database(args.backup):
            print(f"✅ Backup created: {args.backup}")
        else:
            print(f"❌ Backup failed: {args.backup}")
            sys.exit(1)
        return
    
    if args.ai_reset:
        # Non-interactive AI reset
        if not args.confirm:
            print("❌ Non-interactive reset requires --confirm flag")
            sys.exit(1)
        
        if reset_util.reset_ai_processing(confirm=True):
            print("✅ AI processing reset completed")
        else:
            print("❌ AI processing reset failed")
            sys.exit(1)
        return
    
    if args.full_reset:
        # Non-interactive full reset
        if not args.confirm:
            print("❌ Non-interactive full reset requires --confirm flag")
            sys.exit(1)
        
        # Force backup for full reset
        backup_path = f"{args.database}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        if not reset_util.backup_database(backup_path):
            print("❌ Mandatory backup failed - aborting full reset")
            sys.exit(1)
        
        if reset_util.reset_raw_data(confirm=True, double_confirm=True):
            print("✅ Full database reset completed")
        else:
            print("❌ Full database reset failed")
            sys.exit(1)
        return
    
    # Default: interactive mode
    reset_util.interactive_reset()


if __name__ == '__main__':
    main()