VoIP Billing Platform for GnuGk
Copyright (c) 2004-2005, Michal Zygmuntowicz

This work is published under the GNU Public License (GPL)
see file COPYING for details

Recommended versions of PostgreSQL are 7.4.x, as 7.3.x do not deal
well with table alterations (column drops, adds) which may restrict
further upgrades and database manipulation.

This database schema creates the following tables:
*) voipaccount - corresponds to an account endpoints are associated with.
   The account contains information about the current balance and
   allowed prepaid/postpaid balance limit
*) voipuser - corresponds to an H.323 endpoint. Each voipuser has voipaccount
   assigned. There can be several users that use a single account.
   voipuser contains information like:
   - H.323 ID
   - chap password - together with H.323 ID are used to enable H.235 authentication
   - a list of allowed aliases (allowedaliases column) that the endpoint
     can register with. This is a REGULAR EXPRESSION. H.323 ID
	 is allowed by default. To allow an endpoint to register with any aliases,
     write '.*', to allow 'john' and '48581234' aliases only, write '^(john|48581234)$'
   - a list of aliases to be automatically assigned to an endpoint inside RCF
     message (a comma separated list)
   - IP address restriction (NULL to ignore)
   - 'terminating' flag to indicate that this user can terminate traffic 
     and receive money to his account
   - optional NAS IP address, to restrict an endpoint to access only the specified NAS
*) voiptariffdst - unique tariff destination (prefix). It separates pricing
   and billing information from rarely changing prefix information. A special 
   prefix 'PC' can be used to describe H.323 ID calls (to alias names beginning 
   with non-digit characters)
*) voiptariff - a tariff that associates tariff destination (prefix) 
   with pricing and billing information. Tariffs can be of default type
   or can belong to a particular tariff group. There are two main tariff types:
   regular and "terminating". Regular tariffs are to give pricing information
   for a caller, "terminating" tariffs are used to calculate pricing information
   for a called party that is flagged as "terminating" gateway. Terminating gateways
   are matched by their IP addresses (voipuser.framedip) or H.323 ID (voipuser.h323id)
   in case of tariffs with exactmatch flag set.
   Depending on whether you need terminations accounts, you should either use
   regular tariffs or create two tariffs per destination - one for a caller
   and the second for termination (a callee).
*) voiptariffgrp - tariff group can be used to represent a set of 1 or more 
   tariffs. Such group can be then applied to an account in order to change
   tariff priority during tariff selection. If two or more tariffs are found
   for the same destination, a one that belongs to a group associated with
   the particular account is selected (priority over a default tariff)
*) voiptariffsel - a binding between a tariff group and an account
*) voipcall - a CDR (Call Detail Record) table. Approtiate triggers take care
   about updating account balance when the call state is updated or the call
   is disconnected

To start with this system you need:
1. Create an account (voipaccount) object. Example:

	INSERT INTO voipaccount (balance) VALUES (10);
	
2. Create an user associated with this account (voipuser). Example:

	INSERT INTO voipuser (h323id, accountid, chappassword)
	  VALUES ('voip1', 1, 'secret');
	INSERT INTO voipuser (h323id, accountid, chappassword)
	  VALUES ('voip2', 1, 'secret');
	  
3. Build a tariff table - this consists of the following steps:
   a) build a list of destinations (prefixes),
   b) build tariffs associated with these destinations,
   c) (optional) create tariff groups,
   d) (optional) associate tariffs with tariff groups,
   e) (optional) associate tariff groups with particular accounts

   Example:

	INSERT INTO voiptariffdst (id, active, prefix, description)
	  VALUES (1, TRUE, 'PC', 'PC2PC');
	INSERT INTO voiptariffdst (id, active, prefix, description)
	  VALUES (2, TRUE, '0048', 'Poland');
	INSERT INTO voiptariffdst (id, active, prefix, description)
	  VALUES (3, FALSE, '004850', 'Poland - Mobiles');

	INSERT INTO voiptariff (id, dstid, grpid, price, currencysym, 
			initialincrement, regularincrement, description)
		VALUES (DEFAULT, 1, NULL, 0.01, 'USD', 1, 1, '1/1');
	INSERT INTO voiptariff (id, dstid, grpid, price, currencysym, 
			initialincrement, regularincrement, description)
		VALUES (DEFAULT, 2, NULL, 0.04, 'USD', 60, 30, '60/30');

   If someone would like to set a different tariff to Poland for some 
   customers only (discount, for example):

	INSERT INTO voiptariffgrp (id, discount, description)
		VALUES (1, 10.0, 'Discount');
		
	INSERT INTO voiptariff (id, dstid, grpid, price, currencysym, 
			initialincrement, regularincrement, description)
		VALUES (DEFAULT, 2, 1, 0.04, 'USD', 60, 30, '60/30');
	
	INSERT INTO voiptariffsel (id, grpid, accountid)
		VALUES (DEFAULT, 1, ACCOUNT_ID_TO_ASSOCIATE_WITH_THE_GROUP);

   Using this technique you can even build separate tariff sets for each
   account - just create one group per account and create a whole tariff set
   for each group.

   The directory 'examples' contains a sample scripts showing how to create
   a tariff table.