<?php

require '../../config.php';
require '../admin_config.php';
require '../functions.php';

$connect = connect($database);

// Get all stores with coupon count and categories
// Need to check if the category_id is a parent category or subcategory
$sql = "SELECT s.*, 
        COUNT(DISTINCT c.coupon_id) as coupon_count,
        GROUP_CONCAT(DISTINCT 
            CASE 
                -- Check if this ID exists in subcategories table
                WHEN EXISTS (SELECT 1 FROM subcategories sub WHERE sub.subcategory_id = sc.category_id) THEN
                    (SELECT CONCAT(parent_cat.category_title, ' > ', sub.subcategory_title)
                     FROM subcategories sub
                     LEFT JOIN categories parent_cat ON sub.subcategory_parent = parent_cat.category_id
                     WHERE sub.subcategory_id = sc.category_id)
                -- Otherwise it's a parent category
                ELSE cat.category_title
            END 
            ORDER BY sc.category_id SEPARATOR ' | '
        ) as categories
        FROM stores s 
        LEFT JOIN coupons c ON s.store_id = c.coupon_store AND c.coupon_status = 1
        LEFT JOIN store_categories sc ON s.store_id = sc.store_id
        LEFT JOIN categories cat ON sc.category_id = cat.category_id
        GROUP BY s.store_id 
        ORDER BY s.store_id DESC";
$sentence = $connect->prepare($sql); 
$sentence->execute();
$data = $sentence->fetchAll();

$results = array(
    "sEcho" => 1,
    "iTotalRecords" => count($data),
    "iTotalDisplayRecords" => count($data),
    "aaData"=>$data);
echo json_encode($results);

?>
