<?php
/**
 * 2007-2023 PrestaShop
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Academic Free License (AFL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to http://www.prestashop.com for more information.
 *
 *  @author    PrestaShop SA <contact@prestashop.com>
 *  @copyright 2007-2023 PrestaShop SA
 *  @license   http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
 *  International Registered Trademark & Property of PrestaShop SA
 */
declare(strict_types=1);

namespace PrestaShop\Module\AgileMultisellerPro\Repository;

use Doctrine\ORM\EntityRepository;

if (!defined('_PS_VERSION_')) {
    exit;
}

class SellerInfoMembershipTypeRepository extends EntityRepository
{
    public static function getMembershipTypes($id_category, $id_lang, $activeOnly = true)
    {
        $sql = '
            SELECT  IFNULL(mt.id_membership_type,0) AS id_membership_type, mt.units,mt.date_add,mt.id_group,mt.listing_max,mt.product_images
                ,pl.name AS name,p.price AS price, p.id_product, mt.interval_membership
            FROM `' . _DB_PREFIX_ . 'category_product` cp
            LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
            LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . (_PS_VERSION_ < 1.5 ? '' : ' AND pl.id_shop=' . (int) \Configuration::get('PS_SHOP_DEFAULT')) . ')
            LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo_membership_type` mt ON p.id_product=mt.id_product
            WHERE cp.`id_category` = ' . (int) $id_category . ($activeOnly ? '
            AND p.`active` = 1 ' : '') . '
            AND p.id_product > 0
            ORDER BY p.price ASC
            LIMIT 0, 100
		';

        $results = \Db::getInstance()->ExecuteS($sql);

        $tax_display = \Group::getPriceDisplayMethod(\Group::getCurrent()->id);

        $usetax = ($tax_display == PS_TAX_INC and !\Tax::excludeTaxeOption());

        for ($idx = 0; $idx < count($results); ++$idx) {
            $results[$idx]['price'] = \Product::getPriceStatic($results[$idx]['id_product'], $usetax);
        }

        return $results;
    }

    public static function getMembershipProducts($id_lang)
    {
        $context = \Context::getContext();
        $idCategory = (int) \Configuration::get('AGILE_MEMBERSHIP_CID');

        $nbDaysNewProduct = \Configuration::get('PS_NB_DAYS_NEW_PRODUCT');
        if (!\Validate::isUnsignedInt($nbDaysNewProduct)) {
            $nbDaysNewProduct = 20;
        }

        $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity' . (\Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
                    product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ', pl.`description`, pl.`description_short`, pl.`available_now`,
                    pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
                    il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
                    DATEDIFF(product_shop.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00",
                    INTERVAL ' . (int) $nbDaysNewProduct . ' DAY)) > 0 AS new, product_shop.price AS orderprice
                FROM `' . _DB_PREFIX_ . 'category_product` cp
                LEFT JOIN `' . _DB_PREFIX_ . 'product` p
                    ON p.`id_product` = cp.`id_product`
                ' . \Shop::addSqlAssociation('product', 'p') .
                (\Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop
                ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . '
                ' . \Product::sqlStock('p', 0) . '
                LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
                    ON (product_shop.`id_category_default` = cl.`id_category`
                    AND cl.`id_lang` = ' . (int) $id_lang . \Shop::addSqlRestrictionOnLang('cl') . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
                    ON (p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = ' . (int) $id_lang . \Shop::addSqlRestrictionOnLang('pl') . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop
                    ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il
                    ON (image_shop.`id_image` = il.`id_image`
                    AND il.`id_lang` = ' . (int) $id_lang . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m
                    ON m.`id_manufacturer` = p.`id_manufacturer`
                WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
                    AND cp.`id_category` = ' . (int) $idCategory . '
                    AND product_shop.`active` = 1';

        $result = \Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql, true, false);

        if (!$result) {
            return [];
        }

        // Modify SQL result
        return \Product::getProductsProperties($id_lang, $result);
    }
}
