#!/usr/bin/env python3
"""
Migrate SDA Hymnal data from SQLite to PostgreSQL
This script transfers hymns, thematic lists, and responsive readings
while preserving the original formatting and verse structure.
"""

import sqlite3
import psycopg2
import os
import sys
from typing import Dict, List, Tuple
import logging

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def get_postgres_connection():
    """Get PostgreSQL database connection from environment variables"""
    try:
        database_url = os.getenv('DATABASE_URL')
        if not database_url:
            raise ValueError("DATABASE_URL environment variable not set")
        
        conn = psycopg2.connect(database_url)
        return conn
    except Exception as e:
        logger.error(f"Failed to connect to PostgreSQL: {e}")
        sys.exit(1)

def get_sqlite_connection(sqlite_path: str):
    """Get SQLite database connection"""
    try:
        conn = sqlite3.connect(sqlite_path)
        conn.row_factory = sqlite3.Row  # Enable column access by name
        return conn
    except Exception as e:
        logger.error(f"Failed to connect to SQLite database at {sqlite_path}: {e}")
        sys.exit(1)

def format_old_hymnal_content(content: str) -> str:
    """
    Convert 1941 hymnal content to match 1985 format by adding verse numbers.
    Assumes verses are separated by double newlines.
    """
    if not content or not content.strip():
        return content
    
    # Split content by double newlines (verse separators)
    verses = content.strip().split('\n\n')
    
    # Filter out empty verses
    verses = [verse.strip() for verse in verses if verse.strip()]
    
    # Add verse numbers
    formatted_verses = []
    for i, verse in enumerate(verses, 1):
        # Don't add numbers to very short content (likely chorus or single line)
        if len(verse.split('\n')) >= 2:
            formatted_verse = f"{i}.\n{verse}"
        else:
            formatted_verse = verse
        formatted_verses.append(formatted_verse)
    
    # Rejoin with double newlines
    return '\n\n'.join(formatted_verses)

def get_hymnal_mappings(pg_cursor) -> Dict[str, str]:
    """Get the hymnal ID mappings from PostgreSQL"""
    pg_cursor.execute("SELECT id, code FROM hymnals")
    mappings = {}
    for row in pg_cursor.fetchall():
        hymnal_id, code = row
        if code == 'sda-1985':
            mappings['en-newVersion'] = hymnal_id
        elif code == 'sda-1941':
            mappings['en-oldVersion'] = hymnal_id
    
    if len(mappings) != 2:
        raise ValueError("Could not find both hymnal versions in database")
    
    return mappings

def migrate_hymns(sqlite_conn, pg_conn, hymnal_mappings: Dict[str, str]):
    """Migrate hymns from SQLite to PostgreSQL"""
    logger.info("Starting hymns migration...")
    
    sqlite_cursor = sqlite_conn.cursor()
    pg_cursor = pg_conn.cursor()
    
    # Get all hymns from SQLite
    sqlite_cursor.execute("""
        SELECT number, title, content, hymnal_type, 
               COALESCE(is_favorite, 0) as is_favorite 
        FROM hymns 
        ORDER BY hymnal_type, number
    """)
    
    hymns = sqlite_cursor.fetchall()
    logger.info(f"Found {len(hymns)} hymns to migrate")
    
    # Insert hymns into PostgreSQL
    insert_count = 0
    for hymn in hymns:
        try:
            hymnal_id = hymnal_mappings[hymn['hymnal_type']]
            
            # Format 1941 hymnal content to match 1985 format
            content = hymn['content']
            if hymn['hymnal_type'] == 'en-oldVersion':
                content = format_old_hymnal_content(content)
                logger.debug(f"Formatted hymn {hymn['number']} from 1941 hymnal")
            
            pg_cursor.execute("""
                INSERT INTO hymns (hymnal_id, number, title, content, is_favorite)
                VALUES (%s, %s, %s, %s, %s)
                ON CONFLICT (hymnal_id, number) DO UPDATE SET
                    title = EXCLUDED.title,
                    content = EXCLUDED.content,
                    is_favorite = EXCLUDED.is_favorite,
                    updated_at = NOW()
            """, (
                hymnal_id,
                hymn['number'],
                hymn['title'],
                content,
                bool(hymn['is_favorite'])
            ))
            
            insert_count += 1
            
            if insert_count % 100 == 0:
                logger.info(f"Migrated {insert_count} hymns...")
                pg_conn.commit()
                
        except Exception as e:
            logger.error(f"Failed to migrate hymn {hymn['number']} ({hymn['hymnal_type']}): {e}")
            continue
    
    pg_conn.commit()
    logger.info(f"Successfully migrated {insert_count} hymns")

