package ru.bitel.bgbilling.modules.voice.dyn.converter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import ru.bitel.bgbilling.kernel.base.phone.common.bean.GeographicCode;
import ru.bitel.bgbilling.kernel.base.server.logger.BGLogger;
import ru.bitel.bgbilling.server.util.ServerUtils;

public class GeocodesDest2Voice
{
	private void process()
	{
		String selectGeoDestQuery = "SELECT gc.code,id,title FROM voiceip_geographic_code_3 as gc "
		                            + "left join voiceip_dest_3 pd on pd.id=gc.dest_id";
		String insertVoiceDestQuery = "INSERT INTO voice_destination_217 (title,operatorId) VALUES (?,1)";
		String insertVoiceGeocodeQuery = "INSERT INTO voice_geographic_code_217 (code, destinationId, dateFrom, level) VALUES (?,?,NOW(),?)";
		try ( Connection bgTestCon = getBgbillingTestConnection(); 
			  Connection bgLocalCon = getBgbillingLocalConnection(); 
			  Statement st = bgLocalCon.createStatement(); ResultSet rs = st.executeQuery( selectGeoDestQuery ))
		{
			String[] tables2clean = new String[]{"voice_geographic_code_217", "voice_destination_217"};
			for ( int i = 0; i < tables2clean.length; i++ )
			{
				try ( Statement delSt = bgTestCon.createStatement() )
				{
					delSt.execute( "delete from " + tables2clean[i] );
				}
				catch( SQLException ex )
				{
				    BGLogger.error( ex );
				} 
			}
			
			//строим дерево кодов и по мере построения вставляем в БД
			GeographicCode root = new GeographicCode();
			root.setCode( "" );
			root.setLevel( 0 );
			GeographicCode[] path = new GeographicCode[20];
			
			String lastCode = root.getCode();
			int level = 0;
			path[level] = root;
			//запоминаем коды вставленных телефонных кодов
			//2 вспомогательных мапа: в первом хранятся id кодов, которые были вставлены в voice
			//во втором - непосредственные родители каждого узла
			Map<String, Integer> codeIdMap = new HashMap<>();
			Map<String, String> codeParentMap = new HashMap<>();
			while ( rs.next() )
			{
				String code = rs.getString( "gc.code" );
				String destTitle = rs.getString( "title" );
				
				//сперва вставляем направление в voice
				int voiceDestId = -1;
				try ( var insertVoiceDestPs = bgTestCon.prepareStatement( insertVoiceDestQuery, Statement.RETURN_GENERATED_KEYS ))
				{
					insertVoiceDestPs.setString( 1, destTitle );
					insertVoiceDestPs.executeUpdate();
					voiceDestId = ServerUtils.lastInsertId( insertVoiceDestPs );
				}
				//строим дерево кодов и тут же вставляем в таблицу с геокодами
				GeographicCode geoCode = new GeographicCode();
				geoCode.setCode( code );
				geoCode.setDestinationId( voiceDestId );
	
				if ( !code.startsWith( lastCode ) )
				{
					while( --level >= 0 && !code.startsWith( path[level].getCode() ) )
					{
					}
				}
				GeographicCode parent = path[level];
				parent.addChild( geoCode );
	
				path[++level] = geoCode;
				geoCode.setLevel( level );
				
				lastCode = code;
				try ( var insertVoiceGeocodePs = bgTestCon.prepareStatement( insertVoiceGeocodeQuery, Statement.RETURN_GENERATED_KEYS ))
				{
					insertVoiceGeocodePs.setString( 1, geoCode.getCode() );
					insertVoiceGeocodePs.setInt( 2, geoCode.getDestinationId() );
					insertVoiceGeocodePs.setInt( 3, geoCode.getLevel() );
					
					insertVoiceGeocodePs.executeUpdate();
					int codeId = ServerUtils.lastInsertId( insertVoiceGeocodePs );
					//запоминаем код вставленной записи и код родителя
					codeIdMap.put( geoCode.getCode(), codeId );
					codeParentMap.put( geoCode.getCode(), parent.getCode() );
				}
			}
			
			//Обновляем информацию о родителях кодов
			String updateQuery = "UPDATE voice_geographic_code_217 SET parentId=? WHERE id=?";
			try ( var updatePs = bgTestCon.prepareStatement( updateQuery ))
			{
				for (String code : codeParentMap.keySet())
				{
					String parent = codeParentMap.get( code );
					int parentCodeId = codeIdMap.getOrDefault( parent, 0 );
					int codeId = codeIdMap.get( code );
					
					updatePs.setInt( 1, parentCodeId );
					updatePs.setInt( 2, codeId );
					
					updatePs.executeUpdate();
				}
			}
		}
		catch( SQLException ex )
		{
		    BGLogger.error( ex );
		}
	}

	private Connection getBgbillingTestConnection()
	{
		Connection result = null;
		String url = "jdbc:mysql://192.168.184.245/bgbilling_test?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci&allowUrlInLocalInfile=true&zeroDateTimeBehavior=convertToNull&jdbcCompliantTruncation=false&elideSetAutoCommits=true&cachePrepStmts=true&useCursorFetch=true&queryTimeoutKillsConnection=true";
		String name = "root";
		String password = "";
		try
		{
			Class.forName( "com.mysql.cj.jdbc.Driver" );
			result = DriverManager.getConnection( url, name, password );
		}
		catch( ClassNotFoundException | SQLException ex )
		{
		    BGLogger.error( ex );
		}
		return result;
	}

	private Connection getBgbillingLocalConnection()
	{
		Connection result = null;
		String url = "jdbc:mysql://localhost:3306/bgbilling?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci&allowUrlInLocalInfile=true&zeroDateTimeBehavior=convertToNull&jdbcCompliantTruncation=false&elideSetAutoCommits=true&cachePrepStmts=true&useCursorFetch=true&queryTimeoutKillsConnection=true";
		String name = "root";
		String password = "";
		try
		{
			Class.forName( "com.mysql.cj.jdbc.Driver" );
			result = DriverManager.getConnection( url, name, password );
		}
		catch( ClassNotFoundException | SQLException ex )
		{
		    BGLogger.error( ex );
		}
		return result;
	}

	public static void main( String[] args )
	{
		GeocodesDest2Voice dest2Voice = new GeocodesDest2Voice();
		dest2Voice.process();
	}

}
