#!/usr/bin/env python3
"""
Migration: Add sentence-level categorization support

This migration:
1. Creates new tables (SubmissionSentence)
2. Adds sentence_analysis_done column to Submission
3. Adds sentence_id column to TrainingExample
4. Does NOT auto-segment existing submissions (admin must re-analyze)

Run: python migrations/migrate_to_sentence_level.py
"""

import sys
import os

# Add parent directory to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app, db
from app.models.models import Submission, SubmissionSentence, TrainingExample
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def migrate():
    """Run migration to add sentence-level support"""

    app = create_app()

    with app.app_context():
        logger.info("Starting sentence-level categorization migration...")

        # Step 1: Add new column to submissions table using raw SQL
        logger.info("Updating submissions table schema...")
        try:
            db.session.execute(db.text(
                "ALTER TABLE submissions ADD COLUMN sentence_analysis_done BOOLEAN DEFAULT 0"
            ))
            db.session.commit()
            logger.info("✓ Added sentence_analysis_done column")
        except Exception as e:
            if "duplicate column name" in str(e).lower():
                logger.info("✓ Column sentence_analysis_done already exists")
                db.session.rollback()
            else:
                raise

        # Step 2: Add sentence_id column to training_examples
        logger.info("Updating training_examples table schema...")
        try:
            db.session.execute(db.text(
                "ALTER TABLE training_examples ADD COLUMN sentence_id INTEGER"
            ))
            db.session.commit()
            logger.info("✓ Added sentence_id column")
        except Exception as e:
            if "duplicate column name" in str(e).lower():
                logger.info("✓ Column sentence_id already exists")
                db.session.rollback()
            else:
                raise

        # Step 3: Create new tables (if they don't exist)
        logger.info("Creating sentence tables...")
        db.create_all()
        logger.info("✓ Tables created/verified")

        # Step 4: Verify schema
        submissions = Submission.query.count()
        logger.info(f"✓ Found {submissions} existing submissions")

        logger.info("✓ Migration complete")
        
        # Step 4: Summary
        print("\n" + "="*70)
        print("✓ MIGRATION COMPLETE!")
        print("="*70)
        print(f"""
Summary:
  - Database schema updated
  - {submissions} submissions ready for sentence-level analysis
  - 0 sentences (admin must run analysis)

Next Steps:
  1. Restart the Flask app
  2. Go to Admin → Submissions
  3. Click "Analyze All" to perform sentence-level analysis
  4. View sentence breakdown in submission cards

The system is backward compatible - old submission-level 
categories are preserved and will be used as fallback.
""")
        
        return True

if __name__ == '__main__':
    try:
        success = migrate()
        sys.exit(0 if success else 1)
    except Exception as e:
        logger.error(f"Migration failed: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)