import sqlite3
import os

# Get absolute path to the database
BASE_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
DB_PATH = os.path.join(BASE_DIR, 'inventory.db')

def setup_bundle_table():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    print("Creating 'product_bundle' table...")
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS product_bundle (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            bundle_sku TEXT NOT NULL,
            component_sku TEXT NOT NULL,
            component_quantity INTEGER NOT NULL,
            UNIQUE(bundle_sku, component_sku)
        )
    ''')
    
    conn.commit()
    print("Table 'product_bundle' created successfully.")
    conn.close()

def add_bundle(bundle_sku, component_sku, quantity):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO product_bundle (bundle_sku, component_sku, component_quantity) VALUES (?, ?, ?)",
            (bundle_sku, component_sku, quantity)
        )
        conn.commit()
        print(f"Added bundle component: {bundle_sku} -> {component_sku} (qty: {quantity})")
    except sqlite3.IntegrityError:
        print(f"Error: Bundle component {bundle_sku} -> {component_sku} already exists.")
    except Exception as e:
        print(f"Error: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    setup_bundle_table()
    
    # Example usage (commented out)
    # add_bundle('STARTER-PACK-01', 'VEG-GUR-LUFF-01', 1)