#!/usr/bin/env python3
"""
PyraFiles Migration Script: is_public to visibility column
=========================================================

This script migrates existing PyraFiles databases from the old is_public boolean column
to the new visibility string column with three states: 'public', 'private', 'unlisted'.

Migration logic:
- is_public = True  → visibility = 'public'
- is_public = False → visibility = 'private'
- All is_public values are then set to FALSE (for compatibility)

The is_public column is kept in the database but set to a consistent value.

Usage:
    python migrate_visibility.py [--data-dir /path/to/data] [--dry-run]

Options:
    --data-dir    Path to PyraFiles data directory (default: ./data)
    --dry-run     Show what would be migrated without making changes
    --help        Show this help message
"""

import os
import sys
import argparse
import sqlite3
import glob
import logging
from pathlib import Path

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


def check_table_structure(cursor, table_name):
    """Check if table exists and get its column information."""
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = cursor.fetchall()
    column_names = [col[1] for col in columns]
    return columns, column_names


def migrate_database(db_path, dry_run=False):
    """Migrate a single database file."""
    log.info(f"Processing database: {db_path}")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if media table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='media'")
        if not cursor.fetchone():
            log.info(f"  No media table found in {db_path}, skipping")
            conn.close()
            return True
        
        # Get current table structure
        columns, column_names = check_table_structure(cursor, 'media')
        
        has_is_public = 'is_public' in column_names
        has_visibility = 'visibility' in column_names
        
        log.info(f"  Table structure: has_is_public={has_is_public}, has_visibility={has_visibility}")
        
        if not has_is_public and has_visibility:
            log.info(f"  Database already migrated, skipping")
            conn.close()
            return True
        
        if not has_is_public:
            log.warning(f"  No is_public column found in {db_path}, skipping")
            conn.close()
            return True
        
        # Get count of records to migrate
        cursor.execute("SELECT COUNT(*) FROM media")
        total_records = cursor.fetchone()[0]
        
        if total_records == 0:
            log.info(f"  No records to migrate in {db_path}")
            conn.close()
            return True
        
        log.info(f"  Found {total_records} records to migrate")
        
        if dry_run:
            # Show what would be migrated
            cursor.execute("SELECT id, filename, is_public FROM media LIMIT 10")
            sample_records = cursor.fetchall()
            log.info(f"  Sample records that would be migrated:")
            for record in sample_records:
                id_val, filename, is_public = record
                new_visibility = 'public' if is_public else 'private'
                log.info(f"    {filename}: is_public={is_public} → visibility='{new_visibility}'")
            if total_records > 10:
                log.info(f"    ... and {total_records - 10} more records")
            
            conn.close()
            return True
        
        # Perform the actual migration
        log.info(f"  Starting migration...")
        
        # Step 1: Add visibility column if it doesn't exist
        if not has_visibility:
            log.info(f"    Adding visibility column...")
            cursor.execute("ALTER TABLE media ADD COLUMN visibility TEXT DEFAULT 'public'")
        
        # Step 2: Migrate data
        log.info(f"    Migrating data...")
        cursor.execute("""
            UPDATE media 
            SET visibility = CASE 
                WHEN is_public = 1 THEN 'public'
                WHEN is_public = 0 THEN 'private'
                ELSE 'public'
            END
        """)
        
        migrated_count = cursor.rowcount
        log.info(f"    Updated {migrated_count} records")
        
        # Step 3: Verify migration
        cursor.execute("SELECT visibility, COUNT(*) FROM media GROUP BY visibility")
        visibility_counts = cursor.fetchall()
        log.info(f"    Post-migration visibility distribution:")
        for visibility, count in visibility_counts:
            log.info(f"      {visibility}: {count} records")
        
        # Step 4: Set is_public to FALSE (0) for all records
        # We'll keep the column but just set it to a consistent state
        log.info(f"    Setting is_public to FALSE for all records...")
        cursor.execute("UPDATE media SET is_public = 0")
        
        # Commit changes
        conn.commit()
        log.info(f"  ✅ Migration completed successfully!")
        
    except Exception as e:
        log.error(f"  ❌ Error migrating {db_path}: {e}")
        if 'conn' in locals():
            conn.rollback()
        return False
    finally:
        if 'conn' in locals():
            conn.close()
    
    return True


def find_namespace_databases(data_dir):
    """Find all namespace database files."""
    pattern = os.path.join(data_dir, "namespace_*.db")
    namespace_dbs = glob.glob(pattern)
    return namespace_dbs


def main():
    parser = argparse.ArgumentParser(
        description='Migrate PyraFiles databases from is_public to visibility column',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog=__doc__
    )
    parser.add_argument(
        '--data-dir',
        default='./data',
        help='Path to PyraFiles data directory (default: ./data)'
    )
    parser.add_argument(
        '--dry-run',
        action='store_true',
        help='Show what would be migrated without making changes'
    )
    
    args = parser.parse_args()
    
    data_dir = Path(args.data_dir)
    
    if not data_dir.exists():
        log.error(f"Data directory does not exist: {data_dir}")
        sys.exit(1)
    
    if not data_dir.is_dir():
        log.error(f"Data directory is not a directory: {data_dir}")
        sys.exit(1)
    
    log.info(f"Starting PyraFiles migration...")
    log.info(f"Data directory: {data_dir.absolute()}")
    log.info(f"Dry run: {args.dry_run}")
    
    # Find all namespace databases
    namespace_dbs = find_namespace_databases(str(data_dir))
    
    if not namespace_dbs:
        log.info("No namespace databases found. Nothing to migrate.")
        sys.exit(0)
    
    log.info(f"Found {len(namespace_dbs)} namespace databases to check:")
    for db_path in sorted(namespace_dbs):
        log.info(f"  {os.path.basename(db_path)}")
    
    if args.dry_run:
        log.info("\n" + "="*50)
        log.info("DRY RUN MODE - No changes will be made")
        log.info("="*50)
    
    # Migrate each database
    success_count = 0
    error_count = 0
    
    for db_path in sorted(namespace_dbs):
        if migrate_database(db_path, dry_run=args.dry_run):
            success_count += 1
        else:
            error_count += 1
    
    # Summary
    log.info("\n" + "="*50)
    log.info("MIGRATION SUMMARY")
    log.info("="*50)
    log.info(f"Total databases: {len(namespace_dbs)}")
    log.info(f"Successfully processed: {success_count}")
    log.info(f"Errors: {error_count}")
    
    if args.dry_run:
        log.info("\nThis was a dry run. No changes were made.")
        log.info("Run without --dry-run to perform the actual migration.")
    elif error_count == 0:
        log.info("\n✅ All databases migrated successfully!")
    else:
        log.warning(f"\n⚠️  {error_count} databases had errors. Check logs above.")
        sys.exit(1)


if __name__ == "__main__":
    main()