migrate_db.py

#!/usr/bin/env python3
"""
Database migration script to add missing columns
"""
import sqlite3
import os

def migrate_database():
    """Add missing columns to the database"""
    
    # Find the database file
    db_paths = [
        'instance/farm_management.db',
        'farm_management.db',
        'farms.db'
    ]
    
    db_path = None
    for path in db_paths:
        if os.path.exists(path):
            db_path = path
            break
    
    if not db_path:
        print("Database file not found!")
        return False
    
    print(f"Found database at: {db_path}")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if weather_alerts_enabled column exists
        cursor.execute("PRAGMA table_info(farms)")
        columns = [row[1] for row in cursor.fetchall()]
        
        if 'weather_alerts_enabled' not in columns:
            print("Adding weather_alerts_enabled column to farms table...")
            cursor.execute("ALTER TABLE farms ADD COLUMN weather_alerts_enabled BOOLEAN DEFAULT 1")
            print("✓ Added weather_alerts_enabled column")
        else:
            print("✓ weather_alerts_enabled column already exists")
        
        # Create weather_alerts table if it doesn't exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS weather_alerts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER,
                alert_type VARCHAR(100),
                severity VARCHAR(50),
                message TEXT,
                recommendations TEXT,
                is_active BOOLEAN DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id)
            )
        """)
        print("✓ weather_alerts table ready")
        
        # Create market_prices table if it doesn't exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS market_prices (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                crop_type VARCHAR(100),
                market_name VARCHAR(200),
                price_per_unit FLOAT,
                unit VARCHAR(50),
                trend VARCHAR(20),
                date DATE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("✓ market_prices table ready")
        
        # Create disease_detections table if it doesn't exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS disease_detections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER,
                disease_name VARCHAR(200),
                confidence_score FLOAT,
                treatment_recommendation TEXT,
                image_path VARCHAR(500),
                detected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id)
            )
        """)
        print("✓ disease_detections table ready")
        
        # Add new livestock fields to farms table
        try:
            # Check if farm_type column exists
            cursor.execute("PRAGMA table_info(farms)")
            columns = [column[1] for column in cursor.fetchall()]
            
            if 'farm_type' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN farm_type TEXT DEFAULT 'crop'")
                print("✓ Added farm_type column to farms table")
            
            if 'livestock_types' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN livestock_types TEXT")
                print("✓ Added livestock_types column to farms table")
            
            if 'livestock_count' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN livestock_count INTEGER")
                print("✓ Added livestock_count column to farms table")
            
            if 'housing_type' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN housing_type TEXT")
                print("✓ Added housing_type column to farms table")
            
            if 'feeding_system' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN feeding_system TEXT")
                print("✓ Added feeding_system column to farms table")
            
            if 'breed_info' not in columns:
                cursor.execute("ALTER TABLE farms ADD COLUMN breed_info TEXT")
                print("✓ Added breed_info column to farms table")
        
        except Exception as e:
            print(f"Error adding livestock columns: {e}")
            
        print("✓ Multi-sector farm fields ready")
        
    except Exception as e:
        print(f"Error updating farms table: {str(e)}")

    # Create livestock_records table
    try:
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS livestock_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER NOT NULL,
                farmer_id INTEGER NOT NULL,
                animal_id TEXT NOT NULL,
                animal_type TEXT NOT NULL,
                breed TEXT,
                gender TEXT,
                date_of_birth DATE,
                date_acquired DATE NOT NULL DEFAULT CURRENT_DATE,
                acquisition_method TEXT,
                current_weight REAL,
                vaccination_status TEXT,
                health_status TEXT DEFAULT 'healthy',
                breeding_status TEXT,
                milk_production_avg REAL,
                egg_production_avg INTEGER,
                is_active BOOLEAN DEFAULT 1,
                status TEXT DEFAULT 'active',
                notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id),
                FOREIGN KEY (farmer_id) REFERENCES farmers (id)
            )
        ''')
        print("✓ livestock_records table ready")
    except Exception as e:
        print(f"Error creating livestock_records table: {str(e)}")

    # Create production_records table
    try:
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS production_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER NOT NULL,
                farmer_id INTEGER NOT NULL,
                livestock_id INTEGER,
                production_date DATE NOT NULL DEFAULT CURRENT_DATE,
                production_type TEXT NOT NULL,
                quantity REAL NOT NULL,
                unit TEXT NOT NULL,
                quality_grade TEXT,
                unit_price REAL,
                total_value REAL,
                buyer_info TEXT,
                notes TEXT,
                weather_conditions TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id),
                FOREIGN KEY (farmer_id) REFERENCES farmers (id),
                FOREIGN KEY (livestock_id) REFERENCES livestock_records (id)
            )
        ''')
        print("✓ production_records table ready")
    except Exception as e:
        print(f"Error creating production_records table: {str(e)}")

    # Create feed_records table
    try:
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS feed_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER NOT NULL,
                farmer_id INTEGER NOT NULL,
                feed_date DATE NOT NULL DEFAULT CURRENT_DATE,
                feed_type TEXT NOT NULL,
                feed_name TEXT NOT NULL,
                quantity REAL NOT NULL,
                unit TEXT NOT NULL DEFAULT 'kg',
                cost_per_unit REAL,
                total_cost REAL,
                supplier TEXT,
                target_animals TEXT,
                animals_count INTEGER,
                protein_content REAL,
                energy_content REAL,
                notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id),
                FOREIGN KEY (farmer_id) REFERENCES farmers (id)
            )
        ''')
        print("✓ feed_records table ready")
    except Exception as e:
        print(f"Error creating feed_records table: {str(e)}")

    # Create health_records table
    try:
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS health_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farm_id INTEGER NOT NULL,
                farmer_id INTEGER NOT NULL,
                livestock_id INTEGER NOT NULL,
                event_date DATE NOT NULL DEFAULT CURRENT_DATE,
                event_type TEXT NOT NULL,
                vaccine_name TEXT,
                vaccine_batch TEXT,
                next_due_date DATE,
                symptoms TEXT,
                diagnosis TEXT,
                treatment_given TEXT,
                medication TEXT,
                dosage TEXT,
                vet_name TEXT,
                vet_contact TEXT,
                cost REAL,
                follow_up_required BOOLEAN DEFAULT 0,
                follow_up_date DATE,
                recovery_status TEXT,
                notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (farm_id) REFERENCES farms (id),
                FOREIGN KEY (farmer_id) REFERENCES farmers (id),
                FOREIGN KEY (livestock_id) REFERENCES livestock_records (id)
            )
        ''')
        print("✓ health_records table ready")
    except Exception as e:
        print(f"Error creating health_records table: {str(e)}")

    # Create daily_tasks table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS daily_tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                farmer_id INTEGER,
                farm_id INTEGER,
                task_date DATE DEFAULT (date('now')),
                task_type VARCHAR(100) NOT NULL,
                task_title VARCHAR(200) NOT NULL,
                task_description TEXT NOT NULL,
                priority VARCHAR(20) DEFAULT 'medium',
                estimated_duration INTEGER,
                weather_dependent BOOLEAN DEFAULT 0,
                crop_specific VARCHAR(100),
                is_completed BOOLEAN DEFAULT 0,
                completed_at DATETIME,
                completion_notes TEXT,
                completion_rating INTEGER,
                sent_via_telegram BOOLEAN DEFAULT 0,
                telegram_sent_at DATETIME,
                reminder_sent BOOLEAN DEFAULT 0,
                reminder_sent_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by VARCHAR(50) DEFAULT 'system',
                FOREIGN KEY (farmer_id) REFERENCES farmers (id),
                FOREIGN KEY (farm_id) REFERENCES farms (id)
            )
        """)
        print("✓ daily_tasks table ready")
        
        # Create task_completions table if it doesn't exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS task_completions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id INTEGER,
                farmer_id INTEGER,
                completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                completion_method VARCHAR(50) DEFAULT 'dashboard',
                completion_status VARCHAR(50) DEFAULT 'completed',
                notes TEXT,
                rating INTEGER,
                issues_faced TEXT,
                time_taken INTEGER,
                FOREIGN KEY (task_id) REFERENCES daily_tasks (id),
                FOREIGN KEY (farmer_id) REFERENCES farmers (id)
            )
        """)
        print("✓ task_completions table ready")
        
        conn.commit()
        conn.close()
        
        print("Database migration completed successfully!")
        return True
        
    except Exception as e:
        print(f"Error during migration: {e}")
        return False

if __name__ == "__main__":
    migrate_database()