<?php 

namespace app\models\market;

use \Repository;

/** Market_repository
 * (thematic-entity repository)
 * ---
 * SQL entity templates for Market
 * 
 * Every '_base' is a repository of common 'prepare' SQL-queries;
 * They usually include holders for attaching/binding data safely;
 * These queries are expensive in syntax but fast on run time and
 * are used to collect multi-joined amounts ot data.
 * 
 * Organize yous repositories in logical/thematic groups (partitioning).
 * This way your repository is managable and uses very few sources.
 * 
 * This class acts like a namespaced repository for geting your code readable
 * Avoid pushing very simple queries into repository classes. A record like...
 *     'CARS_by:Age' => "SELECT * FROM cars WHERE age = :Age"
 * ... won't make your code sexier nor easier to read; it will just increase
 * your label's entropy and make trickier to pick your next sensable label;
 * 
 * repository classes include two methods (inherited by the abstract, so
 * you do not need to implement something more than just construct
 * the $repository array)
 * ** pull: for pulling a query by a friendly name
 * ** echo: optional method used by some devolopment tools
 * 
 * TODO: development tool using echo is still in progress
 */
class Market_repository extends Repository
{
    //// use Repository;     // repository trait includes
    ////     // a method to pull an entity of the gathering
    ////     // and method to (TODO:) ...

