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.web.server.bean.WebContractSearchModuleBase;
import ru.bitel.bgbilling.kernel.admin.web.server.bean.WebContractSearchParam;
import ru.bitel.bgbilling.kernel.admin.web.server.bean.WebContractSearchParamType;
import ru.bitel.bgbilling.kernel.admin.web.server.bean.WebContractSearchResult;
import ru.bitel.common.Utils;

public class WebSearchContractByPassport
    extends WebContractSearchModuleBase
{
    protected WebContractSearchParam searchByContractParamSId = new WebContractSearchParam( "search.by.contract.parameter.s.id", WebContractSearchParamType.STRING );
    protected WebContractSearchParam searchByContractParamNId = new WebContractSearchParam( "search.by.contract.parameter.n.id", WebContractSearchParamType.STRING );
    
    public WebSearchContractByPassport()
    {
        contractSearchParams = Arrays.asList( title, useForAccessRestore, useForPayment, searchTextPlaceHolder, searchTextComment, searchByCustomer,
                                              searchByContractParamId, searchByContractParamSId, searchByContractParamNId,
                                              verifyByCustomerEmail, verifyByContractParameterEmailId, verifyByCustomerPhone, verifyByContractParameterPhoneId );
    }

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

        searchText = searchText.replaceAll( "[^\\d]", "" );
        if ( !searchText.matches( "^\\d{10}$" ) )
        {
            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 `field_key`='/customer/document/passport_rf/series' AND `field_value`=?) " +
                           "AND cl.customer_id IN (SELECT customer_id FROM `customer_log` WHERE `old`=0 AND `field_key`='/customer/document/passport_rf/number' AND `field_value`=?)";
            try( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setString( 1, searchText.substring( 0, 4 ) );
                psSelect.setString( 2, searchText.substring( 4 ) );
                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_1` AS cp ON cp.cid=c.id AND pid=? AND REGEXP_REPLACE( val, '[^0-9]', '' )=?";
            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 );
            }
        }

        // параметры с номером и серией паспорта
        int paramSId = Utils.parseInt( searchByContractParamSId.getValue(), 0 ); 
        int paramNId = Utils.parseInt( searchByContractParamNId.getValue(), 0 ); 
        if ( paramSId > 0 && paramNId > 0 )
        {
            String query =  "SELECT id, title FROM `contract` AS c " +
                            "WHERE id IN (SELECT cid FROM `contract_parameter_type_1` AS cpS WHERE cpS.pid=? AND cpS.val=?) " +
                            "AND id IN (SELECT cid FROM `contract_parameter_type_1` AS cpN WHERE cpN.pid=? AND cpN.val=?)";
            try ( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setInt( 1, paramSId );
                psSelect.setString( 2, searchText.substring( 0, 4 ) );
                psSelect.setInt( 3, paramNId );
                psSelect.setString( 4, searchText.substring( 4 ) );
                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 );
            }
            if ( "true".equals( verifyByCustomerPhone.getValue() ) )
            {
                verifyByCustomerPhone( list );
            }
            int paramEmailId = Utils.parseInt( verifyByContractParameterEmailId.getValue(), 0 );
            if ( paramEmailId > 0 )
            {
                verifyByContractParameterEmail( list, paramEmailId );
            }
            int paramPhoneId = Utils.parseInt( verifyByContractParameterPhoneId.getValue(), 0 );
            if ( paramPhoneId > 0 )
            {
                verifyByContractParameterPhone( list, paramPhoneId );
            }
        }
        
        return list;
    }
    
    @Override
    public String getTitleText()
    {
        return "по номеру паспорта";
    }
    
    @Override
    protected String getSearchTextPlaceHolderText()
    {
        return "XXXX XXXXXX";
    }

    @Override
    protected String getSearchTextCommentText()
    {
        return "Введите серию и номер паспорта указанного при регистрации договора, в формате XXXX XXXXXX";
    }
}