<?php
header("Content-Type: application/json; charset=UTF-8");
require_once '../config/cors.php';
require_once '../config/database.php';

try {
    $database = new Database();
    $db = $database->getConnection();
    
    $results = [];
    
    // 1. Check if deals table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'deals'");
    if ($checkTable->rowCount() == 0) {
        $results[] = "Creating deals table...";
        
        // Create deals table
        $createDealsTable = "
            CREATE TABLE IF NOT EXISTS deals (
                id VARCHAR(36) PRIMARY KEY,
                product_id VARCHAR(36) NOT NULL,
                title VARCHAR(255) NOT NULL,
                description TEXT,
                discount_percentage DECIMAL(5,2) NOT NULL,
                original_price DECIMAL(10,2) NOT NULL,
                discounted_price DECIMAL(10,2) NOT NULL,
                is_flash_deal BOOLEAN DEFAULT FALSE,
                flash_deal_hours INT DEFAULT 24,
                social_requirements JSON,
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                expires_at TIMESTAMP NULL,
                INDEX idx_product_id (product_id),
                INDEX idx_is_active (is_active),
                INDEX idx_expires_at (expires_at)
            )";
        
        $db->exec($createDealsTable);
        $results[] = "✓ Deals table created successfully";
    } else {
        $results[] = "✓ Deals table already exists";
    }
    
    // 2. Check if user_deal_progress table exists
    $checkProgressTable = $db->query("SHOW TABLES LIKE 'user_deal_progress'");
    if ($checkProgressTable->rowCount() == 0) {
        $results[] = "Creating user_deal_progress table...";
        
        $createProgressTable = "
            CREATE TABLE IF NOT EXISTS user_deal_progress (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id VARCHAR(36) NOT NULL,
                deal_id VARCHAR(36) NOT NULL,
                instagram_verified BOOLEAN DEFAULT FALSE,
                youtube_verified BOOLEAN DEFAULT FALSE,
                instagram_verified_at TIMESTAMP NULL,
                youtube_verified_at TIMESTAMP NULL,
                deal_unlocked BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE,
                UNIQUE KEY unique_user_deal (user_id, deal_id),
                INDEX idx_user_id (user_id),
                INDEX idx_deal_id (deal_id)
            )";
        
        $db->exec($createProgressTable);
        $results[] = "✓ User deal progress table created successfully";
    } else {
        $results[] = "✓ User deal progress table already exists";
    }
    
    // 3. Clean up any deals with missing product data
    $cleanupQuery = "DELETE FROM deals WHERE product_id NOT IN (SELECT id FROM products)";
    $cleanupStmt = $db->prepare($cleanupQuery);
    $cleanupStmt->execute();
    $deletedCount = $cleanupStmt->rowCount();
    
    if ($deletedCount > 0) {
        $results[] = "✓ Cleaned up {$deletedCount} deals with missing product data";
    } else {
        $results[] = "✓ No orphaned deals found";
    }
    
    // 4. Update any deals with null/empty titles
    $updateQuery = "UPDATE deals SET title = CONCAT('Deal for ', (SELECT name FROM products WHERE products.id = deals.product_id)) WHERE title IS NULL OR title = '' OR title = 'undefined'";
    $updateStmt = $db->prepare($updateQuery);
    $updateStmt->execute();
    $updatedCount = $updateStmt->rowCount();
    
    if ($updatedCount > 0) {
        $results[] = "✓ Fixed {$updatedCount} deals with missing titles";
    } else {
        $results[] = "✓ All deals have proper titles";
    }
    
    // 5. Get current deals count
    $countQuery = "SELECT COUNT(*) as count FROM deals";
    $countStmt = $db->prepare($countQuery);
    $countStmt->execute();
    $count = $countStmt->fetch(PDO::FETCH_ASSOC)['count'];
    
    $results[] = "✓ Total deals in database: {$count}";
    
    echo json_encode([
        'success' => true,
        'message' => 'Deals system fixed successfully',
        'results' => $results,
        'deals_count' => $count
    ]);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Failed to fix deals system: ' . $e->getMessage(),
        'trace' => $e->getTraceAsString()
    ]);
}
?>