import logging
from collections import namedtuple

from parallels.hsphere_migrator.billing import data_model
from parallels.hsphere_migrator.billing import source_data_model
from parallels.hsphere_migrator.billing.utils import mask_credit_card_number
from parallels.common.checking import Problem
from parallels.utils import cached

ClientToFetch = namedtuple('ClientToFetch', ('login', 'subscriptions'))

class BillingBackupAgent:
	logger = logging.getLogger(__name__)

	def __init__(self, connection_cursor, report):
		self.conn = connection_cursor
		self.report = report

	def make_backup(self, clients):
		"""
		clients - list of ClientToFetch objects
		"""
		target_model = data_model.Model(accounts=[])
		source_model = source_data_model.Model(users=[])
		credit_card_encryption = self._is_credit_card_encryption_enabled()
		if credit_card_encryption:
			self.report.add_issue(
				Problem(
					'credit_card_encryption_enabled_in_hsphere',
					Problem.ERROR,
					u"Encryption of credit card data is enabled in H-Sphere, which makes it impossible to transfer credit card data."
				),
				u"Please disable the encryption of credit card data in H-Sphere.\n"
				u"You can disable encryption by logging in as administrator and going to Settings > Payment Settings > CC Encryption page."
			)

		for client in clients:
			customer_report = self.report.subtarget('Customer', client.login)
			target_account = data_model.Account(
				login=client.login,
				balance=self._get_balance(client.login),
				credit_cards=self._get_credit_cards(client.login, customer_report) if not credit_card_encryption else [],
				hosting_subscriptions=[],
				domain_subscriptions=[],
				ssl_subscriptions=[],
			)
			target_model.accounts.append(target_account)

			source_user = source_data_model.User(login=client.login, accounts=[])
			source_model.users.append(source_user)

			for subscription in client.subscriptions:
				account_id = self._get_hsphere_account_id(subscription)

				last_bill_date, next_bill_date = self._get_account_billing_dates(account_id)
				target_account.hosting_subscriptions.append(data_model.HostingSubscription(
					name=subscription,
					billing_period=self._get_account_billing_period(account_id),
					last_bill_date=last_bill_date,
					next_bill_date=next_bill_date,
				))

				domain_subscriptions = self._get_account_domain_subscriptions(account_id)
				target_account.domain_subscriptions.extend(domain_subscriptions)

				ssl_subscriptions = self._get_comodo_ssl(account_id, customer_report)
				target_account.ssl_subscriptions.extend(ssl_subscriptions)

				source_account = source_data_model.Account(
					account_id=account_id,
					is_active=self._is_account_active(account_id),
					main_domain_name=subscription,
					ssl_certificates=[source_data_model.SSLCertificate(domain_name=crt.domain_name) for crt in ssl_subscriptions],
					registered_domains=[source_data_model.RegisteredDomain(domain_name=d.name) for d in domain_subscriptions]
				)
				source_user.accounts.append(source_account)

		return target_model, source_model

	def _get_hsphere_account_id(self, subscription):
		self.conn.execute("""
			SELECT pc_domain.account_id
			FROM parent_child pc_domain
			JOIN domains d ON d.id = pc_domain.child_id
			WHERE d.name = '%s'
		""" % subscription)
		(account_id,) = self.conn.fetchone()
		return account_id

	def _is_account_active(self, account_id):
		self.conn.execute("""
			SELECT suspended IS NULL
			FROM accounts
			WHERE id = %s
		""" % account_id)
		(active,) = self.conn.fetchone()
		return active

	def _get_account_domain_subscriptions(self, account_id):
		domain_subscriptions = []
		query = """
			SELECT parent_domains.name, opensrs.last_payed, opensrs.period, opensrs.renew
				FROM opensrs
				JOIN parent_child opensrs_pc
					ON opensrs.id = opensrs_pc.child_id
				JOIN domains parent_domains
					ON parent_domains.id = opensrs_pc.parent_id
				WHERE
					opensrs_pc.account_id = %s
					AND
					opensrs_pc.child_type IN (14,	-- opensrs, Domain registration
								  68	-- domain_transfer
					)
		""" % (account_id,)
		self.conn.execute(query)
		for row in self.conn.fetchall():
			domain_name, last_payed, period, renew = row
			next_bill_date = last_payed.replace(year=last_payed.year+period)
			domain_contact_info = self._get_domain_contact_info(domain_name)
			hsphere_global_contact_info = self._get_global_hsphere_contact_info()
			hsphere_extra = self._get_domain_extra_info(domain_name)
			registrar = self._get_domain_registrar(domain_name)
			if registrar is not None:
				domain_subscriptions.append(data_model.DomainSubscription(
					name=domain_name,
					subscription_period=int(renew),
					start_date=int(last_payed.strftime('%s')), # H-Sphere has no info about start date, so we set it equal to last payed date
					last_bill_date=int(last_payed.strftime('%s')),
					next_bill_date=int(next_bill_date.strftime('%s')),
					users=data_model.DomainSubscriptionUsers(
						# H-Sphere passes contact info as owner (registrant) and admin (administrative) contacts
						owner=domain_contact_info,
						admin=domain_contact_info,
						# H-Sphere passes H-Sphere global admin account information as billing (auxiliary billing) and tech (technical) contacts
						billing=hsphere_global_contact_info,
						tech=hsphere_global_contact_info,
						# Account billing information is not passed to registrar
					),
					nameservers=self._get_domain_nameservers(domain_name),
					registrar=registrar,
					extra=self._convert_domain_extra_info(hsphere_extra, registrar),
				))
			else:
				# we are not able to migrate domains registered by other 
				# registrars, not OpenSRS or Enom, so silently skip them
				pass

		return domain_subscriptions

	def _get_domain_extra_info(self, domain, ):
		query = """
			SELECT dei.name, dei.value
			FROM domain_extra_info dei
			JOIN domain_contact_info dci ON dci.id = dei.ci_id
			JOIN parent_child pc_dci ON pc_dci.child_id = dci.id and pc_dci.child_type = 5	-- contact_info
			JOIN domains d ON d.id = pc_dci.parent_id
			WHERE d.name = '%s'
		""" % domain
		self.conn.execute(query)
		return { name: value for name, value in self.conn.fetchall() }

	def _convert_domain_extra_info(self, extra, registrar):
		converted_dict = {}
		if registrar == 'enom':
			# return the parameters that make sense for ENOM
			converted_dict = self._convert_extra_enom(extra)
		elif registrar == 'opensrs':
			# return the parameters that make sense for OpenSRS
			converted_dict = self._convert_extra_opensrs(extra)
		else:	# email or alldomains, or maybe some custom registrars - we don't transfer the extra info from them
			pass
		return [ (key, value) for key, value in converted_dict.iteritems() ]

	# these mappings are common for ENOM and OpenSRS plugins. The enumerations are different, but the values are the same.
	us_nexus_category_mapping = {
		'C11': 0,
		'C12': 1,
		'C21': 2,
		'C31': 3,
		'C32': 4,
	}

	us_app_purpose_mapping = {
		'P1': 0,
		'P2': 1,
		'P3': 2,
		'P4': 3,
		'P5': 4,
	}

	ca_legal_type_mapping = {
		'CCO': 1,
		'CCT': 2,
		'RES': 3,
		'GOV': 4,
		'EDU': 5,
		'ASS': 6,
		'HOP': 7,
		'PRT': 8,
		'TDM': 9,
		'TRD': 10,
		'PLT': 11,
		'LAW': 12,
		'TRS': 13,
		'ABO': 14,
		'INB': 15,
		'LGR': 16,
		'OMK': 17,
		'MAJ': 18,
	}

	def _convert_extra_enom(self, extra):
		enom_language_mapping = {
			'en': 1,
			'fr': 2,
		}

		uk_legal_type_mapping = {
			'IND': 1,
			'Non_UK': 2,
			'LTD': 3,
			'PLC': 4,
			'PTNR': 5,
			'LLP': 6,
			'STRA': 7,
			'RCHAR': 8,
			'ip': 9,
			'SCH': 10,
			'GOV': 11,
			'CRC': 12,
			'STAT': 13,
			'OTHER': 14,
			'FCORP': 15,
			'FOTHER': 16,
		}

		result = {}
		tld = extra.get('tld')
		if tld == 'us':
			result['USAppPurpose'] = str(self.us_app_purpose_mapping.get(extra.get("app_purpose")))
			result['USNexusCategory'] = str(self.us_nexus_category_mapping.get(extra.get('nexus_category')))
		elif tld == 'ca':
			result['CALegalType'] = str(self.ca_legal_type_mapping.get(extra.get("legal_type")))
			result['CALanguage'] = str(enom_language_mapping.get(extra.get("lang_pref")))
		elif tld in ['co.uk', 'org.uk']:
			result['UKLegalType'] = str(uk_legal_type_mapping.get(extra.get("uk_legal_type")))
			result['UKRegCoNo']  = extra.get("uk_reg_co_no")
		else:
			pass # have no information needed for the other PPAB tlds (be, gs, eu, at, am, nl, it, fm, net.nz, co.nz, org.nz, com.mx, jp, de, ac, io)
		return result

	def _convert_extra_opensrs(self, extra):
		result = {
			'CustomTechContact': True,
		}
		tld = extra.get("tld")
		if tld == 'us':
			result['US_AppPurpose'] = str(self.us_app_purpose_mapping.get(extra.get("app_purpose")))
			result['US_Category'] = str(self.us_nexus_category_mapping.get(extra.get('nexus_category')))
		elif tld == 'ca':
			result['CA_LegalType'] = str(self.ca_legal_type_mapping.get(extra.get("legal_type")))
			result['CA_IsATrademark'] = extra.get('isa_trademark')	# BOOLEAN in PPAB API has type string and values 0 and 1. Just the same as in H-Sphere
		elif tld in ['de', 'eu']:
			pass # have no information needed for PPAB de and eu tlds
		else:
			pass # have no information needed for the other PPAB tlds (be, it, asia, com.au, net.au, fr, pro) either

		return result
		

	def _get_domain_registrar(self, domain):
		"""Get registrar short name ('enom' or 'opensrs') by domain name
		
		Gets registrar by the lowermost possible zone, so for example
		if Enom has '.uk', and OpenSRS has 'edu.uk', and domain is 'u.edu.uk',
		this function returns registrar for 'edu.uk' which is OpenSRS

		Return None in case we have not found registrar for domain,
		for example domain is registered by some different from Enom
		or OpenSRS registrars"""

		mapping = {
			'Enom.com Registrar': 'enom',
			'OpenSRS Registrar': 'opensrs',
		}
		for tld in self._iter_domain_zones(domain):
			tld = tld.lower()
			query = """
				SELECT description
				FROM managed_entity me
				JOIN active_managed_entity ame ON ame.id = me.id
				WHERE ame.mkey = '%s'
			""" % tld
			self.conn.execute(query)
			if self.conn.rowcount > 0:
				description = self.conn.fetchone()[0]
				if description in mapping:
					return mapping[description]

		return None # nothing apropriate found

	@staticmethod
	def _iter_domain_zones(domain):
		"""Iter domain zones from the lowermost to the topmost
		
		For example, for domain 'a.b.c.tld' function will yield
		'b.c.tld', 'c.tld', 'tld'"""

		while True:
			index = domain.find('.')
			if index == -1:
				return
			domain = domain[index + 1:]
			yield domain

	def _get_domain_contact_info(self, domain):
		query = """
			SELECT first_name, last_name, address1, address2, city, state, country, postal_code, phone, fax, email
				FROM domains
				JOIN parent_child
					ON parent_child.parent_id = domains.id
				JOIN domain_contact_info
					ON domain_contact_info.id = parent_child.child_id
				WHERE
					domains.name = '%s'
					AND
					parent_child.child_type = 5
		""" % (domain,) # 5 means contact info in H-Sphere
		self.conn.execute(query)
		row = self.conn.fetchone()
		if row is None or len(row) == 0:
			return None
		else:
			first_name, last_name, address1, address2, city, state, country, zip_code, phone, fax, email = row

			return data_model.DomainSubscriptionUser(
				first_name=first_name,
				last_name=last_name,
				email=email,
				address1=address1,
				address2=address2,
				city=city,
				state=state,
				zip_code=zip_code,
				country=country.lower(),
				# In H-Sphere phone number is stored as a single string. We do not split it over multiple fields
				phone=data_model.PhoneNumber(country_code="", area_code="", number=phone, extension=""),
				# In H-Sphere fax number is stored as a single string. We do not split it over multiple fields
				fax=data_model.PhoneNumber(country_code="", area_code="", number=fax, extension=""),
			)

	@cached
	def _get_global_hsphere_contact_info(self):
		query = """
			SELECT name, value
				FROM settings
			WHERE
				reseller_id = 1
				AND
				name IN (
					'ofname', 'olname', 'email', 'address', 'city', 'state', 'zip', 'country', 'phone', 'fax'
				)
		"""
		self.conn.execute(query)
		contact_info = {}
		for row in self.conn.fetchall():
			name, value = row
			contact_info[name] = value
		return data_model.DomainSubscriptionUser(
			first_name=contact_info.get('ofname', ''),
			last_name=contact_info.get('olname', ''),
			email=contact_info.get('email', ''),
			address1=contact_info.get('address', ''),
			address2='',
			city=contact_info.get('city', ''),
			state=contact_info.get('state', ''),
			zip_code=contact_info.get('zip', ''),
			country=contact_info.get('country', '').lower(),
			# In H-Sphere phone number is stored as a single string. We do not split it over multiple fields
			phone=data_model.PhoneNumber(country_code="", area_code="", number=contact_info.get('phone'), extension=""),
			# In H-Sphere fax number is stored as a single string. We do not split it over multiple fields.
			# In H-Sphere, fax is optional for admin and not specifiable for reseller. In PPAB, fax can be left empty.
			fax=data_model.PhoneNumber(country_code="", area_code="", number=contact_info.get('fax', ''), extension=""),
		)

	def _get_domain_nameservers(self, domain):
		# 3001 is DNS zone resource id
		query = """
			SELECT ns1.name, ns2.name, ns3.name
				FROM domains
				JOIN parent_child
					ON
						parent_child.parent_id = domains.id
						AND
						parent_child.child_type = 3001
				JOIN dns_zones
					ON parent_child.child_id = dns_zones.id
				LEFT JOIN l_server ns1
					ON ns1.id = dns_zones.master
				LEFT JOIN l_server ns2
					ON ns2.id = dns_zones.slave1
				LEFT JOIN l_server ns3
					ON ns3.id = dns_zones.slave2
				WHERE
					domains.name = '%s'
		""" % (domain,)
		self.conn.execute(query)
		row = self.conn.fetchone()
		if row is None or len(row) == 0:
			return []
		else:
			return [ns for ns in row if ns is not None and ns != '']

	def _get_account_billing_period(self, account_id):
		def get_period_param(account_id, param_name):
			query = """
				SELECT plan_value.value
					FROM plan_value
					JOIN accounts
						ON accounts.plan_id = plan_value.plan_id
				WHERE
					accounts.id = %s
					AND
					plan_value.name like '_PERIOD_%s_' || accounts.period_id::text
			""" % (account_id, param_name,)
			self.conn.execute(query)
			row = self.conn.fetchone()
			if row is None or len(row) == 0:
				return None
			else:
				return row[0]

		period_size = get_period_param(account_id, 'SIZE')
		period_type = get_period_param(account_id, 'TYPE')
		period_type_map = {
			'DAY': data_model.BillingPeriodType.DAY,
			'MONTH': data_model.BillingPeriodType.MONTH,
			'YEAR': data_model.BillingPeriodType.YEAR,
		}
		if period_type is None or period_size is None: # billing period is not defined in H-Sphere database - return default one month billing period
			return data_model.BillingPeriod(data_model.BillingPeriodType.MONTH, 1)
		else:
			if period_type in period_type_map:
				return data_model.BillingPeriod(period_type_map[period_type], int(period_size))
			elif period_type == 'WEEK':
				return data_model.BillingPeriod(data_model.BillingPeriodType.DAY, int(period_size) * 7) # PPAB does not support week periods, convert weeks to days
			else: # unknown period type - return default one month billing period
				return data_model.BillingPeriod(data_model.BillingPeriodType.MONTH, 1)

	def _get_account_billing_dates(self, account_id):
		query = """
			SELECT pc_account.p_begin, accounts.p_end
				FROM parent_child pc_account
				JOIN accounts
					ON accounts.id = pc_account.child_id
				WHERE accounts.id = %s
		""" % (account_id,)
		self.conn.execute(query)
		row = self.conn.fetchone()
		if row is None or len(row) == 0:
			return (0, 0)

		begin, end = row
		return int(begin.strftime('%s')), int(end.strftime('%s'))

	def _is_credit_card_encryption_enabled(self):
		query = "SELECT value FROM settings WHERE name = 'cc_encryption'"
		self.conn.execute(query)
		row = self.conn.fetchone()
		if row is None or len(row) == 0:
			return False
		return row[0] == 'ON'

	def _get_balance(self, client_login):
		query = """
			SELECT SUM(balance) FROM users
				JOIN user_account
					ON users.id = user_account.user_id
				JOIN accounts
					ON accounts.id = user_account.account_id
				JOIN balance_credit
					ON balance_credit.id = accounts.id
				WHERE users.username = '%s';
		""" % (client_login,)
		self.conn.execute(query)
		balance, = self.conn.fetchone()
		return float(balance)

	def _get_credit_cards(self, client_login, report):
		credit_cards = []
		query = """
			SELECT
				credit_card.id,
				credit_card.type,
				credit_card.cc_number,
				credit_card.name,
				credit_card.exp_year, credit_card.exp_month,
				credit_card.start_year, credit_card.start_month,
				credit_card.issueno,

				billing_info.address1,
				billing_info.address2,
				billing_info.city,
				billing_info.state,
				billing_info.postal_code,
				billing_info.country,
				billing_info.phone,
				billing_info.email
			FROM users
				JOIN user_billing_infos
					ON users.id = user_billing_infos.user_id
				JOIN credit_card
					ON credit_card.id = user_billing_infos.billing_info_id
				JOIN billing_info
					ON billing_info.id = user_billing_infos.billing_info_id
			WHERE users.username = '%s'
		""" % (client_login,)
		self.conn.execute(query)

		def format_date(year, month):
			if all([
				year is not None,
				year != '',
				month is not None,
				month != ''
			]):
				if len(year) == 4:
					year = year[2:]
				return '%s/%s' % (month, year)
			else:
				return ''

		for row in self.conn.fetchall():
			(
				card_id,

				# Card info
				pay_system,
				card_number,
				card_holder_name,
				expiration_year,
				expiration_month,
				start_year,
				start_month,
				issue_number,

				# Contact info
				address1,
				address2,
				city,
				state,
				zip_code,
				country,
				phone,
				email
			) = row

			ppab_pay_system = self._map_credit_card_pay_system(pay_system)
			credit_card_usage = self._get_credit_card_usage(card_id)
			if ppab_pay_system is None:
				if credit_card_usage > 0:
					report.add_issue(
						Problem(
							'pay_system_not_supported',
							Problem.WARNING,
							u"The payment system '%s' used by the customer's account is not supported by PPAB. The credit card data for the card '%s' of that payment system will not be migrated." % (
								self._get_credit_card_pay_system_name(pay_system),
								mask_credit_card_number(card_number)
							),
						),
						u"Configure the payment settings for this customer manually after transfer."
					)
				else:
					pass # silently skip unused credit cards

				continue

			credit_card = data_model.CreditCard(
				# 1) Credit card information
				pay_system=ppab_pay_system,
				card_number=card_number,
				card_holder_name=card_holder_name,
				cvv='', # CVV is always empty, as H-Sphere does not store CVV data, H-Sphere uses it only for initial credit card validation on customer sign-up
				expiration_date=format_date(expiration_year, expiration_month),
				start_date=format_date(start_year, start_month),
				issue_number=issue_number if issue_number is not None else '',

				# 2) Contact information
				address1=address1,
				address2=address2,
				city=city,
				state=state,
				zip_code=zip_code,
				country=country.lower(),
				email=email,
				# In H-Sphere phone number is stored as a single string. We do not split it over multiple fields
				phone=data_model.PhoneNumber(country_code="", area_code="", number=phone, extension=""),
				# In H-Sphere there is no information about fax
				fax=data_model.PhoneNumber(country_code="", area_code="", number="", extension=""),

				# 3) Billing configuration
				use_for_auto_payments=0
			)
			credit_cards.append({'usage': credit_card_usage, 'credit_card': credit_card})

		credit_cards.sort(key=lambda cc: cc['usage'], reverse=True)

		if len(credit_cards) == 0:
			return []
		else:
			for cc in credit_cards[1:]:
				if cc['usage'] > 0:
					report.add_issue(
						Problem(
							'multiple_credit_cards_are_not_supported',
							Problem.WARNING,
							u"Multiple active credit cards are not supported. The card '%s' will not be active; the most used card '%s' will be used for all transactions." % (
								mask_credit_card_number(credit_cards[0]['credit_card'].card_number),
								mask_credit_card_number(cc['credit_card'].card_number)
							),
						),
						u"Review the payment settings for this customer after transfer and correct them if necessary."
					)
			return (
				# PPAB support only single active credit card per-customer (not per-subscription, as in H-Sphere), so
				[credit_cards[0]['credit_card']._replace(use_for_auto_payments=1)] + # the 1st credit card by usage is considered as main credit card and will be used for all payments in PPAB
				[cc['credit_card'] for cc in credit_cards[1:]] # all the others will be just imported
			)

	def _get_credit_card_usage(self, credit_card_id):
		query = "SELECT COUNT(*) FROM accounts WHERE bi_id = %s" % credit_card_id
		self.conn.execute(query)
		return int(self.conn.fetchone()[0])

	@staticmethod
	def _map_credit_card_pay_system(hsphere_pay_system):
		ps = data_model.PaySystem
		mapping = {
			'MC': ps.MASTER_CARD,
			'DISC': ps.DISCOVER,
			'AX': ps.AMERICAN_EXPRESS,
			'ELECTRON': ps.VISA_ELECTRON,
			'DINERS': ps.DINERS_CLUB,
			'JCB': ps.JCB,
			'VISA': ps.VISA,
			'SWITCH': ps.MAESTRO,
		}
		return mapping.get(hsphere_pay_system)

	def _get_credit_card_pay_system_name(self, hsphere_pay_system):
		query = "SELECT name FROM cc_brands WHERE name_sh = '%s'" % hsphere_pay_system
		self.conn.execute(query)
		row = self.conn.fetchone()
		if row is None or len(row) == 0:
			return hsphere_pay_system
		name = row[0]
		if name is None or name == '':
			return hsphere_pay_system
		else:
			return name

	def _get_comodo_ssl(self, account_id, report):
		product_code_to_model_value = {
			'24': data_model.SSLProducts.INSTANT,
			'34': data_model.SSLProducts.INSTANT_PRO,
			'7': data_model.SSLProducts.PREMIUMO,
			'62': data_model.SSLProducts.ELITE,
			'63': data_model.SSLProducts.GOLD,
			'64': data_model.SSLProducts.PLATINUM,
			'287': data_model.SSLProducts.POSITIVE
		}
		def get_model_product(hsphere_ssl_products):
			for product in hsphere_ssl_products:
				if product in product_code_to_model_value:
					return product_code_to_model_value[product]
			return None

		ssl_subscriptions = []

		ssl_query_template = """
			SELECT cs.id, d.name, cs.pri, cs.cert, cs.period, cs.product
			FROM comodo_ssl cs
			JOIN parent_child pc_comodo ON pc_comodo.child_id = cs.id AND pc_comodo.child_type = 152	-- Comodo SSL
			JOIN parent_child pc_hosting ON pc_hosting.child_id = pc_comodo.parent_id AND pc_hosting.child_type = 9	-- hosting 
			JOIN domains d ON d.id = pc_hosting.parent_id
			WHERE pc_comodo.account_id = {account_id}
		"""

		accounts_query = """
			SELECT ci.email, ci.address1, ci.address2
			FROM contact_info ci
			JOIN accounts a ON a.ci_id = ci.id
			WHERE a.id = %s
		""" % account_id

		self.conn.execute(accounts_query)
		for (email, address1, address2) in self.conn.fetchall():
			self.conn.execute(ssl_query_template.format(account_id=account_id))
			for (cs_id, site_name, pkey, certificate, period, product) in self.conn.fetchall():
				if certificate is None:
					report.add_issue(
						Problem(
							'ssl_certificate_absent',
							Problem.WARNING,
							u"The purchasing process was not completed for the Comodo SSL certificate for the site %s. This certificate was not transferred." % site_name
						),
						u"Transfer this certificate to PPA Billing manually."
					)
					continue

				self.conn.execute("SELECT MAX(created) FROM bill_entry WHERE rid = %s" % cs_id)
				row = self.conn.fetchone()
				if row is None:
					last_bill_date = 0
				else:
					last_bill_date = int(row[0].strftime('%s'))

				model_product = get_model_product(product.split(','))
				assert model_product is not None, "H-Sphere data inconsistency: the SSL certificate %s is not linked to an SSL certificate product. The non-certificate SSL products it is linked to: %s" % (cs_id, product)

				ssl_subscriptions.append(data_model.SSLSubscription(
					domain_name=site_name,
					pkey=pkey,
					certificate=certificate,
					approver_email=email,	# from account info by default, can be determined from certificate
					address1=address1,	# from account info by default, can be determined from certificate
					address2=address2,	# from account info by default, can be determined from certificate
					postal_code="",		# empty by default, can be determined from certificate

					period=int(period),
					product=model_product,
					last_bill_date=last_bill_date,


					csr=None,
					csr_attrs=None,
					start_date=None,
					expiration_date=None
				))
		return ssl_subscriptions
