<commit_before><?php


namespace model\database;

use model\database\Connect\Connection;
use model\Product;
use PDO;
use PDOException;


class ProductsDao
{

    //Make Singletonn
    private static $instance;
    private $pdo;

    //Statements defined as constants

    const GET_PRODUCT_BY_ID = "SELECT p.id, i.image_url, p.title, p.description, p.price, p.subcategory_id, 
                               p.visible, p.quantity, MAX(pr.percent) percent, AVG(r.rating) average 
                               FROM products p JOIN images i ON p.id = i.product_id 
                               LEFT JOIN reviews r ON p.id = r.product_id
							   LEFT JOIN promotions pr ON p.id = pr.product_id 
                               WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                               GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL AND p.id = ?";

    const GET_PRODUCT_BY_ID_ADMIN = "SELECT p.id, i.image_url, p.title, p.description, p.price, p.subcategory_id, 
                                     p.visible, p.quantity, MAX(pr.percent) percent, AVG(r.rating) average 
                                     FROM products p JOIN images i ON p.id = i.product_id 
                                     LEFT JOIN reviews r ON p.id = r.product_id
								     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.id = ?";

    const GET_MOST_RATED_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                     p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                     p.price, MAX(pr.percent) percent, 
                                     IF(MAX(pr.percent) IS NOT NULL, 
                                     p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                     FROM products p JOIN images i ON p.id = i.product_id
                                     LEFT JOIN reviews r ON p.id = r.product_id
                                     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL
                                     ORDER BY average DESC, reviewsCount DESC LIMIT 4";

    const GET_RELATED_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                     p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                     p.price, MAX(pr.percent) percent, 
                                     IF(MAX(pr.percent) IS NOT NULL, 
                                     p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                     FROM products p JOIN images i ON p.id = i.product_id
                                     LEFT JOIN reviews r ON p.id = r.product_id
                                     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.visible = 1 AND P.subcategory_id IS NOT NULL
                                     AND NOT p.id = ? AND p.subcategory_id = ?
                                     ORDER BY average DESC, reviewsCount DESC LIMIT 4";

    const GET_MOST_RECENT_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                      p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                      p.price, MAX(pr.percent) percent, IF(MAX(pr.percent) IS NOT NULL, 
                                      p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                      FROM products p JOIN images i ON p.id = i.product_id
                                      LEFT JOIN reviews r ON p.id = r.product_id
                                      LEFT JOIN promotions pr ON p.id = pr.product_id 
                                      WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL 
                                      GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL
                                      ORDER BY p.created_at DESC, average DESC, reviewsCount DESC 
                                      LIMIT 4";

    const GET_MOST_SOLD = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                           p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                           p.price, MAX(pr.percent) percent, 
                           IF(MAX(pr.percent) IS NOT NULL, 
                           p.price - MAX(pr.percent)/100*p.price, p.price) price_fin,
                           SUM(DISTINCT op.quantity) sold, o.status<commit_msg>fixed SQL for related products<commit_after><?php


namespace model\database;

use model\database\Connect\Connection;
use model\Product;
use PDO;
use PDOException;


class ProductsDao
{

    //Make Singletonn
    private static $instance;
    private $pdo;

    //Statements defined as constants

    const GET_PRODUCT_BY_ID = "SELECT p.id, i.image_url, p.title, p.description, p.price, p.subcategory_id, 
                               p.visible, p.quantity, MAX(pr.percent) percent, AVG(r.rating) average 
                               FROM products p JOIN images i ON p.id = i.product_id 
                               LEFT JOIN reviews r ON p.id = r.product_id
							   LEFT JOIN promotions pr ON p.id = pr.product_id 
                               WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                               GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL AND p.id = ?";

    const GET_PRODUCT_BY_ID_ADMIN = "SELECT p.id, i.image_url, p.title, p.description, p.price, p.subcategory_id, 
                                     p.visible, p.quantity, MAX(pr.percent) percent, AVG(r.rating) average 
                                     FROM products p JOIN images i ON p.id = i.product_id 
                                     LEFT JOIN reviews r ON p.id = r.product_id
								     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.id = ?";

    const GET_MOST_RATED_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                     p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                     p.price, MAX(pr.percent) percent, 
                                     IF(MAX(pr.percent) IS NOT NULL, 
                                     p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                     FROM products p JOIN images i ON p.id = i.product_id
                                     LEFT JOIN reviews r ON p.id = r.product_id
                                     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL
                                     ORDER BY average DESC, reviewsCount DESC LIMIT 4";

    const GET_RELATED_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                     p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                     p.price, MAX(pr.percent) percent, 
                                     IF(MAX(pr.percent) IS NOT NULL, 
                                     p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                     FROM products p JOIN images i ON p.id = i.product_id
                                     LEFT JOIN reviews r ON p.id = r.product_id
                                     LEFT JOIN promotions pr ON p.id = pr.product_id 
                                     WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL
                                     GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL
                                     AND NOT p.id = ? AND p.subcategory_id = ?
                                     ORDER BY average DESC, reviewsCount DESC LIMIT 4";

    const GET_MOST_RECENT_PRODUCTS = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                                      p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                                      p.price, MAX(pr.percent) percent, IF(MAX(pr.percent) IS NOT NULL, 
                                      p.price - MAX(pr.percent)/100*p.price, p.price) price_fin 
                                      FROM products p JOIN images i ON p.id = i.product_id
                                      LEFT JOIN reviews r ON p.id = r.product_id
                                      LEFT JOIN promotions pr ON p.id = pr.product_id 
                                      WHERE pr.start_date <= NOW() AND pr.end_date >= NOW() OR pr.id IS NULL 
                                      GROUP BY p.id HAVING p.visible = 1 AND p.subcategory_id IS NOT NULL
                                      ORDER BY p.created_at DESC, average DESC, reviewsCount DESC 
                                      LIMIT 4";

    const GET_MOST_SOLD = "SELECT p.id, MIN(i.image_url) image_url, p.title, p.subcategory_id, 
                           p.visible, COUNT(DISTINCT r.id) reviewsCount, AVG(r.rating) average, 
                           p.price, MAX(pr.percent) percent, 
                           IF(MAX(pr.percent) IS NOT NULL, 
                           p.price - MAX(pr.percent)/100*p.price, p.price) price_fin,
                           SUM(DISTINCT op.quantity) sold, o.status
{'fraction_non_alphanumeric': 0.07736195725751861, 'fraction_numerical': 0.0036746929697321342, 'mean_word_length': 1.19902190091431, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 98, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3372573', 'n_tokens_mistral': 3049, 'n_tokens_neox': 2708, 'n_words': 813}