import json

async def main(request, params_kw):
    """
    查询某二级分销商在某产品上的分销协议折扣。
    
    参数: sub_distributor_id, productid, prodtypeid(可选)
    
    折扣查找优先级:
    1. 精确匹配 productid
    2. 匹配 prodtypeid  
    3. 使用协议默认折扣
    """
    user_orgid = await get_userorgid()
    dbname = get_module_dbname('supplychain')
    
    sub_distributor_id = params_kw.get("sub_distributor_id")
    productid = params_kw.get("productid")
    prodtypeid = params_kw.get("prodtypeid")
    
    if not sub_distributor_id or not productid:
        return json.dumps({"status": "error", "message": "缺少sub_distributor_id或productid参数"})
    
    config = getConfig(".")
    DBPools(config.databases)
    
    async with db.sqlorContext(dbname) as sor:
        # Try exact product match
        sql = """SELECT dai.id, dai.agreement_id, dai.discount, dai.settlement_price,
                        da.agreement_code, da.agreement_name
                 FROM distribution_agreement_items dai
                 JOIN distribution_agreements da ON dai.agreement_id = da.id
                 WHERE dai.resellerid = ${resellerid}$
                   AND da.sub_distributor_id = ${sub_distributor_id}$
                   AND da.status = '1'
                   AND da.start_date <= CURDATE()
                   AND (da.end_date IS NULL OR da.end_date >= CURDATE())
                   AND dai.productid = ${productid}$
                 ORDER BY da.start_date DESC"""
        recs = await sor.sqlExe(sql, {"resellerid": user_orgid, 
                                       "sub_distributor_id": sub_distributor_id,
                                       "productid": productid})
        
        if not recs and prodtypeid:
            sql = """SELECT dai.id, dai.agreement_id, dai.discount, dai.settlement_price,
                            da.agreement_code, da.agreement_name
                     FROM distribution_agreement_items dai
                     JOIN distribution_agreements da ON dai.agreement_id = da.id
                     WHERE dai.resellerid = ${resellerid}$
                       AND da.sub_distributor_id = ${sub_distributor_id}$
                       AND da.status = '1'
                       AND da.start_date <= CURDATE()
                       AND (da.end_date IS NULL OR da.end_date >= CURDATE())
                       AND dai.prodtypeid = ${prodtypeid}$
                     ORDER BY da.start_date DESC"""
            recs = await sor.sqlExe(sql, {"resellerid": user_orgid,
                                           "sub_distributor_id": sub_distributor_id,
                                           "prodtypeid": prodtypeid})
        
        if not recs:
            sql = """SELECT id as agreement_id, agreement_code, agreement_name,
                            default_discount as discount, NULL as settlement_price
                     FROM distribution_agreements
                     WHERE resellerid = ${resellerid}$
                       AND sub_distributor_id = ${sub_distributor_id}$
                       AND status = '1'
                       AND start_date <= CURDATE()
                       AND (end_date IS NULL OR end_date >= CURDATE())
                     ORDER BY start_date DESC"""
            recs = await sor.sqlExe(sql, {"resellerid": user_orgid,
                                           "sub_distributor_id": sub_distributor_id})
        
        result = [dict(r) for r in recs] if recs else []
        return json.dumps({"status": "ok", "data": result})