DROP FUNCTION IF EXISTS rlarp.get_option_costs_priceg;
CREATE FUNCTION rlarp.get_option_costs_priceg(_priceg text, _majg text)
RETURNS TABLE (
    stlc        text 
    ,coltier    text
    ,branding   text
    ,accs_ps    text
    ,acc_list   text
    ,suffix     text
    ,uomp       text
    ,avgsc      numeric
    ,avgfc      numeric
    ,weight     numeric
    ,target     numeric
    ,futmargin  numeric
)
LANGUAGE plpgsql AS 
$function$

BEGIN

RETURN QUERY
WITH
sel AS (
SELECT 
    i.stlc
    ,i.coltier
    ,i.branding
    --,accs
    ,i.accs_ps
    ,i.acc_list
    ,i.suffix
    --,COALESCE(accs_ps,accs) accs_c
    --,jsonb_agg(DISTINCT r.aomult) cavitation
    --,jsonb_agg(DISTINCT r.aoctme) cyclet
    --,jsonb_arr_aggcd(mino) min_ord
    ,i.uomp
    --,jsonb_agg(DISTINCT aplnt) aplnt
    --,jsonb_agg(DISTINCT colc) colc
    --,jsonb_agg(DISTINCT substring(item,12,case when branding = '' THEN 7 ELSE 4 end)) items
    --,jsonb_agg(DISTINCT majg) majgs
    --,jsonb_agg(DISTINCT assc) accs
    ,round(avg(curstdus),5) avgsc
    ,round(avg(futstdus),5) avgfc
    ,round(avg(nwht),5) avgwt
    ,i.v1ds
FROM 
    "CMS.CUSLG".itemm i
WHERE 
    true
    --stlc ~ 'TWA10200'
    --AND branding = ''
    AND aplnt <> 'I'
    AND COALESCE(i.uomp,'') <> ''
    --AND branding = ''
GROUP BY    
    i.stlc
    ,i.coltier
    ,i.uomp
    ,i.branding
    ,i.acc_list
    ,i.accs_ps
    ,i.suffix
    ,i.v1ds
ORDER BY
    i.stlc ASC
)
SELECT 
     sel.stlc
    ,sel.coltier
    ,sel.branding
    ,sel.accs_ps
    ,sel.acc_list
    ,sel.suffix
    ,sel.uomp
    ,sel.avgsc
    ,sel.avgfc
    ,sel.avgwt
    ,t.price
    ,round(CASE WHEN coalesce(t.price,0) <> 0 THEN (t.price-COALESCE(sel.avgfc,0))/t.price ELSE 0 END,3) futmarg
FROM 
    sel 
    LEFT OUTER JOIN pricequote.target_prices t ON 
        sel.stlc = t.stlc 
        AND sel.v1ds = t.ds
        AND t.chan = 'DIR' 
        AND COALESCE(t.tier,'') IN ('1')
        AND 24 <@ t.vol
    -- LEFT OUTER JOIN pricequote.market_setavgprice t ON
    --     t.mold = sel.stlc
    --     AND t.data_segment = sel.v1ds
    --     AND t.season = 2025
    --     AND t.chan = 'DIRECT'
    --     AND t.country = 'ALL'
    --     AND t.geo = 'ALL'
    --     AND t.region = 'ALL'
WHERE
    sel.stlc IN (SELECT DISTINCT m.stlc FROM rlarp.molds m WHERE m.priceg ~ _priceg AND m.majg ~ _majg);

END

$function$