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.WebContractSearchResult;
import ru.bitel.common.Utils;

public class WebSearchContractByEmail
    extends WebContractSearchModuleBase
{
    public WebSearchContractByEmail()
    {
        contractSearchParams = Arrays.asList( title, useForAccessRestore, useForPayment, searchTextPlaceHolder, searchTextComment, 
                                              searchByCustomer, searchByContractParamId, verifyByCustomerPhone, verifyByContractParameterPhoneId );
    }
    
    @Override
    public List<WebContractSearchResult> doSearch( String searchText )
        throws BGException
    {
        List<WebContractSearchResult> list = super.doSearch( searchText );

        if ( !searchText.matches( "^[^@]{2,}@[^@]{5,}$" ) )
        {
            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/email_' 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 );
            }
        }
        
        // параметр с email
        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_3` AS cp ON cp.cid=c.id AND pid=? AND email LIKE ?";
            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( verifyByCustomerPhone.getValue() ) )
            {
                verifyByCustomerPhone( list );
            }
            int paramPhoneId = Utils.parseInt( verifyByContractParameterPhoneId.getValue(), 0 );
            if ( paramPhoneId > 0 )
            {
                verifyByContractParameterPhone( list, paramPhoneId );
            }
        }
        
        return list;
    }

    @Override
    public String getTitleText()
    {
        return "по e-mail";
    }
    
    @Override
    protected String getSearchTextPlaceHolderText()
    {
        return "email@domain.ru";
    }

    @Override
    protected String getSearchTextCommentText()
    {
        return "Введите адрес электронной почты указанный при регистрации договора";
    }
}
