#!/usr/bin/php

#############################################################################
## indexer settings
#############################################################################
indexer
{
    mem_limit            = 100M
}

#############################################################################
## searchd settings
#############################################################################
searchd
{
    port         = 3312
    log          = /var/log/sphinx/searchd-eventum.log
    query_log    = /var/log/sphinx/query-eventum.log
    read_timeout = 5
    max_children = 30
    pid_file     = /var/run/sphinx/searchd-eventum.pid
    max_matches  = 100000
}

source eventum
{
    type                = mysql
    # connect over unix socket
    # sql_sock            = 

    # connect over tcp
    sql_host            = sp.cxflm71zwqwb.us-east-1.rds.amazonaws.com
    sql_port            = 3306

    sql_user            = softpower
    sql_pass            = !softpower123
    sql_db              = bugtrack
}


#############################################################################
## ISSUES
#############################################################################
source src_issue : eventum
{
    sql_query_range = SELECT MIN(iss_id), MAX(iss_id) FROM eventum_issue
    sql_query = \
        SELECT \
            iss_id, \
            iss_prj_id as prj_id, \
            1 as index_id, \
            iss_id as issue_id, \
            iss_customer_id as customer_id, \
            iss_customer_contract_id as contract_id, \
            UNIX_TIMESTAMP(iss_created_date) AS iss_created_date, \
            iss_summary, \
            iss_description \
        FROM \
            eventum_issue \
        WHERE \
            iss_id>=$start AND \
            iss_id<=$end
    sql_attr_uint   = index_id
    sql_attr_uint   = issue_id
    sql_attr_uint   = prj_id
    sql_attr_uint   = customer_id
    sql_attr_uint   = contract_id
    sql_attr_timestamp   = iss_created_date
    sql_query_pre      = SET NAMES utf8
    sql_query_info     = SELECT iss_summary FROM eventum_issue WHERE iss_id=$id
}

