-- INSERT new vendor data

INSERT INTO gpi_product_flatfile_line(
    product_tmpl_id, vendor_price, vendor_mx_price, vendor_quantity
    , vendor_support, product_price, product_mx_price, product_us_price, state, price_currency, set_price
    , create_uid, write_uid, create_date, write_date
)
SELECT
    psi.product_tmpl_id
    , round((max(psi.price)*1.2), 2) AS vendor_price
    , round((max(psi.price)*1.2), 2) AS vendor_mx_price -- must update module gpi before use this field
    , sum(psi.vendor_qty_avbl) AS vendor_quantity
    -- , psi.vendor_qty_avbl
    , True AS vendor_support
    , 0.0 AS product_price
    , 'done' AS state
    , 'USD' AS price_currency, 'auto' AS set_price
    , 1 AS create_uid, 1 AS write_uid
    , current_date + time '09:00:00' AS create_date
    , current_date + time '09:00:00' AS write_date
    -- count(psi.product_tmpl_id)
FROM
    product_supplierinfo psi
    LEFT JOIN gpi_product_flatfile_line tmp
        ON psi.product_tmpl_id = tmp.product_tmpl_id
WHERE
    psi.company_id = 1 AND
    psi.name IN (13832,13860) AND
    tmp.product_tmpl_id is NULL
GROUP BY psi.product_tmpl_id
ORDER BY psi.product_tmpl_id;
-- LIMIT 10;


-- UPDATE ODOO MX STOCK and PRICE

UPDATE gpi_product_flatfile_line SET
    product_mx_qty = res.qty,
    vendor_mx_price = res.price_final
FROM(
 WITH t AS (
        SELECT
            qt.company_id, qt.product_id, pp.product_tmpl_id, sum (qt.qty) qty
            ,round(avg(qt.cost) * (1 + pc.cyber_per/100.0)) as price_final -- pc.cyber_per se cambia por pc.cyber_per_mx

        FROM
            product_product pp
            INNER JOIN stock_quant qt ON pp.id = qt.product_id
            INNER JOIN gpi_flatfile_product_locatoin_rel loc
                ON loc.location_id = qt.location_id
            left join product_template pt on pp.product_tmpl_id = pt.id
            LEFT JOIN product_category pc on pt.categ_id = pc.id
        WHERE
            qt.company_id = 6 AND pp.active = True
        GROUP BY qt.company_id, qt.product_id, pp.product_tmpl_id, pc.cyber_per
        ORDER BY pp.product_tmpl_id
    )
    SELECT
        f.product_tmpl_id pt_id
        , t.company_id, t.product_id
        , f.product_mx_qty f_mx_qty
        , t.price_final as vendor_mx_price
        , CASE WHEN t.qty is NULL THEN 0 ELSE t.qty END AS qty
    FROM
        gpi_product_flatfile_line f
        LEFT JOIN t ON f.product_tmpl_id = t.product_tmpl_id
) res
WHERE product_tmpl_id = res.pt_id;
    -- AND product_mx_qty <> res.qty;


-- UPDATE ODOO US STOCK

UPDATE gpi_product_flatfile_line SET
    product_us_qty = res.qty
FROM(
    WITH t AS (
        SELECT
            qt.company_id, qt.product_id, pp.product_tmpl_id, sum (qt.qty) qty
        FROM
            product_product pp
            INNER JOIN stock_quant qt ON pp.id = qt.product_id
            INNER JOIN gpi_flatfile_product_locatoin_rel loc
                ON loc.location_id = qt.location_id
        WHERE
            qt.company_id = 1 AND pp.active = True
        GROUP BY qt.company_id, qt.product_id, pp.product_tmpl_id
        ORDER BY pp.product_tmpl_id
    )
    SELECT
        f.product_tmpl_id pt_id
        , t.company_id, t.product_id
        , f.product_us_qty f_us_qty
        , CASE WHEN t.qty is NULL THEN 0 ELSE t.qty END AS qty
    FROM
        gpi_product_flatfile_line f
        LEFT JOIN t ON f.product_tmpl_id = t.product_tmpl_id
) res
WHERE product_tmpl_id = res.pt_id;
    -- AND product_us_qty <> res.qty;


