#!/usr/bin/env python3
"""
Migration 001: Add description column
Date: 2025-08-08
Description: Add description field to pet_pictures table for optional image descriptions
"""

import sqlite3
import sys
from datetime import datetime


def check_column_exists(cursor, table_name, column_name):
    """Check if a column exists in a table"""
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [column[1] for column in cursor.fetchall()]
    return column_name in columns


def migrate_up(cursor):
    """Apply the migration"""
    if not check_column_exists(cursor, 'pet_pictures', 'description'):
        print("Adding description column to pet_pictures table...")
        cursor.execute("ALTER TABLE pet_pictures ADD COLUMN description TEXT")
        return True
    else:
        print("Description column already exists, skipping...")
        return False


def migrate_down(cursor):
    """Rollback the migration (SQLite doesn't support dropping columns easily)"""
    print("WARNING: Cannot rollback this migration easily in SQLite")
    print("Description column will remain but can be ignored")
    return False


def main():
    """Run the migration"""
    db_path = "pet_pictures.db"
    
    try:
        db = sqlite3.connect(db_path)
        cursor = db.cursor()
        
        print(f"Running migration 001: Add description column")
        print(f"Database: {db_path}")
        print(f"Timestamp: {datetime.now().isoformat()}")
        print("-" * 50)
        
        if migrate_up(cursor):
            db.commit()
            print("✓ Migration 001 applied successfully")
        else:
            print("• Migration 001 already applied")
            
        # Verify the migration
        cursor.execute("PRAGMA table_info(pet_pictures)")
        columns = cursor.fetchall()
        print("\nFinal schema:")
        for col in columns:
            nullable = "NOT NULL" if col[3] else "NULL"
            default = f"DEFAULT {col[4]}" if col[4] else ""
            print(f"  {col[1]} {col[2]} {nullable} {default}")
            
    except sqlite3.Error as e:
        print(f"❌ Migration failed: {e}")
        return 1
    except Exception as e:
        print(f"❌ Unexpected error: {e}")
        return 1
    finally:
        if 'db' in locals():
            db.close()
    
    return 0


if __name__ == "__main__":
    sys.exit(main())