"""
Migration: Add category, brand, condition, location columns

Products table:
  - condition  (already has category + brand)
  - location

purchase_items table:
  - category
  - brand
  - condition
  - location

Run with:
    python scripts/migrate_add_category_brand_condition_location.py
"""
import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

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

MIGRATIONS = [
    # products table
    ("products", "condition", "ALTER TABLE products ADD COLUMN condition VARCHAR"),
    ("products", "location",  "ALTER TABLE products ADD COLUMN location VARCHAR"),
    # purchase_items table
    ("purchase_items", "category",  "ALTER TABLE purchase_items ADD COLUMN category VARCHAR"),
    ("purchase_items", "brand",     "ALTER TABLE purchase_items ADD COLUMN brand VARCHAR"),
    ("purchase_items", "condition", "ALTER TABLE purchase_items ADD COLUMN condition VARCHAR"),
    ("purchase_items", "location",  "ALTER TABLE purchase_items ADD COLUMN location VARCHAR"),
]

def column_exists(conn, table, column):
    result = conn.execute(text(
        "SELECT column_name FROM information_schema.columns "
        "WHERE table_name = :t AND column_name = :c"
    ), {"t": table, "c": column})
    return result.fetchone() is not None

def run():
    with engine.begin() as conn:
        for table, column, sql in MIGRATIONS:
            if column_exists(conn, table, column):
                print(f"  SKIP  {table}.{column} (already exists)")
            else:
                conn.execute(text(sql))
                print(f"  ADDED {table}.{column}")
    print("\nMigration complete.")

if __name__ == "__main__":
    run()