-- UPDATE VENDOR QTY

UPDATE gpi_product_flatfile_line SET
    vendor_price = res.psi_price_final
    , vendor_quantity = res.psi_quantity
    , write_date = current_date + time '09:00:00'
    , write_uid = 1
FROM(
    SELECT
        psi.product_tmpl_id pt_id
        , max(psi.price) as psi_price
        , round((max(psi.price)+(max(psi.price)*pc.cyber_per/100)), 2) as psi_price_final
        , sum(psi.vendor_qty_avbl) psi_quantity
        -- count(psi.product_tmpl_id)
    FROM
        product_supplierinfo psi
        LEFT JOIN product_template pt
            ON psi.product_tmpl_id = pt.id
        LEFT JOIN product_category pc
            ON pt.categ_id = pc.id
        INNER JOIN gpi_product_flatfile_line tmp
            ON psi.product_tmpl_id = tmp.product_tmpl_id
    WHERE
        psi.company_id = 1 AND
        psi.name IN (13832,13860)
        -- and tmp.product_tmpl_id is null
    GROUP BY psi.product_tmpl_id, pc.cyber_per
    ORDER BY psi.product_tmpl_id
    -- LIMIT 10
) res
WHERE
    product_tmpl_id = res.pt_id
    AND vendor_support = TRUE;
    -- AND (vendor_price <> res.psi_price_final OR vendor_quantity <> res.psi_quantity);



-- generate CSV file

COPY(
     SELECT
        tmp."Internal Code", tmp."Seller SKU", tmp."EAN", tmp."Product Name",
        tmp."Product Desc", tmp."Brand Name", tmp."USA Vendor Price",
        tmp."MEX Vendor Price", tmp."USA CURRENCY", tmp."MEX CURRENCY",
        tmp."USA Quantity", tmp."MEX Quantity"
    FROM (
        SELECT
            row_number() OVER(PARTITION BY pt.default_code ORDER BY pt.default_code) row_num
            , pt.default_code AS "Internal Code"
            , pt.seller_sku AS "Seller SKU"
            , pt.etilize_ean AS "EAN"
            , pt.name AS "Product Name"
            , pt.description_sale AS "Product Desc"
            , pt.brand_name AS "Brand Name"
            , CASE
                WHEN l.set_price = 'auto' THEN l.vendor_price
                ELSE l.product_price
                END AS "USA Vendor Price"
            , l.vendor_mx_price as "MEX Vendor Price" -- pendiente actualizar el modulo gpi para que este campo exista
            , 'USD' as "USA CURRENCY"
            , 'USD' as "MEX CURRENCY"
            , CASE
                WHEN l.vendor_support = True AND (l.product_us_qty + l.vendor_quantity) > 4
                    THEN (l.product_us_qty + l.vendor_quantity)
                WHEN l.vendor_support = False AND l.product_us_qty > 4
                    THEN l.product_us_qty
                ELSE 0
                END AS "USA Quantity"
            , CASE WHEN l.product_mx_qty > 0
                THEN product_mx_qty
                ELSE 0
                END AS "MEX Quantity"
        FROM
            gpi_product_flatfile_line l
            INNER JOIN product_template pt
                ON l.product_tmpl_id = pt.id
       WHERE
            --(pt.brand_name is not null and trim(pt.brand_name) != '') AND este filtro se elimino por peticion email
            l.state = 'done'
            AND (l.product_mx_qty > 0 OR (l.product_us_qty + l.vendor_quantity) > 4)
        -- GROUP BY 1,2,3,4,5,6,7
        -- LIMIT 10
    ) tmp
     WHERE
        tmp."row_num" = 1
        AND tmp."USA Vendor Price" > 0
     -- AND tmp."MEX Vendor Price" > 0 --pendiete por confirmar esta condición
        AND (tmp."USA Quantity" > 4 OR tmp."MEX Quantity" > 0)
) to '/odoo-september/auto_start/cyber_file/cyberpuerta.csv' DELIMITER ',' QUOTE '"' CSV HEADER;