<?php
require_once '../config/database.php';
require_once '../config/auth.php';

//verifyAppRequest();

$method = $_SERVER['REQUEST_METHOD'];
$action = $_GET['action'] ?? '';

switch ($action) {
    case 'list':        getProducts();      break;
    case 'detail':      getProductDetail(); break;
    case 'search':      searchProducts();   break;
    case 'featured':    getFeatured();      break;
    case 'by_category': getByCategory();    break;
    default:            sendError('Invalid action');
}

// ─── Get All Products (with filters) ─────────────────────────────────────────
// ─── Get Single Product Detail (FIXED) ────────────────────────────────────────


// ─── Get Single Product Detail (FIXED & MATCHED TO FLUTTER) ─────────────────────
function getProductDetail() {
    $db = getDB();
    $id = intval($_GET['id'] ?? 0);
    if (!$id) sendError('Product ID required');

    // 1. Query product schema matching database structure definitions
    $sql = "SELECT p.*, c.name_en as cat_name_en, c.name_bn as cat_name_bn,
                   COALESCE(AVG(r.rating), 0) as avg_rating,
                   COUNT(r.id) as review_count
            FROM products p
            LEFT JOIN categories c ON c.id = p.category_id
            LEFT JOIN reviews r ON r.product_id = p.id AND r.is_approved = 1
            WHERE p.id = $id AND p.is_active = 1
            GROUP BY p.id";

    $res = $db->query($sql);
    if (!$res || $res->num_rows === 0) sendError('Product not found', 404);

    $productData = $res->fetch_assoc();
    $product = formatProduct($productData);

    // 2. Map structural gallery images directly from the formatProduct response
    $gallery = $product['images'] ?? [];

    // 3. Fetch product variations array list matching schema constraints
    $variationQuery = $db->query("SELECT variation_type, variation_value FROM product_variations WHERE product_id = $id");
    $variations = [];
    if ($variationQuery) {
        while ($v = $variationQuery->fetch_assoc()) {
            $variations[] = [
                "type"  => $v['variation_type'] ?? '',
                "value" => $v['variation_value'] ?? ''
            ];
        }
    }

    // 4. FIXED QUERY: Targets 'reviews' table to match database definitions perfectly
    $revRes = $db->query("SELECT rating, comment, created_at FROM reviews 
                           WHERE product_id = $id AND is_approved = 1 
                           ORDER BY id DESC LIMIT 20");
    $reviews = [];
    if ($revRes) {
        while ($r = $revRes->fetch_assoc()) {
            $reviews[] = [
                "name"       => 'Customer',
                "comment"    => $r['comment'] ?? '',
                "rating"     => intval($r['rating'] ?? 5),
                "created_at" => $r['created_at'] ?? ''
            ];
        }
    }

    // Inject parameters inside product dictionary key map object for Dart parsing structure loops
    $product['gallery']    = $gallery;
    $product['variations'] = $variations;
    $product['reviews']    = $reviews;

    // 5. Send unified payload conforming cleanly to payload constraints
    sendSuccess([
        'product'          => $product,
        'related_products' => getRelatedProducts($db, intval($product['category_id']), $id),
        'categories'       => getAllCategoriesForDetail($db),
        'reviews'          => $reviews
    ]);
}

// Global helper context for related items extraction mapping arrays
function getRelatedProducts($db, $catId, $currentId) {
    $relRes = $db->query("SELECT p.*, c.name_en as cat_name_en, c.name_bn as cat_name_bn,
                                 COALESCE(AVG(r.rating),0) as avg_rating, COUNT(r.id) as review_count
                          FROM products p
                          LEFT JOIN categories c ON c.id = p.category_id
                          LEFT JOIN reviews r ON r.product_id = p.id AND r.is_approved = 1
                          WHERE p.category_id = $catId AND p.id != $currentId AND p.is_active = 1
                          GROUP BY p.id LIMIT 8");
    $related = [];
    if ($relRes) {
        while ($row = $relRes->fetch_assoc()) {
            $related[] = formatProduct($row);
        }
    }
    return $related;
}




// Helper to pull clean categories map tree structures
function getAllCategoriesForDetail($db) {

    $catRes = $db->query("
        SELECT id,name_en,name_bn,image
        FROM categories
        WHERE is_active=1
        ORDER BY sort_order ASC
    ");

    $allCats = [];

    if($catRes){

        while($c = $catRes->fetch_assoc()){

            $allCats[] = [

                'id' => intval($c['id']),
                'name_en' => $c['name_en'],
                'name_bn' => $c['name_bn'],
                'image' => $c['image']
                    ? BASE_URL.'uploads/categories/'.$c['image']
                    : null,
            ];
        }
    }

    return $allCats;
}

function searchProducts() {

    $db = getDB();

    $q = trim($_GET['q'] ?? '');

    if(empty($q)){
        $q = trim($_GET['search'] ?? '');
    }

    if(empty($q)){

        echo json_encode([
            "success" => true,
            "products" => []
        ]);
        exit;
    }

    $q = $db->real_escape_string($q);

    $sql = "

    SELECT

        p.*,

        c.name_en AS cat_name_en,
        c.name_bn AS cat_name_bn,

        COALESCE(AVG(r.rating),0) AS avg_rating,
        COUNT(r.id) AS review_count

    FROM products p

    LEFT JOIN categories c
    ON c.id = p.category_id

    LEFT JOIN reviews r
    ON r.product_id = p.id

    WHERE p.is_active = 1

    AND (

        LOWER(p.name_en)
        LIKE CONCAT('%', LOWER('$q'), '%')

        OR LOWER(p.name_bn)
        LIKE CONCAT('%', LOWER('$q'), '%')

        OR LOWER(p.description_en)
        LIKE CONCAT('%', LOWER('$q'), '%')

        OR LOWER(p.description_bn)
        LIKE CONCAT('%', LOWER('$q'), '%')

        OR LOWER(c.name_en)
        LIKE CONCAT('%', LOWER('$q'), '%')

        OR LOWER(c.name_bn)
        LIKE CONCAT('%', LOWER('$q'), '%')

    )

    GROUP BY p.id

    ORDER BY
    p.is_featured DESC,
    p.created_at DESC

    LIMIT 50
    ";

    $res = $db->query($sql);

    $products = [];

    if($res){

        while($row = $res->fetch_assoc()){

            $products[] =
            formatProduct($row);
        }
    }

    echo json_encode([

        "success" => true,

        "products" => $products
    ]);

    exit;
}

// ─── Get Featured / Homepage Products ────────────────────────────────────────
function getFeatured() {
    $db  = getDB();
    $res = $db->query("SELECT p.*, c.name_en as cat_name_en, c.name_bn as cat_name_bn,
                               COALESCE(AVG(r.rating),0) as avg_rating, COUNT(r.id) as review_count
                        FROM products p
                        LEFT JOIN categories c ON c.id = p.category_id
                        LEFT JOIN reviews r ON r.product_id = p.id AND r.is_approved = 1
                        WHERE p.is_active = 1
                        GROUP BY p.id
                        ORDER BY p.is_featured DESC, p.created_at DESC
                        LIMIT 20");
    $products = [];
    while ($row = $res->fetch_assoc()) $products[] = formatProduct($row);
    sendSuccess(['products' => $products]);
}

// ─── Get Products by Category ─────────────────────────────────────────────────
function getByCategory() {
    getProducts(); // same logic with category filter
}

// ─── Format Helper ────────────────────────────────────────────────────────────
function formatProduct($row) {
    $fullImages = [];
    
    // Fallback protection for plain text vs string arrays
    if (!empty($row['images'])) {
        $images = json_decode($row['images'], true);
        
        if (is_array($images)) {
            // Valid JSON Array Format: ["burger.jpg"]
            foreach ($images as $img) {
                $cleanImg = str_replace('uploads/products/', '', $img);
                $fullImages[] = BASE_URL . 'uploads/products/' . $cleanImg;
            }
        } else {
            // Plain String Fallback Format: burger.jpg
            $cleanImg = str_replace('uploads/products/', '', $row['images']);
            $fullImages[] = BASE_URL . 'uploads/products/' . $cleanImg;
        }
    }

    return [
        'id'               => intval($row['id']),
        'category_id'      => intval($row['category_id'] ?? 0),
        'category_name_en' => $row['cat_name_en'] ?? '',
        'category_name_bn' => $row['cat_name_bn'] ?? '',
        'name_en'          => $row['name_en'] ?? '',
        'name_bn'          => $row['name_bn'] ?? '',
        'description_en'   => $row['description_en'] ?? '',
        'description_bn'   => $row['description_bn'] ?? '',
        'price'            => floatval($row['price'] ?? 0),
        'old_price'        => !empty($row['old_price']) ? floatval($row['old_price']) : null,
        'stock'            => intval($row['stock'] ?? 0),
        'images'           => $fullImages,
        'avg_rating'       => round(floatval($row['avg_rating'] ?? 4.9), 1),
        'review_count'     => intval($row['review_count'] ?? 0),
        'is_featured'      => (bool)($row['is_featured'] ?? false),
        'is_new'           => isset($row['created_at']) && strtotime($row['created_at']) > strtotime('-4 weeks'),
    ];
}