#
#  Configuration for the NIBS module, when using MySQL.
#
#  The database schema is available at:
#
#   freeradius_dir/src/modules/rlm_nibs/drivers/rlm_nibs_mysql/nibs_mysql.sql
#
#  The database schema for PROF mode is available at:
#   freeradius_dir/src/modules/rlm_nibs/drivers/rlm_nibs_mysql/nibs_mysql_prof_add.sql
#
#  If you are using PostgreSQL, please use 'nibs_postgresql.conf', instead.
#
nibs {

    # Database type
    driver = "rlm_nibs_postgresql"

    # SQL Server configuration
    server = "@dbhost@"
    port = @dbport@
    login = "@dblogin@"
    password = "@dbpassword@"

    # Database configuration
    nibs_db = "@db@"

    # SQL tables configuration
    auth_table = "sk_users"
    packets_table = "sk_packets"
    prices_table = "sk_prices"
    acct_table = "sk_actions"
    blacklist_table = "sk_blacklist"
    holidays_table = "sk_holidays"
    info_table = "sk_history"
    online_table = "sk_online"
    
    # Print all SQL statements when in debug mode (-x)
    sqltrace = no
    sqltracefile = ${logdir}/nibs_sqltrace.sql

    # number of sql connections to make to server
    num_socks = 16

    # number of seconds to dely retrying on a failed database
    # connection (per_socket)
    connect_failure_retry = 100
    connect_failure_retry_delay = 3

    # Safe characters list for sql queries. Everything else is replaced
    # with their mime-encoded equivalents.
    # The default list should be ok
    #safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"

    #######################################################################
    #  Query config:  Username
    #######################################################################
    # This is the username that will get substituted, escaped, and added 
    # as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used below 
    # everywhere a username substitution is needed so you can be sure 
    # the username passed from the client is escaped properly.  
    #
    #  Uncomment the next line, if you want the sql_user_name to mean:
    #
    #    Use Stripped-User-Name, if it's there.
    #    Else use User-Name, if it's there,
    #    Else use hard-coded string "none" as the user name.
    #sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}"
    #
    sql_user_name = "%{User-Name}"

    #######################################################################
    #  Prices select queries
    #######################################################################

    group_select_query = "SELECT gid, allowed_servers, tos, do_with_tos, direction, fixed, fixed_cost, activation_time, total_time_limit, month_time_limit, week_time_limit, day_time_limit, total_traffic_limit, month_traffic_limit, week_traffic_limit, day_traffic_limit, total_money_limit, month_money_limit, week_money_limit, day_money_limit, session_timeout, idle_timeout, simultaneous_use, port_limit, login_time, other_params, huntgroup_name, allow_callback, framed_ip, framed_mask, no_acct, no_pass, up, down, ippool_name, framed_route, cos FROM ${packets_table}"
    holyday_select_query = "SELECT holiday_date FROM ${holidays_table}"
    prices_select_query = "SELECT h0, input0, output0, h1, input1, output1, h2, input2, output2, h3, input3, output3, h4, input4, output4, h5, input5, output5, h6, input6, output6, h7, input7, output7, h8, input8, output8, h9, input9, output9, h10, input10, output10, h11, input11, output11, h12, input12, output12, h13, input13, output13, h14, input14, output14, h15, input15, output15, h16, input16, output16, h17, input17, output17, h18, input18, output18, h19, input19, output19, h20, input20, output20, h21, input21, output21, h22, input22, output22, h23, input23, output23, week_day, gid, price_type, servers, uid FROM ${prices_table}"
    blacklist_select_query = "SELECT uid, gid, phone, permit FROM ${blacklist_table} order by uid"

    #######################################################################
    #  Authenticate Query
    #######################################################################

    authenticate_query = "SELECT gid, auth_type, password, auth_info FROM sk_enabled_users WHERE username = '%{SQL-User-Name}'"

    #######################################################################
    #  Authorization Queries
    #######################################################################

    authorize_query = "SELECT username, password, auth_type, uid, gid, deposit, credit, extract(epoch from add_date at time zone 'GMT')::int4, blocked, activated, extract(epoch from expired at time zone 'GMT')::int4, total_time, total_traffic, total_money, extract(epoch from last_connection at time zone 'GMT')::int4, framed_ip, framed_mask, callback_number, tos, do_with_tos, direction, fixed, fixed_cost, activation_time, total_time_limit, month_time_limit, week_time_limit, day_time_limit, total_traffic_limit, month_traffic_limit, week_traffic_limit, day_traffic_limit, total_money_limit, month_money_limit, week_money_limit, day_money_limit, login_time, huntgroup_name, simultaneous_use, port_limit, session_timeout, idle_timeout, ippool_name, no_pass, no_acct, allow_callback, other_params, allowed_servers, auth_info, up, down, framed_route FROM ${auth_table} WHERE username = '%{SQL-User-Name}'"

    #######################################################################
    #  Filter queries
    #######################################################################
    
    month_time_query = "SELECT sum(time_on) FROM ${acct_table} WHERE uid = '%%lu' and start_time >= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' AND start_time <= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' + INTERVAL '1 MONTH'"
    week_time_query = "SELECT sum(time_on) FROM ${acct_table} WHERE uid = '%%lu' and extract(year from start_time at time zone 'GMT') = extract(year from LOCALTIMESTAMP at time zone 'GMT') AND extract(week from start_time at time zone 'GMT') = extract(week from LOCALTIMESTAMP at time zone 'GMT')"
    day_time_query = "SELECT sum(time_on) FROM ${acct_table} WHERE uid = '%%lu' and extract(day from start_time at time zone 'GMT') = extract(day from LOCALTIMESTAMP at time zone 'GMT')"

    month_traffic_query = "SELECT sum(in_bytes), sum(out_bytes) FROM ${acct_table} WHERE uid = '%%lu' and start_time >= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' AND start_time <= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' + INTERVAL '1 MONTH'"
    week_traffic_query = "SELECT sum(in_bytes), sum(out_bytes) FROM ${acct_table} WHERE uid = '%%lu' and extract(year from start_time at time zone 'GMT') = extract(year from LOCALTIMESTAMP at time zone 'GMT') AND extract(week from start_time at time zone 'GMT') = extract(week from LOCALTIMESTAMP at time zone 'GMT')"
    day_traffic_query = "SELECT sum(in_bytes), sum(out_bytes) FROM ${acct_table} WHERE uid = '%%lu' and extract(day from start_time at time zone 'GMT') = extract(day from LOCALTIMESTAMP at time zone 'GMT')"

    month_money_query = "SELECT sum(billing_minus) FROM ${acct_table} WHERE uid = '%%lu' and start_time >= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' AND start_time <= extract(year from LOCALTIMESTAMP at time zone 'GMT')||'-'||extract(month from LOCALTIMESTAMP at time zone 'GMT')||'-01 00:00:00' + INTERVAL '1 MONTH'"
    week_money_query = "SELECT sum(billing_minus) FROM ${acct_table} WHERE uid = '%%lu' and extract(year from start_time at time zone 'GMT') = extract(year from LOCALTIMESTAMP at time zone 'GMT') AND extract(week from start_time at time zone 'GMT') = extract(week from LOCALTIMESTAMP at time zone 'GMT')"
    day_money_query = "SELECT sum(billing_minus) FROM ${acct_table} WHERE uid = '%%lu' and extract(day from start_time at time zone 'GMT') = extract(day from LOCALTIMESTAMP at time zone 'GMT')"

    activation_update_query = "UPDATE ${auth_table} SET add_date = %%s, expired = %%s, activated = %%d, last_connection = from_unixtime(%l) WHERE username = '%{SQL-User-Name}'"
    fixed_select_query = "SELECT extract(day from '%S' at time zone 'GMT'), extract(day from last_connection at time zone 'GMT') FROM ${auth_table} WHERE username = '%{SQL-User-Name}'"

    #######################################################################
    #  Accounting Queries
    #######################################################################
    # accounting_onoff_query    - query for Accounting On/Off packets 
    # accounting_update_query   - query for Accounting update packets 
    # accounting_start_query    - query for Accounting start packets 
    # accounting_start_query_alt    - query for Accounting start packets 
    #                               (alternate in case first query fails)
    # accounting_stop_query     - query for Accounting stop packets 
    #######################################################################

    statistic_select_query = "SELECT time_on, in_bytes, out_bytes, extract(epoch from start_time at time zone 'GMT')::int4, billing_minus, hour_in_bytes, hour_out_bytes, hour_traffic_money, last_change FROM ${online_table} WHERE id = '%{Acct-Session-Id}' AND unique_id = '%{Acct-Unique-Session-Id}' AND server = '%{NAS-IP-Address}' AND port = '%{NAS-Port}' AND uid = '%%s'"
    accounting_start_query = "INSERT INTO ${acct_table} (uid, gid, id, unique_id, username, server, port, client_ip, protocol, ip, call_to, call_from, terminate_cause, connect_info, start_time, last_change, before_billing) VALUES (%%lu, %%lu, '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', to_inet('%{NAS-IP-Address}'), '%{NAS-Port}', to_inet('%{Client-IP-Address}'), '%{Framed-Protocol}', to_inet('%{Framed-IP-Address}'), '%{NAS-Identifier}', '%{Calling-Station-Id}', 'Online', '%{Connect-Info}', '%S', %l, %%16.6f)"
    accounting_start_query_alt = "INSERT INTO ${acct_table} (uid, gid, id, unique_id, username, server, port, client_ip, protocol, ip, call_to, call_from, terminate_cause, connect_info, start_time, last_change, before_billing) VALUES (%%lu, %%lu, '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', to_inet('%{NAS-IP-Address}'), '%{NAS-Port}', to_inet('%{Client-IP-Address}'), '%{Framed-Protocol}', to_inet('%{Framed-IP-Address}'), '%{NAS-Identifier}', '%{Calling-Station-Id}', 'Online', '%{Connect-Info}', from_unixtime(%l - %%d), %l, %%16.6f)"

    accounting_reopen = "UPDATE ${acct_table} SET terminate_cause = 'Online', stop_time = LOCALTIMESTAMP, id = '%{Acct-Session-Id}', unique_id = '%{Acct-Unique-Session-Id}' WHERE id LIKE '%{Acct-Session-Id}' AND unique_id LIKE '%{Acct-Unique-Session-Id}' AND server = to_inet('%{NAS-IP-Address}') AND port = '%{NAS-Port}' AND username = '%{SQL-User-Name}' AND terminate_cause != 'Online'"

    accounting_alive_auth_query = "UPDATE ${auth_table} SET deposit = deposit - %%16.6f, total_traffic = total_traffic + %%ld, total_time = total_time + %%ld, total_money = total_money + %%16.6f, last_connection = from_unixtime(%l), last_info = '%{Calling-Station-Id}' WHERE uid = '%%lu'"
    accounting_stop_auth_query = "UPDATE ${auth_table} SET deposit = deposit - %%16.6f, total_traffic = total_traffic + %%ld, total_time = total_time + %%ld, total_money = total_money + %%16.6f, last_connection = from_unixtime(%l), last_info = '%{Calling-Station-Id}' WHERE uid = '%%lu'"

    accounting_update_query = "UPDATE ${acct_table} SET time_on = if(%{Acct-Session-Time},%{Acct-Session-Time},time_on), in_bytes = if(%{Acct-Input-Octets},%{Acct-Input-Octets},in_bytes), out_bytes = if(%{Acct-Output-Octets},%{Acct-Output-Octets},out_bytes), last_change = %l, billing_minus = billing_minus + %%16.6f, ip = to_inet('%{Framed-IP-Address}'), hour_traffic_money = %%16.6f, hour_in_bytes = %%s, hour_out_bytes = %%s WHERE id='%{Acct-Session-Id}' AND unique_id = '%{Acct-Unique-Session-Id}' AND server = to_inet('%{NAS-IP-Address}') AND port = '%{NAS-Port}' AND terminate_cause = 'Online' AND username = '%{SQL-User-Name}'"
    accounting_stop_query = "UPDATE ${acct_table} SET time_on = if(%{Acct-Session-Time},%{Acct-Session-Time},time_on), stop_time = '%S', in_bytes = if(%{Acct-Input-Octets},%{Acct-Input-Octets},in_bytes), out_bytes = if(%{Acct-Output-Octets},%{Acct-Output-Octets},out_bytes), billing_minus = billing_minus + %%16.6f, terminate_cause='%{Acct-Terminate-Cause}', connect_info = '%{Connect-Info}', id = extract(epoch from start_time)||'-%{Acct-Session-Id}', unique_id = extract(epoch from start_time)||'-%{Acct-Unique-Session-Id}', ip = to_inet('%{Framed-IP-Address}') WHERE id='%{Acct-Session-Id}' AND unique_id = '%{Acct-Unique-Session-Id}' AND server = to_inet('%{NAS-IP-Address}') AND port = '%{NAS-Port}' AND uid = '%%lu' AND terminate_cause = 'Online'"

    accounting_onoff_query = "UPDATE ${acct_table} SET stop_time=LOCALTIMESTAMP, time_on=extract(epoch from LOCALTIMESTAMP at time zone 'GMT')::int4 - extract(epoch from start_time at time zone 'GMT')::int4, terminate_cause='Admin-Reboot' WHERE (call_to='%{NAS-Identifier}' OR server=to_inet('%{NAS-IP-Address}')) AND terminate_cause = 'Online'"

    #######################################################################
    # Simultaneous Use Checking Queries
    #######################################################################
    # simul_count_query	- query for the number of current connections and
    #                     to return details of current connections for verification
    #			- If this is not defined, no simultaneouls use checking 
    #			- will be performed by this module instance
    #######################################################################

    # Comment simul_count_query to disable simultaneous use checking
    simul_count_query = "SELECT id, username, server, port, ip, call_from FROM ${online_table} WHERE username = '%{SQL-User-Name}'"

    log_invalid_attempts_query = "INSERT INTO ${info_table} (date_time, auth_name, comment, access_from) VALUES ('%S', '%{SQL-User-Name}', '%%s', '%%s')"
    alive_max_interval = 60

    set_session_timeout = yes
    use_black_list = no
    accounting_on_alive = yes
    ignore_localhost_pkt = yes
    kill_by_alive = no
    round_to_minute = no
    kill_program = "${sbindir}/dialup.userkill %{User-Name} %{NAS-IP-Address} %{Client-IP-Address} %{NAS-Port} %{Framed-IP-Address} %%s"
#    onstart_program = "${sbindir}/dialup.userstart %{User-Name} %{NAS-IP-Address} %{Client-IP-Address} %{NAS-Port} %{Framed-IP-Address} %%s"
#    onstop_program = "${sbindir}/dialup.userstop %{User-Name} %{NAS-IP-Address} %{Client-IP-Address} %{NAS-Port} %{Framed-IP-Address} %%s"
#  Shapers for CISCO:
#    ingress_shaper = "Cisco-Avpair = \"lcp:interface-config=rate-limit input %%i000 8000 8000 c t e d\""
#    egress_shaper = "Cisco-Avpair += \"lcp:interface-config=rate-limit output %%i000 8000 8000 c t e d\""
# Shapers for IPROUTE2:
#    ingress_shaper = "Upstream-Speed-Limit = %%i"
#    egress_shaper = "Downstream-Speed-Limit = %%i"

    allowed_service = ""
}