    /** repository is an array of Prepare SWL queries;
     * each query is attached to the array via a friendly label;
     * 
     * NOTE: (PROPOSAL)
     * ---
     * about the label:
     * 1. keep names short descriptive but not too long
     * 2. main entity/entities shall be UPPERCASED
     * 3. determinands/adjectives shall be Cappitalized
     * 4. words are connected with underscore (_)
     * 5. if SQL inludes bind-holderd, label shall be suffixed with '_by:'
     * followed by holder names connected with undercores (case-sensitively)
     * 
     * about the data-bind holders (if present):
     * 1. all holders are prefixed with ':'
     * 2. holder name shall begin with a letter and contain one word.
     * 
     * example:
     * 'Active_EMPLOYEES_by:Age_CityID' => "SELECT Em.*, c.*
     * FROM employee Em LEFT JOIN city c ON c.id = Em.city_id
     * WHERE c.id = :CityID AND Em.age = :Age"
     * 
     * It is recommended to leave a descriptive comment befor each declaration
     * and you may also include sql-comments inside the query.
     */
    public static $repository = [

        // PRODUCTS by: storeID, ProductUrl
        // picks all product properties
        // + default image + price for certain store
        'Active_PRODUCT_by:storeID_ProductUrl' =>
            "SELECT p.*,
                pc.ID AS ProductCategory,
                pc.Hierarchy,
                pc.FullFriendlyUrl AS `Path`,
                prices.ProductPrice_OriginalPrice AS Price,
                prices.ProductPrice_DiscountedPrice AS DiscountPrice,
                prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
                prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice,
                b.Title BrandName
            FROM products p
            LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
            LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
            LEFT JOIN prices ON p.SKU = prices.SKU
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets ON assets.ID = p2i.ImageID
            LEFT JOIN brands b ON b.ID = p.BrandID
            WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
                AND p2i.Order = 1
                AND p.IsActive = 1
                AND p.Published = 1
                AND prices.PriceListID = :storeID
            AND p.FriendlyUrl = :ProductUrl"
        ,

        // PRODUCTS by: storeID, ProductID
        // picks all product properties
        // + default image + price for certain store
        'Active_PRODUCT_by:storeID_ProductID' =>
            "SELECT p.*,
                pc.ID AS ProductCategory,
                pc.Hierarchy,
                pc.FullFriendlyUrl AS `Path`,
                prices.ProductPrice_OriginalPrice AS Price,
                prices.ProductPrice_DiscountedPrice AS DiscountPrice,
                prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
                prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice,
                b.Title BrandName
            FROM products p
            LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
            LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
            LEFT JOIN prices ON p.SKU = prices.SKU
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets ON assets.ID = p2i.ImageID
            LEFT JOIN brands b ON b.ID = p.BrandID
            WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
                AND p2i.Order = 1
                AND p.IsActive = 1
                AND p.Published = 1
                AND prices.PriceListID = :storeID
            AND p.ID = :ProductID"
        ,

        // CATEGORY-PRODUCTS by: storeID, likeHierarchy
        // traverses all products from category and subcategories
        // picke price and default image
        'CATEGORY-PRODUCTS_by:storeID_likeHierarchy' =>
            "SELECT p.*,
                assets.Url AS ImageUrl,
                pc.FullFriendlyUrl AS `Path`,
                prices.ProductPrice_OriginalPrice AS Price,
                prices.ProductPrice_DiscountedPrice AS DiscountPrice,
                prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
                prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice
            FROM products p
            LEFT JOIN prices ON p.SKU = prices.SKU
            LEFT JOIN products_to_product_categories p2pc ON p2pc.SimpleProductID = p.ID
            LEFT JOIN product_categories pc ON pc.ID = p2pc.ProductCategoryID
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets ON assets.ID = p2i.ImageID
            WHERE p2pc.ProductCategoryID IN (
                SELECT `ID` FROM product_categories
                WHERE Hierarchy LIKE :likeHierarchy
            )
            AND p2i.Order = 1
            AND p.IsActive = 1
            AND p.Published = 1
            AND prices.PriceListID = :storeID"
        ,

        // Count Products
        // of all last-lever Product categories
        // by StoreID
        // ---
        // used on creating product_categories tree
        // ProductCategories_model::tree()
        'Count_Last_Level_PRODUCTS_by:storeID' =>
            "SELECT pc.ID, COUNT(p.ID) as `Counter`
            FROM product_categories pc
            LEFT JOIN products_to_product_categories p2pc ON pc.ID = p2pc.ProductCategoryID
            LEFT JOIN products p ON p2pc.SimpleProductID = p.ID
            LEFT JOIN prices ON p.SKU = prices.SKU
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets ON assets.ID = p2i.ImageID
            WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
                AND p2i.Order = 1
                AND pc.Level = 2
                AND p.IsActive = 1
                AND p.Published = 1
                AND prices.PriceListID = :storeID
            GROUP BY pc.ID"
        ,

        // PRODUCT by: ProductID
        // Join images array (json string)
        // Join prices for all stores (json string)
        'Active_PRODUCT_complete_by:ProductID' =>
            "SELECT p.*, pc.ID AS ProductCategory,
            pc.Hierarchy, pc.FullFriendlyUrl AS `Path`,
            (  -- get all store prices as Json
                SELECT JSON_ARRAYAGG(JSON_OBJECT(
                    'Store', prices.PriceListID,
                    'Price', prices.ProductPrice_OriginalPrice,
                    'DiscountPrice', prices.ProductPrice_DiscountedPrice,
                    'PerUnitPrice', prices.UnitMeasurePrice_OriginalPrice,
                    'PerUnitDiscountPrice', prices.UnitMeasurePrice_DiscountedPrice
                ))
                FROM prices
                WHERE prices.SKU = p.SKU
            ) as pricesJson,
            (   -- get all images as Json
                SELECT JSON_ARRAYAGG(JSON_OBJECT(
                    'Order', p2i.Order,
                    'Url', assets.Url,
                    'Title', assets.Title,
                    'Description', assets.Description
                ))
                FROM products_to_images p2i
                LEFT JOIN assets ON assets.ID = p2i.ImageID
                WHERE p2i.SimpleProductID = p.ID
            ) AS imagesJson,
            b.Title BrandName
            FROM products p
            LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
            LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets ON assets.ID = p2i.ImageID
            LEFT JOIN brands b ON b.ID = p.BrandID
            WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
                AND p2i.Order = 1
                AND p.IsActive = 1
                AND p.Published = 1
            AND p.ID = :ProductID"
        ,

        // Product images by: ProductID
        // (sorted by 'order' field)
        'PRODUCT_IMAGES_by:productID' =>
            "SELECT a.Url, a.Title, a.Description
            FROM products p
            LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
            LEFT JOIN assets a ON a.ID = p2i.ImageID
            WHERE p.IsActive = 1
                AND p.Published = 1
                AND p.ID = :productID
            ORDER BY p2i.Order"

    ];

    /* no need to impement anything else;
     * you can overide if needed the default methods:
     * + public static function pull($entity){ }
     * + public static function echo($content =false){ }
     */

}