Here are some random thoughts on design to get everyone focused. Have a beer first.
To do least cost routing manipulation, the key component we're going to need is the destination of the call. We typically don't identify this in FreePBX, but I think we can make it work.
Find trunkid for the call and then identify the match pattern of the numbers dialed. If set up correctly with LCR in mind, this will tell us the provider and the call destination e.g. US, Europe, etc.
Then we would lookup trunkid in trunks
Lookup valid match_pattern(s) by seq in trunk_dialpatterns
Lookup trunkid,match_pattern in trunk-lcr (new table) to get to proper max_provider_rate and provider_rate_table
trunk_lcr table structure might look like this:
trunkid (ties back to trunks)
match_pattern (ties back to trunk_dialpatterns)
seq (so we can have more than one provider record for different types of calls based on different dial patterns)
max_provider_rate (what's the most expensive call permitted with this provider record entry)
provider_rate_table (name of (new) asterisk.provider rate table when there's a match on this record)
provider_rate_url (if we're looking up the provider rate on line instead of in a MySQL table)
* if provider_rate_table and provider_rate_url both are empty, max_provider_rate will be considered fixed (flat) call rate for this provider for this type of dial pattern
* always match 1NXXNXXXXXX and NXXNXXXXXX and pure XX. type entries LAST! so sort the match_patterns so that those with letters and 1+letters are always at the bottom. In this way, we can find more specific rules for ALASKA, HAWAII, etc first. This can be handled with seq field in trunk_lcr.
* don't know an easy way around 23 Canadian area codes??
http://www.allareacodes.com/canadian_area_codes.htm. Maybe we can handle it with a dial prefix in which case we also need the prefix from trunk_dialpatterns added to trunk_lcr. Canadians could use dial prefix for US calls.
* note here that people will need to be more specific with match patterns if dealing with AK, HI, CAN
* if you want this to work properly. then we can fall back to NXXNXXXXXX type entries last in the search.
* logic for call should be IF no trunkid match in trunk_lcr, then OK (we're not using LCR for this provider)
* IF max_provider_rate > call_max_rate then BYPASS (this provider is too expensive for this call)
* IF no rate table/lookup tables and max_provider_rate <= call_max_rate then OK (this provider qualifies for call but there is no rate database available for number being called)
** NOTE: for Anveo, you probably need to do a 7-digit query (1902132) and, if it fails, do a 4-digit query (1902) before returning OK because of no match on US phone numbers! Length of prefix varies depending upon country. In Afghanistan, for example, you'd need to test 6 digits, then 4, then 2!
* IF no match found in rate table/lookup table and max_provider_rate is <= call_max_rate then OK (this provider qualifies for call but there is no rate info in existing database for the number being called)
* IF match found in rate table/lookup table and rate is <= max_provider_rate then OK else BYPASS (found a match on the number being called in the rate DB so figure out if the price is right)