<?php 

if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME'])) {
    die('No direct script access allowed');
}

function createProductVariation(
    int $product_id, 
    ?string $price = null, 
    ?string $stock = null, 
    ?string $weight = null,
    ?string $weight_unit = null,
    ?string $image_path = null,
    ?string $sku = null
): int {
    try {
        $stmt = pdo()->prepare("INSERT INTO product_variations 
                                    (product_id, price, stock, weight, weight_unit, image_path, sku) 
                                    VALUES (?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$product_id, $price, $stock, $weight, $weight_unit, $image_path, $sku]);
        return (int)pdo()->lastInsertId();
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error creating variation for product {$product_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return 0;
    }
}

function addOptionToVariation(int $variation_id, int $attribute_value_id): bool {
    try {
        $stmt = pdo()->prepare("INSERT IGNORE INTO variation_options (variation_id, attribute_value_id) VALUES (?, ?)");
        return $stmt->execute([$variation_id, $attribute_value_id]);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error adding option {$attribute_value_id} to variation {$variation_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

function getProductVariations(int $product_id, bool $include_options = true): array {
    try {
        $stmt = pdo()->prepare("SELECT * FROM product_variations WHERE product_id = ? ORDER BY id ASC");
        $stmt->execute([$product_id]);
        $variations = $stmt->fetchAll();

        if ($include_options && !empty($variations)) {
            $variation_ids = array_column($variations, 'id');
            $placeholders = implode(',', array_fill(0, count($variation_ids), '?'));
            
            $sql_options = "SELECT vo.variation_id, vo.attribute_value_id, av.value, av.attribute_id, a.name as attribute_name
                            FROM variation_options vo
                            JOIN attribute_values av ON vo.attribute_value_id = av.id
                            JOIN attributes a ON av.attribute_id = a.id
                            WHERE vo.variation_id IN ({$placeholders})
                            ORDER BY a.name, av.value";
            $stmt_options = pdo()->prepare($sql_options);
            $stmt_options->execute($variation_ids);
            $options_raw = $stmt_options->fetchAll();

            $options_by_variation = [];
            foreach ($options_raw as $option) {
                $options_by_variation[$option['variation_id']][] = $option;
            }

            foreach ($variations as &$variation) {
                $variation['options'] = $options_by_variation[$variation['id']] ?? [];
                
                $option_names = array_map(function($opt) {
                    return $opt['value'];
                }, $variation['options']);
                $variation['full_name'] = implode(' - ', $option_names);
                
                $variation['platform_photo_id'] = $variation['image_path'] 
                    ? (rtrim(PRODUCT_IMAGE_PUBLIC_URL_BASE, '/') . '/' . ltrim($variation['image_path'], '/')) 
                    : null;
            }
            unset($variation);
        }

        return $variations;
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting variations for product {$product_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return [];
    }
}

function getVariationById(int $variation_id): ?array {
    try {
        $stmt = pdo()->prepare("SELECT * FROM product_variations WHERE id = ?");
        $stmt->execute([$variation_id]);
        $variation = $stmt->fetch();
        
        if (!$variation) {
            return null;
        }

        $sql_options = "SELECT av.value, a.name as attribute_name
                        FROM variation_options vo
                        JOIN attribute_values av ON vo.attribute_value_id = av.id
                        JOIN attributes a ON av.attribute_id = a.id
                        WHERE vo.variation_id = ?
                        ORDER BY a.name, av.value";
        $stmt_options = pdo()->prepare($sql_options);
        $stmt_options->execute([$variation_id]);
        $options = $stmt_options->fetchAll();

        $variation['options'] = $options;
        $option_names = array_map(function($opt) {
            return $opt['value'];
        }, $options);
        $variation['full_name'] = implode(' - ', $option_names);
        
        $variation['platform_photo_id'] = $variation['image_path'] 
            ? (rtrim(PRODUCT_IMAGE_PUBLIC_URL_BASE, '/') . '/' . ltrim($variation['image_path'], '/')) 
            : null;

        return $variation;
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting variation by ID {$variation_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return null;
    }
}

function findVariationByOptions(int $product_id, array $selected_value_ids): ?array {
    if (empty($selected_value_ids)) {
        return null;
    }

    sort($selected_value_ids);
    $value_count = count($selected_value_ids);

    try {
        $placeholders = implode(',', array_fill(0, $value_count, '?'));
        $sql = "SELECT pv.* FROM product_variations pv
                JOIN variation_options vo ON pv.id = vo.variation_id
                WHERE pv.product_id = ? 
                AND vo.attribute_value_id IN ({$placeholders})
                GROUP BY pv.id
                HAVING COUNT(DISTINCT vo.attribute_value_id) = ? 
                AND (SELECT COUNT(*) FROM variation_options vo2 WHERE vo2.variation_id = pv.id) = ? 
                LIMIT 1";

        $params = array_merge([$product_id], $selected_value_ids, [$value_count, $value_count]);
        $stmt = pdo()->prepare($sql);
        $stmt->execute($params);
        $variation = $stmt->fetch();

        return $variation ? getVariationById($variation['id']) : null;

    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error finding variation by options for product {$product_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return null;
    }
}

function updateVariationField(int $variation_id, string $field, $value): bool {
    $allowed_fields = ['price', 'stock', 'weight', 'weight_unit', 'image_path', 'sku'];
    
    if (!in_array($field, $allowed_fields)) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - Security Warning: Invalid field '{$field}' in updateVariationField for variation {$variation_id}.\n", FILE_APPEND);
        return false;
    }

    $sql = "UPDATE product_variations SET `{$field}` = ? WHERE id = ?";
    $params = [$value, $variation_id];

    try {
        $stmt = pdo()->prepare($sql);
        return $stmt->execute($params);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error updating variation field ($field) for variation {$variation_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

function deleteVariation(int $variation_id): bool {
    try {
        $stmt = pdo()->prepare("DELETE FROM product_variations WHERE id = ?");
        return $stmt->execute([$variation_id]);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error deleting variation {$variation_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

?>