import os
import sys
import xlrd

if __name__ == "__main__":
	# set your django setting module here
	os.environ.setdefault("DJANGO_SETTINGS_MODULE", "tres.settings") 
	from django.contrib.auth.models import User
	from userProfile.models import UserProfile
	from mezzanine.blog.models import BlogParentCategory, BlogCategory
	from django.template.defaultfilters import slugify

	CATEGORIES_XLS_PATH = 'assets/categories/xls/Wishradio - Categories.xlsx'
	PARENT_CATEGORY_INDEX = 1
	SUB_CATEGORIES_INDEX = 2

	workbook = xlrd.open_workbook(CATEGORIES_XLS_PATH)

	def create_categories(sheet, start_index, end_index):
		"""
			First row entry of every section will contain parent_category name.
		"""
		categories = sheet.row_values(start_index)
		parent_category = None
		parent_category_list = BlogParentCategory.objects.filter(title=categories[PARENT_CATEGORY_INDEX])
		if len(parent_category_list) == 0:
			parent_category_string = categories[PARENT_CATEGORY_INDEX]
			parent_category_string = parent_category_string.replace('/', '')
			parent_category = BlogParentCategory(slug=slugify(parent_category_string))
			parent_category.title = parent_category_string
			parent_category.save()
		else:
			parent_category = parent_category_list[0]

		sub_categories = sheet.col_values(SUB_CATEGORIES_INDEX, start_rowx=start_index, end_rowx=end_index)

		for sub_category in sub_categories:
			if sub_category == '':
				sub_category = parent_category.title
				
			sub_category = sub_category.replace('/','')
			sub_category_list = BlogCategory.objects.filter(title=sub_category)
			if len(sub_category_list) == 0:
				sub_category_obj = BlogCategory(slug=slugify(sub_category), title=sub_category)
				sub_category_obj.save()
				sub_category_obj.parent_category.add(parent_category)
			else:
				sub_category_obj = sub_category_list[0]
				sub_category_obj.parent_category.add(parent_category)
				print sub_category_obj.title, "is now listed under: ", sub_category_obj.parent_category.all(), "\n"
				#if sub_category_obj.parent_category.title == parent_category.title:
				#	print parent_category, " already has a sub category: ", sub_category
				#else:
				#	sub_category_obj.parent_category = parent_category
				# sub_category_obj.save()


	sheets = workbook.sheets()
	print '------------------------------------------------------------------------------------------'
	print 'getting sheets...'
	print 'sheets found: ', [sheet.name for sheet in sheets]
	print '------------------------------------------------------------------------------------------'
	for sheet in sheets:
		column_0_values = sheet.col_values(colx=0)

		start_tag = 1
		end_tag = start_tag + 1
		print "Processing sheet: ", sheet.name

		while(start_tag in column_0_values):
			start_index = column_0_values.index(start_tag)
			if end_tag in column_0_values:
				end_index = column_0_values.index(end_tag)
				create_categories(sheet, start_index, end_index )
				start_tag = end_tag
				end_tag = start_tag + 1
			else:
				create_categories(sheet, start_index, sheet.nrows)
				break

		






