<?php
/**
 * Products API Endpoint
 * Handles fetching, creating, updating, and deleting products with dual persistence:
 * 1. MySQL database (if connected) with automatic table creation & migration
 * 2. Server-side JSON storage (products.json) as robust fallback & static sync
 */

require_once __DIR__ . '/db.php';

$JSON_FILE = __DIR__ . '/products.json';
$ALT_JSON_FILE = dirname(dirname(__DIR__)) . '/data_storage/products.json';

// Helper: Read products from JSON storage
function readJsonProducts($filePath) {
    if (!file_exists($filePath)) {
        return [];
    }
    $raw = @file_get_contents($filePath);
    if (!$raw) return [];
    $data = json_decode($raw, true);
    if (!is_array($data)) return [];
    return array_values(array_filter($data, function($p) {
        $id = $p['id'] ?? '';
        return !empty($id) && strpos($id, 'prod_170000000000') !== 0 && !preg_match('/^prod_(tshirt|polo|hoodie|shop|cap|belt|badge|jacket|amulet|bag|backpack)_/', $id);
    }));
}

// Helper: Write products to JSON storage (both local and data_storage if accessible)
function writeJsonProducts($products) {
    global $JSON_FILE, $ALT_JSON_FILE;
    $encoded = json_encode(array_values($products), JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
    @file_put_contents($JSON_FILE, $encoded);
    if (file_exists(dirname($ALT_JSON_FILE))) {
        @file_put_contents($ALT_JSON_FILE, $encoded);
    }
}

// Initialize MySQL table if connection is active
if ($pdo !== null) {
    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS `products` (
            `id` VARCHAR(50) PRIMARY KEY,
            `name` VARCHAR(255) NOT NULL,
            `slug` VARCHAR(255) NOT NULL UNIQUE,
            `description` TEXT NULL,
            `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            `compare_at_price` DECIMAL(10,2) NULL,
            `images_json` TEXT NULL,
            `category_id` VARCHAR(50) NOT NULL,
            `rating` DECIMAL(3,2) DEFAULT 5.00,
            `reviews_count` INT DEFAULT 0,
            `sizes_json` TEXT NULL,
            `is_new` TINYINT(1) DEFAULT 0,
            `is_recommended` TINYINT(1) DEFAULT 0,
            `is_pre_order` TINYINT(1) DEFAULT 0,
            `season_tag` VARCHAR(100) NULL,
            `stock_count` INT DEFAULT 100,
            `in_stock` TINYINT(1) DEFAULT 1,
            `department_slugs_json` TEXT NULL,
            `merchant_email` VARCHAR(191) NULL,
            `video_url` TEXT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

        // Clean up any mock seed products
        $pdo->exec("DELETE FROM products WHERE id LIKE 'prod_170000000000%' OR id LIKE 'prod_tshirt_%' OR id LIKE 'prod_polo_%' OR id LIKE 'prod_hoodie_%' OR id LIKE 'prod_shop_%' OR id LIKE 'prod_cap_%' OR id LIKE 'prod_amulet_%' OR name = 'เสื้อเกราะ' OR name LIKE '%รถมอเตอร์ไซค์%';");
    } catch (Exception $e) {
        // Table creation error ignored; will use JSON fallback
    }
}

$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';

switch ($method) {
    case 'GET':
        handleGet($pdo);
        break;
    case 'POST':
        handlePost($pdo);
        break;
    case 'PUT':
        handlePut($pdo);
        break;
    case 'DELETE':
        handleDelete($pdo);
        break;
    default:
        sendResponse(false, null, 'Method not allowed', 405);
}

function handleGet($pdo) {
    global $JSON_FILE;

    $reqId = $_GET['id'] ?? null;
    $reqSlug = $_GET['slug'] ?? null;
    $merchantEmail = $_GET['merchantEmail'] ?? null;
    $categoryId = $_GET['categoryId'] ?? null;
    $search = $_GET['q'] ?? null;

    // 1. Try fetching from MySQL if available
    if ($pdo !== null) {
        try {
            if ($reqId) {
                $stmt = $pdo->prepare("SELECT * FROM products WHERE id = :id LIMIT 1");
                $stmt->execute([':id' => $reqId]);
                $row = $stmt->fetch();
                if ($row) {
                    sendResponse(true, formatProductRow($row), 'พบสินค้า');
                }
            }

            if ($reqSlug) {
                $stmt = $pdo->prepare("SELECT * FROM products WHERE slug = :slug LIMIT 1");
                $stmt->execute([':slug' => $reqSlug]);
                $row = $stmt->fetch();
                if ($row) {
                    sendResponse(true, formatProductRow($row), 'พบสินค้า');
                }
            }

            $query = "SELECT * FROM products WHERE 1=1";
            $params = [];
            if ($merchantEmail) {
                $query .= " AND LOWER(merchant_email) = LOWER(:email)";
                $params[':email'] = $merchantEmail;
            }
            if ($categoryId) {
                $query .= " AND category_id = :catId";
                $params[':catId'] = $categoryId;
            }
            if ($search) {
                $query .= " AND (name LIKE :q OR description LIKE :q)";
                $params[':q'] = '%' . $search . '%';
            }
            $query .= " ORDER BY created_at DESC";

            $stmt = $pdo->prepare($query);
            $stmt->execute($params);
            $rows = $stmt->fetchAll();

            if (!empty($rows)) {
                $products = array_map('formatProductRow', $rows);
                sendResponse(true, $products, 'ดึงรายการสินค้าจากฐานข้อมูลสำเร็จ');
            }
        } catch (Exception $e) {
            // MySQL error, smoothly proceed to JSON fallback
        }
    }

    // 2. JSON Storage Fallback
    $all = readJsonProducts($JSON_FILE);

    if ($reqId) {
        foreach ($all as $p) {
            if (($p['id'] ?? '') === $reqId) {
                sendResponse(true, $p, 'พบสินค้า');
            }
        }
        sendResponse(false, null, 'ไม่พบสินค้า', 404);
    }

    if ($reqSlug) {
        foreach ($all as $p) {
            if (($p['slug'] ?? '') === $reqSlug) {
                sendResponse(true, $p, 'พบสินค้า');
            }
        }
        sendResponse(false, null, 'ไม่พบสินค้า', 404);
    }

    $filtered = $all;
    if ($merchantEmail) {
        $filtered = array_filter($filtered, function($p) use ($merchantEmail) {
            return strtolower($p['merchantEmail'] ?? '') === strtolower($merchantEmail);
        });
    }
    if ($categoryId) {
        $filtered = array_filter($filtered, function($p) use ($categoryId) {
            return ($p['categoryId'] ?? '') === $categoryId;
        });
    }
    if ($search) {
        $q = mb_strtolower($search, 'UTF-8');
        $filtered = array_filter($filtered, function($p) use ($q) {
            $name = mb_strtolower($p['name'] ?? '', 'UTF-8');
            $desc = mb_strtolower($p['description'] ?? '', 'UTF-8');
            return strpos($name, $q) !== false || strpos($desc, $q) !== false;
        });
    }

    sendResponse(true, array_values($filtered), 'ดึงรายการสินค้าสำเร็จ');
}

function handlePost($pdo) {
    global $JSON_FILE;
    $body = getJsonInput();

    if (empty($body['name']) || !isset($body['price'])) {
        sendResponse(false, null, 'กรุณากรอกชื่อและราคาสินค้า', 400);
    }

    $id = !empty($body['id']) ? $body['id'] : ('prod_' . time() . rand(100, 999));
    $name = trim($body['name']);
    $slug = !empty($body['slug']) ? $body['slug'] : (strtolower(preg_replace('/[^a-zA-Z0-9ก-๙]+/', '-', $name)) . '-' . rand(10, 99));
    $description = $body['description'] ?? '';
    $price = (float)$body['price'];
    $compareAtPrice = isset($body['compareAtPrice']) ? (float)$body['compareAtPrice'] : null;
    $images = is_array($body['images'] ?? null) && count($body['images']) > 0 
        ? $body['images'] 
        : [(!empty($body['image']) ? $body['image'] : '/u69319090023/Shop23/images/products/tshirt_black_1.svg')];
    $categoryId = $body['categoryId'] ?? 'cat_clothing';
    $rating = isset($body['rating']) ? (float)$body['rating'] : 5.0;
    $reviewsCount = isset($body['reviewsCount']) ? (int)$body['reviewsCount'] : 0;
    $sizes = is_array($body['sizes'] ?? null) && count($body['sizes']) > 0 ? $body['sizes'] : ['S', 'M', 'L', 'XL'];
    $isNew = !empty($body['isNew']);
    $isRecommended = !empty($body['isRecommended']);
    $isPreOrder = !empty($body['isPreOrder']);
    $seasonTag = $body['seasonTag'] ?? null;
    $stockCount = isset($body['stockCount']) ? (int)$body['stockCount'] : 50;
    $inStock = isset($body['inStock']) ? (bool)$body['inStock'] : ($stockCount > 0);
    $departmentSlugs = is_array($body['departmentSlugs'] ?? null) ? $body['departmentSlugs'] : [];
    $merchantEmail = $body['merchantEmail'] ?? 'admin@cmtc.ac.th';
    $videoUrl = $body['videoUrl'] ?? null;
    $relatedProductIds = is_array($body['relatedProductIds'] ?? null) ? $body['relatedProductIds'] : [];

    $productObj = [
        'id'                => $id,
        'name'              => $name,
        'slug'              => $slug,
        'description'       => $description,
        'price'             => $price,
        'compareAtPrice'    => $compareAtPrice,
        'images'            => $images,
        'categoryId'        => $categoryId,
        'rating'            => $rating,
        'reviewsCount'      => $reviewsCount,
        'sizes'             => $sizes,
        'isNew'             => $isNew,
        'isRecommended'     => $isRecommended,
        'isPreOrder'        => $isPreOrder,
        'seasonTag'         => $seasonTag,
        'stockCount'        => $stockCount,
        'inStock'           => $inStock,
        'departmentSlugs'   => $departmentSlugs,
        'merchantEmail'     => $merchantEmail,
        'videoUrl'          => $videoUrl,
        'relatedProductIds' => $relatedProductIds
    ];

    // 1. Save to JSON File
    $existing = readJsonProducts($JSON_FILE);
    $found = false;
    foreach ($existing as $k => $item) {
        if (($item['id'] ?? '') === $id) {
            $existing[$k] = $productObj;
            $found = true;
            break;
        }
    }
    if (!$found) {
        array_unshift($existing, $productObj);
    }
    writeJsonProducts($existing);

    // 2. Save to MySQL if available
    if ($pdo !== null) {
        insertOrUpdateProductMySQL($pdo, $productObj);
    }

    sendResponse(true, $productObj, 'บันทึกสินค้าลงระบบสำเร็จ', 201);
}

function handlePut($pdo) {
    global $JSON_FILE;
    $body = getJsonInput();
    $id = $body['id'] ?? null;

    if (!$id) {
        sendResponse(false, null, 'ต้องระบุ ID สินค้า', 400);
    }

    // 1. Update in JSON File
    $existing = readJsonProducts($JSON_FILE);
    $updatedProduct = null;

    foreach ($existing as $k => $item) {
        if (($item['id'] ?? '') === $id) {
            if (isset($body['inStock'])) {
                $existing[$k]['inStock'] = (bool)$body['inStock'];
            }
            if (isset($body['stockCount'])) {
                $existing[$k]['stockCount'] = (int)$body['stockCount'];
                $existing[$k]['inStock'] = (int)$body['stockCount'] > 0;
            }
            if (isset($body['price'])) {
                $existing[$k]['price'] = (float)$body['price'];
            }
            $updatedProduct = $existing[$k];
            break;
        }
    }

    if ($updatedProduct) {
        writeJsonProducts($existing);
    }

    // 2. Update in MySQL
    if ($pdo !== null && (isset($body['inStock']) || isset($body['stockCount']))) {
        try {
            $inStock = isset($body['inStock']) ? ($body['inStock'] ? 1 : 0) : 1;
            $stockCount = isset($body['stockCount']) ? (int)$body['stockCount'] : 0;
            $stmt = $pdo->prepare("UPDATE products SET in_stock = :in_stock, stock_count = :stock_count WHERE id = :id");
            $stmt->execute([
                ':in_stock'    => $inStock,
                ':stock_count' => $stockCount,
                ':id'          => $id
            ]);
        } catch (Exception $e) {}
    }

    sendResponse(true, $updatedProduct ?: ['id' => $id], 'อัปเดตสต็อกสินค้าสำเร็จ');
}

function handleDelete($pdo) {
    global $JSON_FILE;
    $id = $_GET['id'] ?? null;
    if (!$id) {
        sendResponse(false, null, 'ต้องระบุ ID สินค้า', 400);
    }

    // 1. Remove from JSON File
    $existing = readJsonProducts($JSON_FILE);
    $filtered = array_filter($existing, function($p) use ($id) {
        return ($p['id'] ?? '') !== $id;
    });
    writeJsonProducts($filtered);

    // 2. Remove from MySQL
    if ($pdo !== null) {
        try {
            $stmt = $pdo->prepare("DELETE FROM products WHERE id = :id");
            $stmt->execute([':id' => $id]);
        } catch (Exception $e) {}
    }

    sendResponse(true, ['id' => $id], 'ลบสินค้าออกจากระบบสำเร็จ');
}

function insertOrUpdateProductMySQL($pdo, $p) {
    try {
        $sql = "INSERT INTO products (
            id, name, slug, description, price, compare_at_price, images_json,
            category_id, rating, reviews_count, sizes_json, is_new, is_recommended,
            is_pre_order, season_tag, stock_count, in_stock, department_slugs_json,
            merchant_email, video_url, created_at
        ) VALUES (
            :id, :name, :slug, :description, :price, :compare_at_price, :images_json,
            :category_id, :rating, :reviews_count, :sizes_json, :is_new, :is_recommended,
            :is_pre_order, :season_tag, :stock_count, :in_stock, :dept_json,
            :merchant_email, :video_url, NOW()
        ) ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            description = VALUES(description),
            price = VALUES(price),
            compare_at_price = VALUES(compare_at_price),
            images_json = VALUES(images_json),
            sizes_json = VALUES(sizes_json),
            stock_count = VALUES(stock_count),
            in_stock = VALUES(in_stock),
            department_slugs_json = VALUES(department_slugs_json),
            merchant_email = VALUES(merchant_email)";

        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':id'               => $p['id'],
            ':name'             => $p['name'],
            ':slug'             => $p['slug'] ?? ($p['id'] . '-' . rand(10, 99)),
            ':description'      => $p['description'] ?? '',
            ':price'            => (float)($p['price'] ?? 0),
            ':compare_at_price' => isset($p['compareAtPrice']) ? (float)$p['compareAtPrice'] : null,
            ':images_json'      => json_encode($p['images'] ?? [], JSON_UNESCAPED_UNICODE),
            ':category_id'      => $p['categoryId'] ?? 'cat_clothing',
            ':rating'           => (float)($p['rating'] ?? 5.0),
            ':reviews_count'    => (int)($p['reviewsCount'] ?? 0),
            ':sizes_json'       => json_encode($p['sizes'] ?? [], JSON_UNESCAPED_UNICODE),
            ':is_new'           => !empty($p['isNew']) ? 1 : 0,
            ':is_recommended'   => !empty($p['isRecommended']) ? 1 : 0,
            ':is_pre_order'     => !empty($p['isPreOrder']) ? 1 : 0,
            ':season_tag'       => $p['seasonTag'] ?? null,
            ':stock_count'      => isset($p['stockCount']) ? (int)$p['stockCount'] : 50,
            ':in_stock'         => !empty($p['inStock']) ? 1 : 0,
            ':dept_json'        => json_encode($p['departmentSlugs'] ?? [], JSON_UNESCAPED_UNICODE),
            ':merchant_email'   => $p['merchantEmail'] ?? 'admin@cmtc.ac.th',
            ':video_url'        => $p['videoUrl'] ?? null
        ]);
        return true;
    } catch (Exception $e) {
        return false;
    }
}

function formatProductRow($row) {
    return [
        'id'              => $row['id'],
        'name'            => $row['name'],
        'slug'            => $row['slug'],
        'description'     => $row['description'] ?? '',
        'price'           => (float)$row['price'],
        'compareAtPrice'  => $row['compare_at_price'] ? (float)$row['compare_at_price'] : null,
        'images'          => json_decode($row['images_json'] ?? '[]', true) ?: [],
        'categoryId'      => $row['category_id'],
        'rating'          => (float)($row['rating'] ?? 5.0),
        'reviewsCount'    => (int)($row['reviews_count'] ?? 0),
        'sizes'           => json_decode($row['sizes_json'] ?? '["S","M","L","XL"]', true) ?: ['S', 'M', 'L', 'XL'],
        'isNew'           => (bool)$row['is_new'],
        'isRecommended'   => (bool)$row['is_recommended'],
        'isPreOrder'      => (bool)$row['is_pre_order'],
        'seasonTag'       => $row['season_tag'],
        'stockCount'      => (int)($row['stock_count'] ?? 0),
        'inStock'         => (bool)$row['in_stock'],
        'departmentSlugs' => json_decode($row['department_slugs_json'] ?? '[]', true) ?: [],
        'merchantEmail'   => $row['merchant_email'],
        'videoUrl'        => $row['video_url']
    ];
}