-- max_call_len(dst, trunk, maxcpm, contact, department, company)
-- How long can the user call with the given destination on the given trunk, looking at his credit?
CREATE or REPLACE FUNCTION max_call_len(varchar, integer, numeric, integer, integer, integer,varchar) RETURNS INTEGER AS '
   DECLARE
     _costplan integer;	   -- trunk rateplan
     _ccostplan integer;   -- country cost plan
     _billplan integer;    -- billing plan for contact
     _timewindow integer;  -- indicates the timewindow this call started in
     _credit numeric;      -- contact credit balance
     _cpm numeric;         -- cost per minute
     _curcpm numeric;      -- cost per minute
     _prefix varchar;
     _facility varchar;
     _cprefix varchar;     -- international prefix for our country
     _billing varchar;
     _length integer;
     _startcost numeric;
     _periodcost numeric;
     _firstperiod numeric;
     _periods numeric;
     i integer;
     rateid integer;
     _rate rate%ROWTYPE;
     _name varchar;
     _firstname varchar;
     _lastname varchar;
     _notfound bool;
   BEGIN
     select into _billing, _ccostplan, _name, _cprefix company.billing, country.rateplan, rateplan.name, country.prefix
       from company left join country on country.id = company.country
                    left join rateplan on rateplan.id = country.rateplan
      where company.id = $6;
     RAISE NOTICE ''billing = %'', _billing;
     RAISE NOTICE ''country cost plan is % (%)'', _ccostplan, _name;

     _prefix = normalize_phonenumber($1);
     RAISE NOTICE ''normalized prefix %'', _prefix;

     select into _costplan, _name trunk.rateplan, rateplan.name
       from trunk left join rateplan on rateplan.id = trunk.rateplan
      where trunk.id = $2 and trunk.status <> ''deleted'';
     IF NOT FOUND THEN
       RAISE NOTICE ''trunk % not found. User cannot call'', $2;
       return 0;
     END IF;
     RAISE NOTICE ''trunk cost plan is % (%)'', _costplan, _name;

     select into _billplan, _credit, _firstname, _lastname, _name 
                 contact.rateplan, (contact.credit + department.credit + company.credit) as credit, 
                 contact.firstname, contact.lastname, rateplan.name 
      from contact left join rateplan on rateplan.id = contact.rateplan
                   left join department on department.id = $5
                   left join company on company.id = $6
     where contact.id = $4 and contact.status <> ''deleted'';
     IF NOT FOUND THEN
       RAISE NOTICE ''contact % not found'', $4;
       return 0;
     END IF;
     RAISE NOTICE ''contact % (% %): _credit = %'', $4, _firstname, _lastname, _credit;
     IF _billplan = 1 THEN
       select into _billplan, _name department.rateplan, rateplan.name 
         from department left join rateplan on rateplan.id = department.rateplan
        where department.id = $5;
       IF NOT FOUND OR _billplan = 1 THEN 
         select into _billplan, _name company.rateplan, rateplan.name 
           from company left join rateplan on rateplan.id = company.rateplan
          where company.id = $6;
         IF NOT FOUND OR _billplan = 1 THEN
           RAISE NOTICE ''no billing rateplan found for contact, department or company: let the user call away'';
           return 14400000; -- 4 hours
         END IF;
       END IF;
     END IF;
     RAISE NOTICE ''billing plan is % (%)'', _billplan, _name;

     -- NOW ANSWER THE FOLLOWING QUESTION: HOW MUCH IS THIS CALL GOING TO COST US?
     -- MORE IMPORTANTLY: IF A LIMIT A SPECIFIED, CHECK WE ARE NOT GOING OVER THE LIMIT.

     IF $3 > 0 THEN -- maximum call cost is specified?
       RAISE NOTICE ''maximum call cost specified (%)'', $3;
       rateid = NULL;
       IF _ccostplan > 1 THEN
         RAISE NOTICE ''checking country cost plan'';
         rateid = find_rates(_ccostplan, 2, $1, ''CALL'');
         IF rateid IS NULL THEN
           RAISE NOTICE ''% not found in country rateplan'', $1;
         END IF;
       END IF;

       IF rateid IS NULL THEN
         RAISE NOTICE ''checking trunk cost plan'';
         rateid = find_rates(_costplan, length(_cprefix)-1, _prefix, ''CALL'');
         IF rateid IS NULL THEN
           RAISE NOTICE ''% not found in trunk cost plan - denied'', _prefix;
           return 0;
         END IF;
       END IF;
       select * into _rate from rate where id = rateid;
       RAISE NOTICE ''FOUND: id=%,prefix=%,description=%,timewindow=%,facility=%'', _rate.id,_rate.prefix,_rate.description,_rate.timewindow,_rate.facility;
       RAISE NOTICE ''firstperiod=%,periods=%,startcost=%,periodcost=%'', _rate.firstperiod,_rate.periods,_rate.startcost,_rate.periodcost;

       -- first compute the startcost
       _length = _rate.firstperiod;
       IF _length > 120 THEN -- if the start period is longer than two minutes
         IF _rate.startcost > 2 * $3 THEN  -- and costs more then twice the price per minute
           RAISE NOTICE ''start cost (%) exceeds max cost per minute for % minutes'', _rate.startcost, 120;
           RETURN 0;
         END IF;
       END IF;

       _cpm = _rate.startcost; 
       IF _rate.periods > 0 THEN  -- if there is a minute price
         IF _rate.periodcost > 0 THEN  -- and it is not zero
           _cpm = (_cpm + ((120 - _length) / _rate.periods * _rate.periodcost)) / (120 / 60);
         END IF;
       END IF;
       IF _cpm > $3 THEN
         RAISE NOTICE ''cost per minute (%) greater then maximum (%)'', _cpm, $3;
         RETURN 0;
       END IF;      
       RAISE NOTICE ''cost per minute = %. Allowed'', _cpm;
       -- assume that if we specified a maximum, that is because the user is calling in using a premium number.
       -- and thus is paying for this call though his phone bill (from which we receive a kickback).
       RETURN 14400000;
     END IF;

     -- THERE IS NO MAX COST PER MINUTE SPECIFIED. OK.
     -- ANSWER THE FOLLOWING QUESTION: WITH HIS CURRENT BALANCE, 
     -- HOW LONG IS THIS CALL ALLOWED TO LAST?

     RAISE NOTICE ''no maximum call cost specified'';
     _startcost = 0;
     _periodcost = 0;
     _firstperiod = 0;
     _periods = 0;
     FOR i IN 1..10 LOOP
       _facility = split_part($7, ''&'', i);
       EXIT WHEN _facility = '''';
       rateid = NULL;
       IF _ccostplan > 1 THEN
         RAISE NOTICE ''checking country cost plan'';
         rateid = find_rates(_ccostplan, length(_cprefix)-1, $1, _facility);
         IF rateid IS NULL THEN
           RAISE NOTICE ''% % not found in country cost plan'', $1, _facility;
         END IF;
       END IF;

       IF rateid IS NULL THEN
         RAISE NOTICE ''checking contact billing plan'';
         rateid = find_rates(_billplan, length(_cprefix)-1, _prefix, _facility);
         IF rateid IS NULL THEN
           RAISE NOTICE ''% % not found in billing plan - denied'', _prefix, _facility;
           return 0;
         END IF;
       END IF;

       select * into _rate from rate where id = rateid;
       RAISE NOTICE ''FOUND: id=%,prefix=%,description=%,timewindow=%,facility=%'', _rate.id,_rate.prefix,_rate.description,_rate.timewindow,_rate.facility;
       RAISE NOTICE ''firstperiod=%,periods=%,startcost=%,periodcost=%'', _rate.firstperiod,_rate.periods,_rate.startcost,_rate.periodcost;

       IF _rate.periodcost > 0 AND _rate.periods = 0 THEN
         RAISE NOTICE ''should not happen. periodcost is % but period length is 0..'', _rate.periodcost;
         RETURN 0;        -- should not happen, but prevent divide by zeroes.
       END IF;

       _startcost = _startcost + _rate.startcost;
       _periodcost = _periodcost + _rate.periodcost; 
       IF _rate.firstperiod > 0 THEN
         _firstperiod = _rate.firstperiod;
       END IF;
       IF _rate.periods > 0 THEN
         _periods = _rate.periods;
       END IF;
     END LOOP;

     RAISE NOTICE ''TOTAL: firstperiod=%,periods=%,startcost=%,periodcost=%'', _firstperiod,_periods,_startcost,_periodcost;
     _startcost = _startcost * 1.19;
     _periodcost = _periodcost * 1.19;
     RAISE NOTICE ''including VAT: startcost=%,periodcost=%'', _startcost,_periodcost;
     _credit = _credit - _startcost;
     IF _credit <= 0 THEN
       RAISE NOTICE ''not enough credit for this call'';
       return 0;
     END IF;
     IF _periodcost = 0 THEN
       RAISE NOTICE ''this call has no cost per minute at all'';
       RETURN 14400000; -- this call has no cost per minute at all.
     END IF;
       _length = _firstperiod;
       _length = _length + CAST ((_credit/_periodcost)*_periods AS INTEGER);
       if _length > 14400 THEN -- absolute max length is 4 hours.
         _length = 14400;
       END IF;
       RAISE NOTICE ''maximum call length is % seconds'', _length;
       RETURN _length * 1000; -- make milliseconds
   END;
' LANGUAGE plpgsql;
