-- step out of our provisioning DB
USE mysql;

-- drop database if it allready exists
-- this will drop all tables and triggers
DROP DATABASE IF EXISTS accounting;

-- create DB with utf8 default charset, so we don't have to
-- specify charset for each table
CREATE DATABASE IF NOT EXISTS accounting CHARACTER SET 'utf8';

USE accounting;

-- create accounting tables

CREATE TABLE `acc` like kamailio.acc;

CREATE TABLE `cdr` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `update_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `source_user_id` char(36) NOT NULL,
  `source_provider_id` varchar(255) NOT NULL,
  `source_user` varchar(255) NOT NULL,
  `source_domain` varchar(255) NOT NULL,
  `source_cli` varchar(64) NOT NULL,
  `source_clir` tinyint(1) NOT NULL default '0',
  `destination_user_id` char(36) NOT NULL,
  `destination_provider_id` varchar(255) NOT NULL,
  `destination_user` varchar(255) NOT NULL,
  `destination_domain` varchar(255) NOT NULL,
  `destination_user_dialed` varchar(255) NOT NULL,
  `destination_user_in` varchar(255) NOT NULL,
  `destination_domain_in` varchar(255) NOT NULL,
  `call_type` enum('call','cfu','cft','cfb','cfna') NOT NULL default 'call',
  `call_status` enum('ok','busy','noanswer','cancel','offline','timeout','other') NOT NULL default 'ok',
  `call_code` char(3) NOT NULL,
  `start_time` timestamp NOT NULL default '0000-00-00 00:00:00',
  `duration` int(10) unsigned NOT NULL,
  `call_id` varchar(255) NOT NULL,
  `carrier_cost` float default NULL,
  `reseller_cost` float default NULL,
  `customer_cost` float default NULL,
  `carrier_billing_fee_id` int(11) unsigned default NULL,
  `reseller_billing_fee_id` int(11) unsigned default NULL,
  `customer_billing_fee_id` int(11) unsigned default NULL,
  `carrier_billing_zone_id` int(11) unsigned default NULL,
  `reseller_billing_zone_id` int(11) unsigned default NULL,
  `customer_billing_zone_id` int(11) unsigned default NULL,
  `frag_carrier_onpeak` tinyint(1) default NULL,
  `frag_reseller_onpeak` tinyint(1) default NULL,
  `frag_customer_onpeak` tinyint(1) default NULL,
  `is_fragmented` tinyint(1) default NULL,
  `rated_at` datetime default NULL,
  `rating_status` enum('unrated','ok','failed') NOT NULL default 'unrated',
  PRIMARY KEY  (`id`),
  KEY `suid` (`source_user_id`),
  KEY `duid` (`destination_user_id`),
  KEY `suri` (`source_user`,`source_domain`,`source_cli`),
  KEY `duri` (`destination_user`,`destination_domain`),
  KEY `sprov` (`source_provider_id`),
  KEY `dprov` (`destination_provider_id`),
  KEY `kcid` (`call_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mark` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `collector` varchar(255) NOT NULL,
  `acc_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;