#
#  Configuration for the Freeradius SQL module using MySQL database and a
#  central radacct table. To use monthly tables with auto-rotation see
#  radius_accounting.conf and radius_accounting.proc
#
sql {
    driver                      = "rlm_sql_mysql"
    server                      = "sipdb"
    login                       = "radius"
    password                    = "PASSWORD"
    radius_db                   = "radius"
    acct_table                  = "radacct"
    sqltrace                    = no
    sqltracefile                = ${logdir}/sqltrace-%Y%m%d.log
    num_sql_socks               = 25
    connect_failure_retry_delay = 60

    accounting_start_query = "\
          INSERT INTO ${acct_table} \
                (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, \
                AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, \
                FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, \
                SipRPID, SourceIP, SourcePort, CanonicalURI) \
          VALUES \
                (NULL, '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{Billing-Party}', SUBSTRING_INDEX('%{Billing-Party}', '@',-1), \
                '%{SIP-Proxy-IP}', '%{NAS-Port}', '%S', '0', '0', '0', '0', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Called-Station-Id}')), \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Calling-Station-Id}')), \
                '%{Sip-Response-Code}', '%{Service-Type}', '%{ENUM-TLD}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0', \
                '%{Sip-Response-Code}', '%{Sip-Method}', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Sip-Translated-Request-URI}')), \
                '%{Sip-To-Tag}', '%{Sip-From-Tag}', '%{Sip-RPId}', \
                '%{Source-IP}', '%{Source-Port}', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Canonical-URI}')) \
                )"

    # Update query is used by MediaProxy >=1.8.0
    accounting_update_query       = "\
          UPDATE ${acct_table} \
          SET \
                AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, %{Acct-Session-Time}, 0), \
                AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), \
                AcctInputOctets = AcctInputOctets + %{Acct-Input-Octets}, \
                AcctOutputOctets = AcctOutputOctets + %{Acct-Output-Octets}, \
                SipUserAgents = '%{Sip-User-Agents}', \
                SipCodecs = '%{Media-Codecs}', \
                SipApplicationType = '%{Sip-Applications}', \
                MediaInfo = '%{Media-Info}', \
                Normalized = '0' \
                WHERE \
                AcctSessionId = '%{Acct-Session-Id}' \
                AND SipToTag = '%{Sip-To-Tag}' AND SipFromTag = '%{Sip-From-Tag}'"

    accounting_update_query_alt       = "\
          UPDATE ${acct_table} \
          SET \
                AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, %{Acct-Session-Time}, 0), \
                AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), \
                AcctInputOctets = AcctInputOctets + %{Acct-Input-Octets}, \
                AcctOutputOctets = AcctOutputOctets + %{Acct-Output-Octets}, \
                SipUserAgents = '%{Sip-User-Agents}', \
                SipCodecs = '%{Media-Codecs}', \
                SipApplicationType = '%{Sip-Applications}', \
                MediaInfo = '%{Media-Info}', \
                Normalized = '0' \
                WHERE \
                AcctSessionId = '%{Acct-Session-Id}' \
                AND SipToTag = 'n/a' AND SipFromTag = '%{Sip-From-Tag}'"

    accounting_stop_query = "\
          UPDATE ${acct_table} \
          SET \
                AcctStopTime = '%S', AcctSessionTime = unix_timestamp('%S') - unix_timestamp(AcctStartTime), \
                AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}', Normalized = '0', \
                RTPStatistics = '%{X-RTP-Stat}' \
                WHERE \
                AcctSessionId = '%{Acct-Session-Id}' AND \
                ((SipToTag = '%{Sip-To-Tag}' AND SipFromTag = '%{Sip-From-Tag}') OR (SipToTag = '%{Sip-From-Tag}' AND SipFromTag = '%{Sip-To-Tag}')) AND \
                ConnectInfo_stop IS NULL  AND (MediaInfo IS NULL OR MediaInfo != 'timeout')"

    accounting_stop_query_alt = "\
          UPDATE ${acct_table} \
          SET \
                AcctStopTime = '%S', AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime), \
                AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}', Normalized = '0', \
                RTPStatistics = '%{X-RTP-Stat}' \
                WHERE \
                AcctSessionId = '%{Acct-Session-Id}' AND \
                (SipToTag = 'n/a' AND (SipFromTag = '%{Sip-From-Tag}' OR SipFromTag = '%{Sip-To-Tag}')) AND \
                ConnectInfo_stop IS NULL AND (MediaInfo IS NULL OR MediaInfo != 'timeout')"

    accounting_failed_query = "\
          INSERT INTO ${acct_table} \
                (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, \
                AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, \
                FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, \
                SipRPID, SourceIP, SourcePort, CanonicalURI) \
          VALUES \
                (NULL, '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{Billing-Party}', SUBSTRING_INDEX('%{Billing-Party}', '@',-1), \
                '%{SIP-Proxy-IP}', '%{NAS-Port}', '%S', '%S', '0', '0', '0', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Called-Station-Id}')), \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Calling-Station-Id}')), \
                '%{Sip-Response-Code}', '%{Service-Type}', '%{ENUM-TLD}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0', \
                '%{Sip-Response-Code}', '%{Sip-Method}', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Sip-Translated-Request-URI}')), \
                MD5(RAND()), '%{Sip-From-Tag}', '%{Sip-RPId}', \
                '%{Source-IP}', '%{Source-Port}', \
                trim(leading 'sip:' from trim(leading 'sips:' from '%{Canonical-URI}')) \
            )"
}