<?php
/**
 * Products API Endpoint
 * Handles fetching (GET) and creating (POST) products with variants and images.
 */

require_once '../../includes/db_connection.php';
require_once '../includes/auth.php';
require_once '../includes/functions.php';

// Require authentication
requireAuth();

header('Content-Type: application/json');

$method = $_SERVER['REQUEST_METHOD'];
$db = Database::getInstance()->getConnection();

$action = $_GET['action'] ?? $_POST['action'] ?? '';
$requestId = $_GET['id'] ?? $_POST['id'] ?? null;

if ($method === 'POST' && $action === 'delete') {
    try {
        if (!$requestId) {
            throw new Exception('Product ID is required');
        }

        $stmt = $db->prepare("DELETE FROM products WHERE id = ?");
        $stmt->execute([$requestId]);

        if ($stmt->rowCount() > 0) {
            echo json_encode(['success' => true, 'message' => 'Product deleted successfully']);
        } else {
            http_response_code(404);
            echo json_encode(['success' => false, 'message' => 'Product not found']);
        }
    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
}
elseif ($method === 'POST') {
    try {
        $db->beginTransaction();

        $isUpdate = !empty($_POST['id']);
        $productId = $isUpdate ? (int)$_POST['id'] : null;

        // 1. Basic Validation
        if (empty($_POST['title'])) {
            throw new Exception('Product title is required');
        }

        // 2. Prepare Data
        $title = trim($_POST['title']);
        $description = $_POST['description'] ?? '';
        $status = $_POST['status'] ?? 'draft';
        $seo_title = $_POST['seo_title'] ?? $title;
        $seo_description = $_POST['seo_description'] ?? '';
        
        $basePrice = 0.00;
        if (isset($_POST['variants']) && is_array($_POST['variants'])) {
            $firstVariant = reset($_POST['variants']);
            $basePrice = $firstVariant['price'] ?? 0;
        }

        if ($isUpdate) {
            // Update Existing
            $stmt = $db->prepare("
                UPDATE products SET 
                    title = ?, description = ?, status = ?, price = ?, 
                    seo_title = ?, seo_description = ?
                WHERE id = ?
            ");
            $stmt->execute([$title, $description, $status, $basePrice, $seo_title, $seo_description, $productId]);
        } else {
            // Create New
            // Generate Slug only on creation
            $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $title)));
            $stmt = $db->prepare("SELECT COUNT(*) FROM products WHERE slug = ?");
            $stmt->execute([$slug]);
            if ($stmt->fetchColumn() > 0) {
                $slug .= '-' . time();
            }

            $stmt = $db->prepare("
                INSERT INTO products (
                    title, slug, description, status, price, 
                    seo_title, seo_description, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
            ");
            $stmt->execute([$title, $slug, $description, $status, $basePrice, $seo_title, $seo_description]);
            $productId = $db->lastInsertId();
        }

        // ... Rest of logic (Images, Variants, Collections) needs update ...

        // 4. Handle Images (Keep existing logic - simplified for brevity in this replace, ensuring no regression)
        $uploadDir = '../../assets/images/products/';

        // Handle Deletions/New Uploads (Existing logic remains valid, ensure vars correct)
        if (!empty($_POST['deleted_images'])) {
            foreach ($_POST['deleted_images'] as $delPath) {
                $stmtDel = $db->prepare("DELETE FROM product_images WHERE product_id = ? AND image_path = ?");
                $stmtDel->execute([$productId, $delPath]);
            }
        }
        
        $filesKey = isset($_FILES['new_images']) ? 'new_images' : 'images';
        if (isset($_FILES[$filesKey])) {
            foreach ($_FILES[$filesKey]['name'] as $key => $filename) {
                if ($_FILES[$filesKey]['error'][$key] === UPLOAD_ERR_OK) {
                    $tmpName = $_FILES[$filesKey]['tmp_name'][$key];
                    $ext = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
                    $allowed = ['jpg', 'jpeg', 'png', 'gif', 'webp'];
                    if (in_array($ext, $allowed)) {
                        $newFilename = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $title))) . '-' . uniqid() . '.' . $ext;
                        $dest = $uploadDir . $newFilename;
                        if (move_uploaded_file($tmpName, $dest)) {
                            $stmtImg = $db->prepare("INSERT INTO product_images (product_id, image_path) VALUES (?, ?)");
                            $stmtImg->execute([$productId, 'assets/images/products/' . $newFilename]);
                        }
                    }
                }
            }
        }

        // 5. Handle Product Options & Values (New Structured Logic)
        // Expected POST data: options: [{name: 'Color', values: ['Red', 'Blue']}]
        // Expected POST data: variants: [{options: {'Color': 'Red'}, price: 10, sku: '...'}]
        
        $optionValueMap = []; // Maps "OptionName|ValueName" => value_id

        if (isset($_POST['options_json'])) {
            $optionsData = json_decode($_POST['options_json'], true);
            
            // Sync Options: Delete all existing options (Cascade deletes values)
            // This is safer for data integrity during updates unless we want complex diffing
            if ($isUpdate) {
                $stmtDelOpt = $db->prepare("DELETE FROM product_options WHERE product_id = ?");
                $stmtDelOpt->execute([$productId]);
            }

            if (is_array($optionsData)) {
                $stmtOpt = $db->prepare("INSERT INTO product_options (product_id, name, position) VALUES (?, ?, ?)");
                $stmtVal = $db->prepare("INSERT INTO product_option_values (product_option_id, value, position) VALUES (?, ?, ?)");
                
                foreach ($optionsData as $optIdx => $opt) {
                    if (empty($opt['name'])) continue;
                    
                    $stmtOpt->execute([$productId, $opt['name'], $optIdx]);
                    $optId = $db->lastInsertId();
                    
                    if (!empty($opt['values']) && is_array($opt['values'])) {
                        foreach ($opt['values'] as $valIdx => $val) {
                            $stmtVal->execute([$optId, $val, $valIdx]);
                            $valId = $db->lastInsertId();
                            $optionValueMap[$opt['name'] . '|' . $val] = $valId;
                        }
                    }
                }
            }
        }

        // 6. Handle Variants (Structured)
        if ($isUpdate) {
             // Get existing variant IDs to handle deletions
             $stmtGetVars = $db->prepare("SELECT id FROM product_variants WHERE product_id = ?");
             $stmtGetVars->execute([$productId]);
             $existingIds = $stmtGetVars->fetchAll(PDO::FETCH_COLUMN);
             $submittedIds = [];
        }

        if (isset($_POST['variants']) && is_array($_POST['variants'])) {
            $stmtUpdVar = $db->prepare("UPDATE product_variants SET variant_name=?, price=?, sku=?, inventory_quantity=? WHERE id=?");
            $stmtInsVar = $db->prepare("INSERT INTO product_variants (product_id, variant_name, price, sku, inventory_quantity) VALUES (?, ?, ?, ?, ?)");
            $stmtLinkVal = $db->prepare("INSERT INTO product_variant_option_values (variant_id, option_value_id) VALUES (?, ?)");
            // If updating, we clear old option links for the specific variant? Yes.
            $stmtClearLinks = $db->prepare("DELETE FROM product_variant_option_values WHERE variant_id = ?");

            foreach ($_POST['variants'] as $variant) {
                // Determine Variant Name (Composite) for fallback display
                // If JS sends 'options' map, we prefer that, but 'name' might explicitly be sent.
                $vName = $variant['name'] ?? 'Default'; 
                $vPrice = !empty($variant['price']) ? $variant['price'] : 0;
                $vSku = !empty($variant['sku']) ? $variant['sku'] : null;
                $vInv = !empty($variant['inventory']) ? $variant['inventory'] : 0;
                $vId = !empty($variant['id']) ? $variant['id'] : null;
                $vOptions = isset($variant['options']) ? $variant['options'] : []; // {'Color': 'Red'}

                $currentVariantId = null;

                if ($isUpdate && $vId && in_array($vId, $existingIds)) {
                     // Update
                     try {
                        $stmtUpdVar->execute([$vName, $vPrice, $vSku, $vInv, $vId]);
                        $submittedIds[] = $vId;
                        $currentVariantId = $vId;
                        // Clear existing links to rebuild
                        $stmtClearLinks->execute([$vId]);
                     } catch (Exception $e) { }
                } else {
                     // Insert
                     try {
                         $stmtInsVar->execute([$productId, $vName, $vPrice, $vSku, $vInv]);
                         $currentVariantId = $db->lastInsertId();
                         if ($isUpdate) $submittedIds[] = $currentVariantId;
                     } catch (Exception $e) { }
                }

                // Link Options
                if ($currentVariantId && !empty($vOptions)) {
                    foreach ($vOptions as $optName => $optValue) {
                        $key = $optName . '|' . $optValue;
                        if (isset($optionValueMap[$key])) {
                            try {
                                $stmtLinkVal->execute([$currentVariantId, $optionValueMap[$key]]);
                            } catch (Exception $e) { /* Ignore duplicates */ }
                        }
                    }
                }
            }
        }
        
        // Delete removed variants
        if ($isUpdate) {
             $toDelete = array_diff($existingIds, $submittedIds);
             if (!empty($toDelete)) {
                 $inQuery = implode(',', array_fill(0, count($toDelete), '?'));
                 $stmtDelVar = $db->prepare("DELETE FROM product_variants WHERE id IN ($inQuery)");
                 $stmtDelVar->execute(array_values($toDelete));
             }
        }

        // 6. Handle Collections (Categories)
        // Delete existing relations first if updating
        if ($isUpdate) {
            $stmtDelCol = $db->prepare("DELETE FROM product_collections WHERE product_id = ?");
            $stmtDelCol->execute([$productId]);
        }

        if (isset($_POST['collection_ids']) && is_array($_POST['collection_ids'])) {
            $stmtCol = $db->prepare("INSERT INTO product_collections (product_id, collection_id) VALUES (?, ?)");
            foreach ($_POST['collection_ids'] as $collectionId) {
                if (!empty($collectionId)) {
                    $stmtCol->execute([$productId, (int)$collectionId]);
                }
            }
        }

        $db->commit();

        echo json_encode([
            'success' => true,
            'message' => 'Product created successfully',
            'id' => $productId
        ]);

    } catch (Exception $e) {
        $db->rollBack();
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
}
elseif ($method === 'GET') {
    try {
        // Validation for Single Product Fetch
        if (isset($_GET['slug'])) {
             $slug = $_GET['slug'];
             $stmt = $db->prepare("SELECT * FROM products WHERE slug = ?");
             $stmt->execute([$slug]);
             $product = $stmt->fetch(PDO::FETCH_ASSOC);

             if ($product) {
                 // Fetch Product Options & Values
                 $stmtOpts = $db->prepare("SELECT * FROM product_options WHERE product_id = ? ORDER BY position ASC");
                 $stmtOpts->execute([$product['id']]);
                 $options = $stmtOpts->fetchAll(PDO::FETCH_ASSOC);

                 foreach ($options as &$opt) {
                     $stmtVals = $db->prepare("SELECT * FROM product_option_values WHERE product_option_id = ? ORDER BY position ASC");
                     $stmtVals->execute([$opt['id']]);
                     $vals = $stmtVals->fetchAll(PDO::FETCH_ASSOC);
                     $opt['values'] = array_column($vals, 'value');
                 }
                 $product['options'] = $options;

                 // Fetch images
                 $stmtImg = $db->prepare("SELECT image_path, is_featured FROM product_images WHERE product_id = ?");
                 $stmtImg->execute([$product['id']]);
                 $product['images'] = $stmtImg->fetchAll(PDO::FETCH_ASSOC);

                 // Fetch variants (Updated to include options mapping)
                 $stmtVar = $db->prepare("SELECT * FROM product_variants WHERE product_id = ?");
                 $stmtVar->execute([$product['id']]);
                 $variants = $stmtVar->fetchAll(PDO::FETCH_ASSOC);
                 
                 // Check for linked options for each variant
                 foreach ($variants as &$v) {
                     $stmtLink = $db->prepare("
                        SELECT po.name as option_name, pov.value as option_value 
                        FROM product_variant_option_values pvov
                        JOIN product_option_values pov ON pvov.option_value_id = pov.id
                        JOIN product_options po ON pov.product_option_id = po.id
                        WHERE pvov.variant_id = ?
                     ");
                     $stmtLink->execute([$v['id']]);
                     $links = $stmtLink->fetchAll(PDO::FETCH_ASSOC);
                     
                     $v['options'] = [];
                     foreach ($links as $link) {
                         $v['options'][$link['option_name']] = $link['option_value'];
                     }
                 }
                 $product['variants'] = $variants;

                 // Fetch connected collections
                 $stmtCols = $db->prepare("SELECT collection_id, (SELECT name FROM collections WHERE id = collection_id) as name FROM product_collections WHERE product_id = ?");
                 $stmtCols->execute([$product['id']]);
                 $product['collections_data'] = $stmtCols->fetchAll(PDO::FETCH_ASSOC);

                 echo json_encode(['success' => true, 'data' => $product]);
             } else {
                 http_response_code(404);
                 echo json_encode(['success' => false, 'message' => 'Product not found']);
             }
             exit;
        }

        // List Logic
        $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
        $offset = ($page - 1) * $limit;
        
        $search = $_GET['search'] ?? '';
        $status = $_GET['status'] ?? '';

        // Build Query
        $where = [];
        $params = [];

        if (!empty($search)) {
            $where[] = "(title LIKE ? OR sku LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        if (!empty($status) && in_array($status, ['active', 'draft', 'archived'])) {
            $where[] = "status = ?";
            $params[] = $status;
        }

        $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        // Count Total
        $countStmt = $db->prepare("SELECT COUNT(*) FROM products $whereClause");
        $countStmt->execute($params);
        $totalItems = $countStmt->fetchColumn();

        // Fetch Products
        $sql = "
            SELECT 
                p.*,
                (SELECT image_path FROM product_images WHERE product_id = p.id ORDER BY is_featured DESC LIMIT 1) as main_image,
                (SELECT COUNT(*) FROM product_variants WHERE product_id = p.id) as variant_count,
                 -- Fetch associated collection names
                (
                    SELECT GROUP_CONCAT(c.name SEPARATOR ', ')
                    FROM collections c
                    JOIN product_collections pc ON c.id = pc.collection_id
                    WHERE pc.product_id = p.id
                ) as collection_names,
                -- Fetch first variant SKU if main SKU is empty
                (SELECT sku FROM product_variants WHERE product_id = p.id LIMIT 1) as variant_sku
            FROM products p
            $whereClause
            ORDER BY p.created_at DESC
            LIMIT ? OFFSET ?
        ";
        
        // Add limit/offset to params
        $params[] = $limit;
        $params[] = $offset;

        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Format Data
        $formattedProducts = [];
        foreach ($products as $p) {
            // Determine SKU to display
            $displaySku = !empty($p['sku']) ? $p['sku'] : ($p['variant_sku'] ?? 'N/A');

            $formattedProducts[] = [
                'id' => $p['id'],
                'slug' => $p['slug'],
                'title' => $p['title'],
                'status' => $p['status'],
                'price' => number_format($p['price'], 2),
                'sku' => $displaySku,
                'image' => $p['main_image'] ? $p['main_image'] : 'assets/images/placeholder.png',
                'collections' => $p['collection_names'] ?? 'Uncategorized',
                'variant_count' => $p['variant_count'],
                'created_at' => date('M d, Y', strtotime($p['created_at']))
            ];
        }

        // Pagination Data
        $totalPages = ceil($totalItems / $limit);
        
        echo json_encode([
            'success' => true,
            'data' => $formattedProducts,
            'pagination' => [
                'total_pages' => $totalPages,
                'current_page' => $page,
                'total_items' => $totalItems,
                'has_next' => $page < $totalPages,
                'has_prev' => $page > 1
            ]
        ]);

    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
}
elseif ($method === 'DELETE') {
    try {
        $id = $_GET['id'] ?? null;
        if (!$id) {
            throw new Exception('Product ID is required');
        }

        $stmt = $db->prepare("DELETE FROM products WHERE id = ?");
        $stmt->execute([$id]);

        if ($stmt->rowCount() > 0) {
            echo json_encode(['success' => true, 'message' => 'Product deleted successfully']);
        } else {
            http_response_code(404);
            echo json_encode(['success' => false, 'message' => 'Product not found']);
        }
    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
}
else {
    http_response_code(405);
    echo json_encode([
        'success' => false, 
        'message' => 'Method not allowed',
        'debug_method' => $method, // DEBUG
        'debug_get' => $_GET       // DEBUG
    ]);
}