def migrate_thematic_lists(sqlite_conn, pg_conn, hymnal_mappings: Dict[str, str]):
    """Migrate thematic lists and ambits from SQLite to PostgreSQL"""
    logger.info("Starting thematic lists migration...")
    
    sqlite_cursor = sqlite_conn.cursor()
    pg_cursor = pg_conn.cursor()
    
    # Get all thematic lists
    sqlite_cursor.execute("""
        SELECT id, thematic, hymnal_type 
        FROM thematic_lists 
        ORDER BY hymnal_type, id
    """)
    
    thematic_lists = sqlite_cursor.fetchall()
    logger.info(f"Found {len(thematic_lists)} thematic lists to migrate")
    
    # Track old_id -> new_id mapping for thematic lists
    thematic_list_mappings = {}
    
    for idx, theme_list in enumerate(thematic_lists):
        try:
            hymnal_id = hymnal_mappings[theme_list['hymnal_type']]
            
            # Insert thematic list
            pg_cursor.execute("""
                INSERT INTO thematic_lists (hymnal_id, name, sort_order)
                VALUES (%s, %s, %s)
                RETURNING id
            """, (hymnal_id, theme_list['thematic'], idx + 1))
            
            new_list_id = pg_cursor.fetchone()[0]
            thematic_list_mappings[theme_list['id']] = new_list_id
            
        except Exception as e:
            logger.error(f"Failed to migrate thematic list {theme_list['thematic']}: {e}")
            continue
    
    pg_conn.commit()
    logger.info(f"Successfully migrated {len(thematic_list_mappings)} thematic lists")
    
    # Now migrate thematic ambits
    logger.info("Starting thematic ambits migration...")
    
    sqlite_cursor.execute("""
        SELECT thematic_list_id, ambit, start_number, end_number
        FROM thematic_ambits
        ORDER BY thematic_list_id, start_number
    """)
    
    ambits = sqlite_cursor.fetchall()
    logger.info(f"Found {len(ambits)} thematic ambits to migrate")
    
    ambit_count = 0
    for ambit in ambits:
        try:
            if ambit['thematic_list_id'] not in thematic_list_mappings:
                logger.warning(f"Skipping ambit for missing thematic list ID {ambit['thematic_list_id']}")
                continue
                
            new_list_id = thematic_list_mappings[ambit['thematic_list_id']]
            
            pg_cursor.execute("""
                INSERT INTO thematic_ambits (thematic_list_id, name, start_number, end_number, sort_order)
                VALUES (%s, %s, %s, %s, %s)
            """, (
                new_list_id,
                ambit['ambit'],
                ambit['start_number'],
                ambit['end_number'],
                ambit_count + 1
            ))
            
            ambit_count += 1
            
        except Exception as e:
            logger.error(f"Failed to migrate ambit {ambit['ambit']}: {e}")
            continue
    
    pg_conn.commit()
    logger.info(f"Successfully migrated {ambit_count} thematic ambits")

def migrate_responsive_readings(sqlite_conn, pg_conn):
    """Migrate responsive readings from SQLite to PostgreSQL"""
    logger.info("Starting responsive readings migration...")
    
    sqlite_cursor = sqlite_conn.cursor()
    pg_cursor = pg_conn.cursor()
    
    # Get all responsive readings
    sqlite_cursor.execute("""
        SELECT number, title, content, COALESCE(is_favorite, 0) as is_favorite
        FROM responsive_readings
        ORDER BY number
    """)
    
    readings = sqlite_cursor.fetchall()
    logger.info(f"Found {len(readings)} responsive readings to migrate")
    
    reading_count = 0
    for reading in readings:
        try:
            pg_cursor.execute("""
                INSERT INTO responsive_readings (number, title, content, is_favorite)
                VALUES (%s, %s, %s, %s)
                ON CONFLICT (number) DO UPDATE SET
                    title = EXCLUDED.title,
                    content = EXCLUDED.content,
                    is_favorite = EXCLUDED.is_favorite,
                    updated_at = NOW()
            """, (
                reading['number'],
                reading['title'],
                reading['content'],
                bool(reading['is_favorite'])
            ))
            
            reading_count += 1
            
        except Exception as e:
            logger.error(f"Failed to migrate responsive reading {reading['number']}: {e}")
            continue
    
    pg_conn.commit()
    logger.info(f"Successfully migrated {reading_count} responsive readings")

def main():
    """Main migration function"""
    if len(sys.argv) != 2:
        print("Usage: python3 migrate_hymnal_data.py <path_to_hymnarium.db>")
        sys.exit(1)
    
    sqlite_path = sys.argv[1]
    
    if not os.path.exists(sqlite_path):
        logger.error(f"SQLite database file not found: {sqlite_path}")
        sys.exit(1)
    
    logger.info("Starting SDA Hymnal migration...")
    logger.info(f"Source: {sqlite_path}")
    logger.info(f"Target: PostgreSQL (DATABASE_URL)")
    
    # Connect to both databases
    sqlite_conn = get_sqlite_connection(sqlite_path)
    pg_conn = get_postgres_connection()
    
    try:
        # Get hymnal mappings
        pg_cursor = pg_conn.cursor()
        hymnal_mappings = get_hymnal_mappings(pg_cursor)
        logger.info(f"Found hymnal mappings: {hymnal_mappings}")
        
        # Run migrations
        migrate_hymns(sqlite_conn, pg_conn, hymnal_mappings)
        migrate_thematic_lists(sqlite_conn, pg_conn, hymnal_mappings)
        migrate_responsive_readings(sqlite_conn, pg_conn)
        
        # Print summary
        pg_cursor.execute("SELECT COUNT(*) FROM hymns")
        total_hymns = pg_cursor.fetchone()[0]
        
        pg_cursor.execute("SELECT COUNT(*) FROM thematic_lists")
        total_themes = pg_cursor.fetchone()[0]
        
        pg_cursor.execute("SELECT COUNT(*) FROM responsive_readings")
        total_readings = pg_cursor.fetchone()[0]
        
        logger.info("Migration completed successfully!")
        logger.info(f"Final counts: {total_hymns} hymns, {total_themes} themes, {total_readings} readings")
        
    except Exception as e:
        logger.error(f"Migration failed: {e}")
        pg_conn.rollback()
        raise
        
    finally:
        sqlite_conn.close()
        pg_conn.close()

if __name__ == "__main__":
    main()