package ru.bitel.bgbilling.kernel.dyn.web;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Arrays;
import java.util.List;

import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.admin.webmenu.server.bean.WebContractSearchModuleBase;
import ru.bitel.bgbilling.kernel.admin.webmenu.server.bean.WebContractSearchResult;
import ru.bitel.common.Utils;

public class WebSearchContractByMobile
    extends WebContractSearchModuleBase
{
    public WebSearchContractByMobile()
    {
        contractSearchParams = Arrays.asList( title, useForAccessRestore, useForPayment, searchTextPlaceHolder, searchTextComment, 
                                              searchByCustomer, searchByContractParamId, verifyByCustomerEmail, verifyByContractParameterEmailId );
    }

    @Override
    public List<WebContractSearchResult> doSearch( String searchText )
        throws BGException
    {
        List<WebContractSearchResult> list = super.doSearch( searchText );

        searchText = searchText.replaceAll( "[^\\d]", "" );
        if ( !searchText.matches( "^\\d{11}$" ) )
        {
            return list;
        }
        
        if ( "true".equals( searchByCustomer.getValue() ) )
        {
            String query = "SELECT id, title FROM `contract` AS c " +
                           "INNER JOIN `customer_link` AS cl ON cl.contract_id=c.id AND cl.date_to IS NULL " +
                           "WHERE cl.customer_id IN (SELECT customer_id FROM `customer_log` WHERE `old`=0 AND SUBSTRING( `field_key`, 1, 24 ) = '/customer/contact/phone_' AND `field_value`=?) ";
            try( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setString( 1, searchText );
                try( ResultSet resultSet = psSelect.executeQuery() )
                {
                    while ( resultSet.next() )
                    {
                        WebContractSearchResult result = new WebContractSearchResult();
                        result.setContractId( resultSet.getInt( "id" ) );
                        result.setContractTitle( resultSet.getString( "title" ) );
                        list.add( result );
                    }
                }
            }
            catch( Exception ex )
            {
                throw new BGException( ex );
            }
        }
        
        // параметр с номером телефона
        int paramId = Utils.parseInt( searchByContractParamId.getValue(), 0 ); 
        if ( paramId > 0 )
        {
            String query =  "SELECT id, title FROM `contract` AS c " +
                            "INNER JOIN `contract_parameter_type_phone_item` AS cp ON cp.cid=c.id AND pid=? AND n=1 AND phone=?";
            try ( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setInt( 1, paramId );
                psSelect.setString( 2, searchText );
                try ( ResultSet resultSet = psSelect.executeQuery() )
                {
                    while ( resultSet.next() )
                    {
                        WebContractSearchResult result = new WebContractSearchResult();
                        result.setContractId( resultSet.getInt( "id" ) );
                        result.setContractTitle( resultSet.getString( "title" ) );
                        list.add( result );
                    }
                }
            }
            catch( Exception ex )
            {
                throw new BGException( ex );
            }
        }
        
        // подстановка проверочных параметров
        if ( !list.isEmpty() )
        {
            if ( "true".equals( verifyByCustomerEmail.getValue() ) )
            {
                verifyByCustomerEmail( list );
            }
            int paramEmailId = Utils.parseInt( verifyByContractParameterEmailId.getValue(), 0 );
            if ( paramEmailId > 0 )
            {
                verifyByContractParameterEmail( list, paramEmailId );
            }
        }
        
        return list;
    }
    
    @Override
    public String getTitleText()
    {
        return "по номеру телефона";
    }
    
    @Override
    protected String getSearchTextPlaceHolderText()
    {
        return "+7 XXX XXX-XX-XX";
    }

    @Override
    protected String getSearchTextCommentText()
    {
        return "Введите номер мобильного телефона указанного при регистрации договора, в формате +7 XXX XXX-XX-XX";
    }
}
