<?php
// Database migration script for wishlist functionality
// This script creates the wishlists table and should be run once

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../helpers/Env.php';

try {
    $db = Database::getInstance();
    
    // Create the wishlists table
    $sql = "
    CREATE TABLE IF NOT EXISTS wishlists (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        item_type ENUM('item', 'coin') NOT NULL,
        item_id INT NOT NULL,
        notes TEXT DEFAULT NULL,
        target_price DECIMAL(10, 2) DEFAULT NULL,
        priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_user_item (user_id, item_type, item_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_item (user_id, item_type, item_id),
        INDEX idx_user_created (user_id, created_at DESC)
    ) ENGINE=InnoDB;
    ";
    
    $db->exec($sql);
    
    echo "SUCCESS: Wishlist table created successfully!\n";
    echo "Table 'wishlists' has been created with the following structure:\n";
    echo "- id (Primary Key, Auto Increment)\n";
    echo "- user_id (Foreign Key to users table)\n";
    echo "- item_type (ENUM: 'item' for banknotes, 'coin' for coins)\n";
    echo "- item_id (Foreign reference to items or coins table)\n";
    echo "- notes (TEXT, optional user notes)\n";
    echo "- target_price (DECIMAL, optional target price in RSD)\n";
    echo "- priority (ENUM: 'low', 'medium', 'high')\n";
    echo "- created_at (Timestamp)\n";
    echo "- updated_at (Timestamp)\n";
    echo "- Unique constraint on (user_id, item_type, item_id)\n";
    echo "- Indexes for performance optimization\n\n";
    
    echo "Wishlist functionality is now ready to use!\n";
    
} catch (PDOException $e) {
    echo "ERROR: Failed to create wishlist table: " . $e->getMessage() . "\n";
    exit(1);
} catch (Exception $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
    exit(1);
}
?>