from app import app, db, User
from flask_migrate import upgrade
from sqlalchemy import text

def init_db():
    with app.app_context():
        # First, try to create the section table if it doesn't exist
        try:
            db.session.execute(text("""
                CREATE TABLE IF NOT EXISTS section (
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    description TEXT,
                    icon VARCHAR(200)
                )
            """))
            db.session.commit()
            print("Section table created or already exists")
        except Exception as e:
            print(f"Error creating section table: {e}")
            db.session.rollback()

        # Then try to add the section_id column if it doesn't exist
        try:
            # Check if column exists
            result = db.session.execute(text("""
                SELECT column_name 
                FROM information_schema.columns 
                WHERE table_name='plant' AND column_name='section_id'
            """))
            if not result.fetchone():
                print("Adding section_id column to plant table")
                db.session.execute(text("""
                    ALTER TABLE plant 
                    ADD COLUMN section_id INTEGER 
                    REFERENCES section(id)
                """))
                db.session.commit()
                print("Added section_id column successfully")
            else:
                print("section_id column already exists")
        except Exception as e:
            print(f"Error adding section_id column: {e}")
            db.session.rollback()

        # Create default admin user if it doesn't exist
        try:
            admin = User.query.filter_by(username='admin').first()
            if not admin:
                admin = User(username='admin')
                admin.set_password('admin123')  # You should change this password after first login
                db.session.add(admin)
                db.session.commit()
                print("Default admin user created!")
        except Exception as e:
            print(f"Error creating admin user: {e}")
            db.session.rollback()

if __name__ == '__main__':
    init_db()