"""
Migration script to add dimensional fields to workflow_definitions table
"""
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.core.database import engine
from sqlalchemy import text

def migrate():
    """Add dimensional workflow columns to workflow_definitions table"""
    print("Adding dimensional workflow columns to workflow_definitions...")

    columns_to_add = [
        ("dimension_type", "VARCHAR NULL"),
        ("dimension_scope", "VARCHAR DEFAULT 'global'"),
        ("cost_center_id", "VARCHAR NULL"),
        ("project_id", "VARCHAR NULL"),
        ("department_id", "VARCHAR NULL"),
        ("region_id", "VARCHAR NULL"),
        ("dimensional_only", "BOOLEAN DEFAULT FALSE"),
    ]

    for col_name, col_def in columns_to_add:
        with engine.connect() as conn:
            try:
                # Check if column exists
                result = conn.execute(text(f"""
                    SELECT column_name
                    FROM information_schema.columns
                    WHERE table_name = 'workflow_definitions' AND column_name = '{col_name}'
                """))
                if result.fetchone() is None:
                    conn.execute(text(f"ALTER TABLE workflow_definitions ADD COLUMN {col_name} {col_def}"))
                    conn.commit()
                    print(f"  Added column: {col_name}")
                else:
                    print(f"  Column already exists: {col_name}")
            except Exception as e:
                print(f"  Error adding column {col_name}: {e}")

    print("Migration completed successfully!")

def list_columns():
    """List current columns in workflow_definitions table"""
    with engine.connect() as conn:
        result = conn.execute(text("""
            SELECT column_name, data_type, is_nullable, column_default
            FROM information_schema.columns
            WHERE table_name = 'workflow_definitions'
            ORDER BY ordinal_position
        """))
        print("Current workflow_definitions columns:")
        for row in result:
            print(f"  {row[0]}: {row[1]} (nullable: {row[2]}, default: {row[3]})")

if __name__ == "__main__":
    if "--list-only" in sys.argv:
        list_columns()
    else:
        migrate()
        print("\nFinal column list:")
        list_columns()
