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

public class WebSearchContractByFIO
    extends WebContractSearchModuleBase
{
    protected WebContractSearchParam searchByContractParamFId = new WebContractSearchParam( "search.by.contract.parameter.f.id", WebContractSearchParamType.STRING );
    protected WebContractSearchParam searchByContractParamIId = new WebContractSearchParam( "search.by.contract.parameter.i.id", WebContractSearchParamType.STRING );
    protected WebContractSearchParam searchByContractParamOId = new WebContractSearchParam( "search.by.contract.parameter.o.id", WebContractSearchParamType.STRING );
    
    public WebSearchContractByFIO()
    {
        contractSearchParams = Arrays.asList( title, useForAccessRestore, useForPayment, searchTextPlaceHolder, searchTextComment, searchByCustomer,
                                              searchByContractParamId, searchByContractParamFId, searchByContractParamIId, searchByContractParamOId,
                                              verifyByCustomerEmail, verifyByContractParameterEmailId, verifyByCustomerPhone, verifyByContractParameterPhoneId );
    }

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

        if ( !searchText.matches( "^(\\S{2,} *){3}$" ) )
        {
            return list;
        }
        
        searchText = searchText.replaceAll( "\\s+", " " );
        String[] fio = searchText.split( " " );
        
        if ( "true".equals( searchByCustomer.getValue() ) )
        {
            // /customer/fio/last - фамилия /customer/fio/first - имя /customer/fio/middle - отчество
            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/fio/last' AND UPPER(`field_value`)=?) "+
                           "AND cl.customer_id IN (SELECT customer_id FROM `customer_log` WHERE `old`=0 AND `field_key`='/customer/fio/first' AND UPPER(`field_value`)=?) " + 
                           "AND cl.customer_id IN (SELECT customer_id FROM `customer_log` WHERE `old`=0 AND `field_key`='/customer/fio/middle' AND UPPER(`field_value`)=?)";
            try( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setString( 1, fio[0].toUpperCase() );
                psSelect.setString( 2, fio[1].toUpperCase() );
                psSelect.setString( 3, fio[2].toUpperCase() );
                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 c.id, title FROM `contract` AS c " +
                            "INNER JOIN `contract_parameter_type_1` AS cp ON cp.cid=c.id AND pid=? AND UPPER(val)=?";
            try ( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setInt( 1, paramId );
                psSelect.setString( 2, searchText.toUpperCase() );
                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 paramFId = Utils.parseInt( searchByContractParamFId.getValue(), 0 ); 
        int paramIId = Utils.parseInt( searchByContractParamIId.getValue(), 0 ); 
        int paramOId = Utils.parseInt( searchByContractParamOId.getValue(), 0 ); 
        if ( paramFId > 0 && paramIId > 0 && paramOId > 0 )
        {
            String query =  "SELECT id, title FROM `contract` AS c " +
                            "WHERE id IN (SELECT cid FROM `contract_parameter_type_1` AS cpF WHERE cpF.pid=? AND UPPER(cpF.val)=?) " +
                            "AND id IN (SELECT cid FROM `contract_parameter_type_1` AS cpI WHERE cpI.pid=? AND UPPER(cpI.val)=?) " +
                            "AND id IN (SELECT cid FROM `contract_parameter_type_1` AS cpO WHERE cpO.pid=? AND UPPER(cpO.val)=?)";
            try ( PreparedStatement psSelect = connection.prepareStatement( query ) )
            {
                psSelect.setInt( 1, paramFId );
                psSelect.setString( 2, fio[0].toUpperCase() );
                psSelect.setInt( 3, paramIId );
                psSelect.setString( 4, fio[1].toUpperCase() );
                psSelect.setInt( 5, paramOId );
                psSelect.setString( 6, fio[2].toUpperCase() );
                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( verifyByCustomerPhone.getValue() ) )
            {
                verifyByCustomerPhone( list );
            }
            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 "Фамилия Имя Отчество";
    }

    @Override
    protected String getSearchTextCommentText()
    {
        return "Введите ФИО указанные при заключении договора";
    }
    
}