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

import java.sql.Connection;
import java.util.List;
import java.util.Optional;

import org.json.JSONArray;
import org.json.JSONObject;

import ru.bitel.bgbilling.kernel.contract.api.server.bean.customer.CustomerDao;
import ru.bitel.bgbilling.kernel.script.server.dev.GlobalScriptBase;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.sql.ConnectionSet;

/**
 * Конвертор контактов клиентов из старого формата в новый  
 */
public class CustomerContactConverter
	extends GlobalScriptBase
{
	@Override
	public void execute( Setup setup, ConnectionSet connectionSet )
		throws Exception
	{
        String querySelect = "SELECT id, data FROM " + CustomerDao.TABLE_CUSTOMER;
        String queryUpdate = "UPDATE " + CustomerDao.TABLE_CUSTOMER + " SET data=? WHERE id=?";
        Connection connection = connectionSet.getConnection();
	    try( var psSelect = connection.prepareStatement( querySelect );
	         var psUpdate = connection.prepareStatement( queryUpdate ) )
	    {
	        try( var rs = psSelect.executeQuery() )
	        {
	            while ( rs.next() )
	            {
	                String data = rs.getString( "data" );
	                JSONObject json = new JSONObject( data );
	                Object obj = json.optQuery( "/customer/contact" );
	                if ( obj == null || !(obj instanceof JSONObject) )
	                {
	                    continue;
	                }
	                JSONObject contactJson = (JSONObject)obj;
	                JSONArray mobiles = Optional.ofNullable( contactJson.optJSONArray( "mobile" ) ).orElse( new JSONArray() ); 
                    JSONArray phones = Optional.ofNullable( contactJson.optJSONArray( "phone" ) ).orElse( new JSONArray() ); 
                    JSONArray emails = Optional.ofNullable( contactJson.optJSONArray( "email" ) ).orElse( new JSONArray() );
                    List<String> keys = contactJson.keySet().stream().toList(); 
	                for ( String key : keys )
	                {
                        JSONObject a = new JSONObject();
                        a.put( "value", contactJson.optString( key, "" ) );
	                    if ( key.startsWith( "phone_" ) )
	                    {
	                        mobiles.put( a );
	                        contactJson.remove( key );
	                    }
                        else if ( key.startsWith( "home_phone_" ) )
                        {
                            phones.put( a );
                            contactJson.remove( key );
                        }
                        else if ( key.startsWith( "email_" ) )
                        {
                            emails.put( a );
                            contactJson.remove( key );
                        }
	                }
	                contactJson.put( "mobile", mobiles );
                    contactJson.put( "phone", phones );
                    contactJson.put( "email", emails );
                    //
                    int id = rs.getInt( "id" );
                    psUpdate.setString( 1, json.toString() );
                    psUpdate.setInt( 2, id );
                    psUpdate.executeUpdate();
	            }
	        }
	    }
	}
}