index issue
{
    source              = src_issue
    path                = /var/lib/sphinx/eventum/issue
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index issue_stemmed : issue
{
    path                = /var/lib/sphinx/eventum/issue_stemmed
    morphology          = stem_en
}

# Combine the main and stemmed indexes together
index issue_description
{
    type                 = distributed
    local                = issue
    local                = issue_stemmed
}


#############################################################################
## ISSUES recent
#############################################################################
source src_issue_recent : src_issue
{
    sql_query_range = SELECT IF(CAST(MAX(iss_id) AS SIGNED)-100>0, MAX(iss_id), 1), MAX(iss_id) FROM eventum_issue
}

index issue_recent
{
    source              = src_issue_recent
    path                = /var/lib/sphinx/eventum/issue_recent
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index issue_recent_stemmed : issue_recent
{
    path                = /var/lib/sphinx/eventum/issue_recent_stemmed
    morphology          = stem_en
}

index issue_recent_description
{
    type                = distributed
    local               = issue_recent
    local               = issue_recent_stemmed
}


#############################################################################
## EMAILS
#############################################################################
source src_email : eventum
{
    sql_query_range = SELECT MIN(seb_sup_id), MAX(seb_sup_id) FROM eventum_support_email_body
    sql_range_step = 2000
    sql_query = \
        SELECT \
            seb_sup_id, \
            2 as index_id, \
            iss_id as issue_id, \
            iss_prj_id as prj_id, \
            iss_customer_id as customer_id, \
            iss_customer_contract_id as contract_id, \
            UNIX_TIMESTAMP(iss_created_date) AS iss_created_date, \
            sup_from, \
            sup_to, \
            sup_cc, \
            sup_subject, \
            seb_body \
        FROM \
            eventum_support_email, \
            eventum_support_email_body, \
            eventum_issue \
        WHERE \
            sup_iss_id = iss_id AND \
            seb_sup_id>=$start AND \
            seb_sup_id<=$end AND \
            sup_id = seb_sup_id
    sql_attr_uint       = index_id
    sql_attr_uint       = issue_id
    sql_attr_uint       = prj_id
    sql_attr_uint       = customer_id
    sql_attr_uint       = contract_id
    sql_attr_timestamp  = iss_created_date
    sql_query_pre       = SET NAMES utf8
    sql_query_info      = SELECT seb_body FROM eventum_support_email_body WHERE seb_sup_id=$id
}

index email
{
    source          = src_email
    path            = /var/lib/sphinx/eventum/email
    morphology      = none
    min_word_len    = 1
    charset_type    = sbcs
}

index email_stemmed : email
{
    path            = /var/lib/sphinx/eventum/email_stemmed
    morphology      = stem_en
}

index email_description
{
    type                   = distributed
    local                  = email
    local                  = email_stemmed
}

#############################################################################
## EMAILS RECENT (Only last 1000 records)
#############################################################################
source src_email_recent : src_email
{
#    sql_query_range = SELECT (MAX(seb_sup_id)-1000), MAX(seb_sup_id) FROM eventum_support_email_body
#    # we use @max to store initial value and not to overflow
#    sql_query_range = SELECT (@max:=MAX(seb_sup_id))- 1000, MAX(seb_sup_id) FROM eventum_support_email_body
#    # we use need to cast MAX() result to SIGNED not to overflow and check for negative value
    sql_query_range = SELECT IF(CAST(MAX(sup_id) AS SIGNED)-1000>0, MAX(sup_id), 1), MAX(sup_id) FROM eventum_support_email
}

index email_recent
{
    source          = src_email_recent
    path            = /var/lib/sphinx/eventum/email_recent
    morphology      = none
    stopwords       =
    min_word_len    = 1
    charset_type    = sbcs
}

index email_recent_stemmed : email_recent
{
    path            = /var/lib/sphinx/eventum/email_recent_stemmed
    morphology      = stem_en
}

index email_recent_description
{
    type                   = distributed
    local                  = email_recent
    local                  = email_recent_stemmed
}


#############################################################################
## PHONE SUPPORT
#############################################################################
source src_phonesupport : eventum
{
    sql_query_range = SELECT MIN(phs_id), MAX(phs_id) FROM eventum_phone_support
    sql_range_step = 1000
    sql_query = \
        SELECT \
            phs_id, \
            3 as index_id, \
            iss_id as issue_id, \
            iss_prj_id as prj_id, \
            iss_customer_id as customer_id, \
            iss_customer_contract_id as contract_id, \
            UNIX_TIMESTAMP(iss_created_date) AS iss_created_date, \
            /*phs_call_from AS call_name_from, */ \
            /*phs_call_to as call_name_to, */ \
            phs_phone_number, \
            phs_description/*, \
            phs_triggered_by_other */\
        FROM \
            eventum_phone_support, \
            eventum_issue \
        WHERE \
            phs_iss_id = iss_id AND \
            phs_id>=$start AND phs_id<=$end
    sql_attr_uint       = index_id
    sql_attr_uint       = issue_id
    sql_attr_uint       = prj_id
    sql_attr_uint       = customer_id
    sql_attr_uint       = contract_id
    sql_attr_timestamp  = iss_created_date
    sql_query_pre       = SET NAMES utf8
    sql_query_info      = SELECT phs_description FROM eventum_phone_support WHERE phs_id=$id
}

index phonesupport
{
    source              = src_phonesupport
    path                = /var/lib/sphinx/eventum/phonesupport
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index phonesupport_stemmed : phonesupport
{
    path                = /var/lib/sphinx/eventum/phonesupport_stemmed
    morphology          = stem_en
}

index phonesupport_description
{
    type                = distributed
    local               = phonesupport
    local               = phonesupport_stemmed
}


#############################################################################
## PHONE SUPPORT RECENT (Only last 1000 records)
#############################################################################
source src_phonesupport_recent : src_phonesupport
{
#    sql_query_range = SELECT (MAX(phs_id)-1000), MAX(phs_id) FROM eventum_phone_support
#    # we use @max:= to avoid overflow via cast
#    sql_query_range = SELECT (@max:=MAX(phs_id))-1000, MAX(phs_id) FROM eventum_phone_support
    sql_query_range = SELECT IF(CAST(MAX(phs_id) AS SIGNED)-1000>0, MAX(phs_id), 1), MAX(phs_id) FROM eventum_phone_support

}

index phonesupport_recent
{
    source              = src_phonesupport_recent
    path                = /var/lib/sphinx/eventum/phonesupport_recent
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index phonesupport_recent_stemmed : phonesupport_recent
{
    path                = /var/lib/sphinx/eventum/phonesupport_recent_stemmed
    morphology          = stem_en
}

index phonesupport_recent_description
{
    type                = distributed
    local               = phonesupport_recent
    local               = phonesupport_recent_stemmed
}


#############################################################################
## NOTES data source definition
#############################################################################
source src_note : eventum
{
    sql_query_range = SELECT MIN(not_id), MAX(not_id) FROM eventum_note
    sql_range_step = 1000
    sql_query = \
        SELECT \
            not_id, \
            4 as index_id, \
            iss_id as issue_id, \
            iss_prj_id as prj_id, \
            iss_customer_id as customer_id, \
            iss_customer_contract_id as contract_id, \
            UNIX_TIMESTAMP(iss_created_date) AS iss_created_date, \
            not_title, \
            not_note /*, \
            not_blocked_message*/ \
        FROM \
            eventum_note, \
            eventum_issue \
        WHERE \
            not_iss_id = iss_id AND \
            not_id>=$start AND not_id<=$end
    sql_attr_uint       = index_id
    sql_attr_uint       = issue_id
    sql_attr_uint       = prj_id
    sql_attr_uint       = customer_id
    sql_attr_uint       = contract_id
    sql_attr_timestamp  = iss_created_date
    sql_query_pre       = SET NAMES utf8
    sql_query_info      = SELECT not_id, not_iss_id, not_title FROM eventum_note WHERE not_id=$id
}

index note
{
    source              = src_note
    path                = /var/lib/sphinx/eventum/note
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index note_stemmed : note
{
    path                = /var/lib/sphinx/eventum/note_stemmed
    morphology          = stem_en
}

index note_description
{
    type                = distributed
    local               = note
    local               = note_stemmed
}


#############################################################################
## NOTES RECENT (Only last 1000 notes)
#############################################################################
source src_note_recent : src_note
{
#    sql_query_range = SELECT (MAX(not_id)-1000), MAX(not_id) FROM eventum_note
#    # we use @max:= to avoid overflow via cast
#    sql_query_range = SELECT (@max:=MAX(not_id))-1000, MAX(not_id) FROM eventum_note
    sql_query_range = SELECT IF(CAST(MAX(not_id) AS SIGNED)-1000>0, MAX(not_id), 1), MAX(not_id) FROM eventum_note
}

index note_recent
{
    source              = src_note_recent
    path                = /var/lib/sphinx/eventum/note_recent
    morphology          = none
    min_word_len        = 1
    charset_type        = sbcs
}

index note_recent_stemmed : note_recent
{
    path                = /var/lib/sphinx/eventum/note_recent_stemmed
    morphology          = stem_en
}

index note_recent_description
{
    type                = distributed
    local               = note_recent
    local               = note_recent_stemmed
}