"""
Migration: add category, brand, condition, location columns to
           sale_items and invoice_items tables.
Idempotent – safe to run multiple times.
"""
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

COLUMNS = {
    'sale_items': [
        ('category',  'VARCHAR'),
        ('brand',     'VARCHAR'),
        ('condition', 'VARCHAR'),
        ('location',  'VARCHAR'),
    ],
    'invoice_items': [
        ('category',  'VARCHAR'),
        ('brand',     'VARCHAR'),
        ('condition', 'VARCHAR'),
        ('location',  'VARCHAR'),
    ],
}

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

with engine.begin() as conn:
    for table, cols in COLUMNS.items():
        for col, col_type in cols:
            if column_exists(conn, table, col):
                print(f"  ALREADY EXISTS {table}.{col}")
            else:
                conn.execute(text(f'ALTER TABLE {table} ADD COLUMN {col} {col_type}'))
                print(f"  ADDED {table}.{col}")

print("Migration complete.")
