from telegram.ext.filters import filters
from telegram.ext.messagehandler import MessageHandler
from telegram import ParseMode
from telegram.ext import Updater, CommandHandler, ConversationHandler, CallbackQueryHandler
from telegram import InlineKeyboardButton, InlineKeyboardMarkup, ForceReply, ReplyKeyboardMarkup
import urllib.request
from threading import Thread
from random import randint
import os


def start(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		text = ('Hello dear user!')
		context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()

def help(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		text = ('Hello dear user!')
		context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()
# Download URL

def download_url(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		text = ('📝 <b>Send your file url</b>\n\n'
				'⚠️ If you dont send your url before 60s the operation will cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'GET_FILE_NAME'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()

def get_file_name(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		context.user_data['url'] = update.message.text
		try:
			response = urllib.request.urlopen(urllib.request.Request(context.user_data['url'], method='HEAD'))
		except:
			text = ('⁉️ Sorry! Invalid url!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
			return ConversationHandler.END
		if response.status != 200:
			text = ('⁉️ Sorry! Unable to download the url!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
			return ConversationHandler.END
		context.user_data['file_size'] = float(response.headers["Content-Length"]) / 2**20
		if context.user_data['file_size'] > 50:
			text = ('🚫 Sorry! File size is larger than 50MB!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
			return ConversationHandler.END
		text = ('✅ <b>All right! Send your new file name</b>\n'
				'💢 If you want to set file name automatically send /None command!\n\n'
				'⚠️ If you dont send your url before 60s the operation will cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'UPLOAD_FILE'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()

def upload_file(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		context.user_data['file_name'] = update.message.text
		if update.message.text == '/None':
			context.user_data['file_name'] = context.user_data['url'].split('/')[-1]
		else:
			context.user_data['file_name'] = update.message.text
		download_dir = path + 'temp/' + context.user_data['file_name']
		try:
			urllib.request.urlretrieve(context.user_data['url'], download_dir)
			context.bot.send_document(chat_id,
									document=open(download_dir, 'rb'),
									reply_markup=main_keyboard)
			os.remove(download_dir)
		except:
			text = ('🚫 Sorry! Try again later!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
		return ConversationHandler.END
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()
	
def timeout_operation(update, context):
	text = 'Timout ... 😴'
	update.message.reply_text(text, reply_markup=main_keyboard)
	return ConversationHandler.END

def cancel_operation(update, context):
	text = '⁉️ Operation Canceled!'
	update.message.reply_text(text, reply_markup=main_keyboard)
	return ConversationHandler.END

# Rename Files

def start_rename_files(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		text = ('📝 <b>Send your file with size less than 50MB</b>\n\n'
				'⚠️ If you dont send your file before 60s the operation will /cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'GET_FILE'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()

def get_file(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		context.user_data['file'] = update.message.document
		file_name = update.message.document.file_name
		if update.message.document.file_size > 20 * 2**20:
			update.message.reply_text('🚫 Sorry! File size larger than 20 MB!')
			return ConversationHandler.END
		file_extension = file_name.split('.')[-1]
		if len(file_extension) > 5:
			context.user_data['ext'] = 'None'
		else:
			context.user_data['ext'] = file_name.split('.')[-1]
		text = ('📝 <b>Send your new file name</b>\n'
				f'💢 If your name has not file extension we will add <b>.{context.user_data["ext"]}</b> to your file name\n\n'
				'⚠️ If you dont send your file before 60s the operation will /cancel!')
		context.bot.send_message(chat_id, text, reply_markup=ForceReply(), parse_mode=ParseMode.HTML)
		return 'GO_TO_RENAME'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()

def rename_file_and_upload(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		file_name = update.message.text
		if file_name.find('.') < 0 and context.user_data['ext']:
			file_name += ('.' + context.user_data['ext'])
		with open(f'{path}/temp/{file_name}', 'wb') as f:
			context.bot.get_file(context.user_data['file']).download(out = f)
		context.bot.send_document(chat_id, document=open(f'{path}/temp/{file_name}', 'rb'), reply_markup=main_keyboard)
		os.remove(f'{path}/temp/{file_name}')
		return ConversationHandler.END
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()	

# Sync Excel Files

def start_excel_sync(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		text = ('📝 <b>Send the firts excel file</b>\n\n'
				'⚠️ If you dont send your file before 60s the operation will /cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'GET_FIRST_EXCEL'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()	

def get_first_excel(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		context.user_data['first_excel'] = update.message.document
		if update.message.document.file_name.split('.')[-1] not in ['xlsx', 'xls']:
			text = ('🚫 Sorry! You can just send excel files!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
			return ConversationHandler.END
		text = ('📝 <b>Send the second excel file</b>\n\n'
				'⚠️ If you dont send your file before 60s the operation will /cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'GET_SECOND_EXCEL'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()	

def get_second_excel(update, context):
	def thread_function(update, context):
		chat_id = update.effective_chat.id
		context.user_data['second_excel'] = update.message.document
		if update.message.document.file_name.split('.')[-1] not in ['xlsx', 'xls']:
			text = ('🚫 Sorry! You can just send excel files!')
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard)
			return ConversationHandler.END
		text = ('📝 <b>Send the column name that you want merge on it</b>\n\n'
				'⚠️ If you dont send your name before 60s the operation will /cancel!')
		context.bot.send_message(chat_id, text, parse_mode=ParseMode.HTML)
		return 'UPLOAD_FILE'
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()	

def merge_and_upload(update, context):
	def thread_function(update, context):
		id = randint(1000000000, 9999999999)
		chat_id = update.effective_chat.id
		with open(f'{path}/temp/{id}_a.xlsx', 'wb') as f:
			context.bot.get_file(context.user_data['first_excel']).download(out=f)
		with open(f'{path}/temp/{id}_b.xlsx', 'wb') as f:
			context.bot.get_file(context.user_data['second_excel']).download(out=f)
		if merge_two_excel(id, update.message.document):
			context.bot.send_document(chat_id,
									document=open(f'{path}/temp/s_{id}.xlsx', 'rb'),
									reply_markup=main_keyboard)
		else:
			os.remove(f'{path}/temp/{id}_a.xlsx')
			os.remove(f'{path}/temp/{id}_b.xlsx')
			text = '⁉️ <b>ّSorry! Operation has been failed</b>\n\n'
			context.bot.send_message(chat_id, text, reply_markup=main_keyboard, parse_mode=ParseMode.HTML)
		return ConversationHandler.END
	thread = Thread(target=thread_function, args=(update,context))
	thread.start()	

def merge_two_excel(id, column_tag):
	try:
		import pandas as pd
		data_1 = pd.read_excel(f'{path}/temp/{id}_a.xlsx')
		data_2 = pd.read_excel(f'{path}/temp/{id}_b.xlsx')
		data = data_1.merge(data_2, on=column_tag, how='outer', suffixes=('_x', '_y'))
		data = (data.rename(columns = lambda x: x.replace('_x', '')).fillna(data.filter(regex='_y$')
				.rename(columns = lambda x: x.replace('_y', ''))).filter(regex=r'.*(?<!_y)$'))
		data.to_excel(f'{path}/temp/s_{id}.xlsx', index=False)
		os.remove(f'{path}/temp/{id}_a.xlsx')
		os.remove(f'{path}/temp/{id}_b.xlsx')
		return True
	except:
		return False

# Main function
if __name__ == '__main__':
	# Bot Configs
	TOKEN = '5931628423:AAEztLAlYWOs-RwpN6Bb0D0Xkqt2JvSNFQY'
	admin_id = 37087739
	updater = Updater(token=TOKEN)
	path = '/'.join(__file__.split('/')[:-1]) + '/'
	# Keyboards
	buttons = [['Download Url', 'Rename File'], ['Sync Excel']]
	main_keyboard = ReplyKeyboardMarkup(buttons, one_time_keyboard=True, resize_keyboard=True)
	# Command Handlers
	updater.dispatcher.add_handler(CommandHandler('start', start, filters.chat_type.private))
	# Conversation Handlers
	updater.dispatcher.add_handler(ConversationHandler(
		entry_points=[MessageHandler(filters.text('Download Url') , download_url)],
		states={
			'GET_FILE_NAME': [MessageHandler(filters.text, get_file_name)],
			'UPLOAD_FILE': [MessageHandler(filters.text, upload_file)],
			ConversationHandler.TIMEOUT: [MessageHandler(filters.all, timeout_operation)]
		},
		fallbacks=[CommandHandler('cancel', cancel_operation)],
		conversation_timeout=30,
	))
	updater.dispatcher.add_handler(ConversationHandler(
		entry_points=[MessageHandler(filters.text('Rename File') , start_rename_files)],
		states={
			'GET_FILE': [MessageHandler(filters.document, get_file)],
			'GO_TO_RENAME': [MessageHandler(filters.text, rename_file_and_upload)],
			ConversationHandler.TIMEOUT: [MessageHandler(filters.all, timeout_operation)]
		},
		fallbacks=[CommandHandler('cancel', cancel_operation)],
		conversation_timeout=30,
	))
	updater.dispatcher.add_handler(ConversationHandler(
		entry_points=[MessageHandler(filters.text('Sync Excel'), start_excel_sync)],
		states={
			'GET_FIRST_EXCEL': [MessageHandler(filters.document, get_first_excel)],
			'GET_SECOND_EXCEL': [MessageHandler(filters.document, get_second_excel)],
			'UPLOAD_FILE': [MessageHandler(filters.text, merge_and_upload)],
			ConversationHandler.TIMEOUT: [MessageHandler(filters.all, timeout_operation)]
		},
		fallbacks=[CommandHandler('cancel', cancel_operation)],
		conversation_timeout=120,
	))
	# Message Handlers	
	updater.dispatcher.add_handler(MessageHandler(filters.text('Help') & filters.chat_type.private, help))
	#updater.dispatcher.add_handler(MessageHandler(filters.text & filters.chat_type.private, other))
	# Start Bot
	updater.start_polling()
	print('Bot is started ...')
	updater.idle()