class Easydb4MigrationPlugin.EthZuerich extends Easydb4MigrationPlugin
	name: ->
		"eth-zuerich"

	chunkSize: 10000

	payload: (filename, json, update, store_file) ->
		# if filename == "basetype_user_ldap_sso.json"
		# 	return @payloadGetty(store_file)

		if filename.startsWith("Bilder-")
			return @payloadBilder(filename, json, update, store_file)

		return

	payloadBilder: (filename, json, update, store_file) ->
		for obj in json.objects
			for kuenstler in obj.bilder["_nested:bilder__bilder__kuenstler_getty"] or []
				if not kuenstler["lookup:lk_person_id"]
					continue

				ref = kuenstler["lookup:lk_person_id"].__not_found_easydb4_reference

				lookup =
					_objecttype: "personen"
					_mask: "_all_fields"
					personen:
						"lookup:_id":
							easydb4_reference: ref

				kuenstler["lk_person_id"] = lookup
				delete(kuenstler["lookup:lk_person_id"])


			for standort in obj.bilder["_nested:bilder__bilder__standort_getty"] or []
				if not standort["lookup:lk_ort_id"]
					continue

				ref = standort["lookup:lk_ort_id"].__not_found_easydb4_reference

				lookup =
					_objecttype: "orte"
					_mask: "_all_fields"
					orte:
						"lookup:_id":
							easydb4_reference: ref

				standort["lk_ort_id"] = lookup
				delete(standort["lookup:lk_ort_id"])


			for ort in obj.bilder["_nested:bilder__bilder__ort_getty"] or []
				if not ort["lookup:lk_ort_id"]
					continue

				ref = ort["lookup:lk_ort_id"].__not_found_easydb4_reference

				lookup =
					_objecttype: "orte"
					_mask: "_all_fields"
					orte:
						"lookup:_id":
							easydb4_reference: ref

				ort["lk_ort_id"] = lookup
				delete(ort["lookup:lk_ort_id"])


		return

	payloadGetty: (store_file) ->

		dfr = new CUI.Deferred()

		@_migration.query("""SELECT COUNT(id) AS count FROM "source.public.getty_subject" WHERE lk_easydb_vocabulary_id = 1""")
		.fail(dfr.reject)
		.done (result) =>
			orte_count = result.rows[0].count
			@_migration.query("""SELECT COUNT(id) AS count FROM "source.public.getty_subject" WHERE lk_easydb_vocabulary_id = 2""")
			.fail(dfr.reject)
			.done (result) =>
				personen_count = result.rows[0].count
				if false # test?
					personen_count = 1000
					orte_count = 1000

				orte_count = 0

				console.debug("Orte: ", orte_count, "Personen:", personen_count)

				@export_orte(store_file, orte_count)
				.fail(dfr.reject)
				.done =>
					@export_personen(store_file, personen_count)
					.done(dfr.resolve)
					.fail(dfr.reject)

		return dfr.promise()



	export_personen: (store_file, personen_limit) ->
		faked_arr = []
		faked_arr[personen_limit-1] = true


		update_payloads = []

		promise = CUI.chunkWork.call(@,
			items: faked_arr
			chunk_size: @chunkSize
			call: (faked_items, offset, len) =>
				limit = faked_items.length

				range = (offset+1)+"-"+Math.min(limit+offset, len)
				splash_text = "getty_subject/personen "+range+"/"+len
				ez5.splash.show("easydb4migration.progress", payload: splash_text)

				dfr = new CUI.Deferred()

				@_migration.query(@sql(2, offset, limit))
				.fail(dfr.reject)
				.done (result) =>
					payload = import_type: "db", objecttype: "personen", objects: []

					current_id = null

					for row in result.rows
						if row.digitale_diathek_id != current_id
							current_id = row.digitale_diathek_id
							current_term_id = null
							first_term_id = null
							current_bio_id = null

							obj =
								_objecttype: "personen"
								_mask: "_all_fields"
								personen:
									_version: 1
									digitale_diathek_id: current_id
									easydb4_reference: row.easydb4_reference
									getty_id: row.getty_id
									gnd_id: row.gnd_id
									viaf_id: row.viaf_id
									preferred_term:
										"en-US": row.easydb_getty_term_preferred_text
									"_nested:personen__terms": []
									"_nested:personen__biographies": []

							payload.objects.push(obj)

						if current_term_id != row.easydb_term_id
							if not first_term_id
								first_term_id = row.easydb_term_id

							current_term_id = row.easydb_term_id
							if row.term_preferred # or not current_term_id
								obj.personen.preferred_term =
									"en-US": row.term_text

								obj.personen.getty_link = @getty_link("ulan", row.getty_id, row.term_text)
								obj.personen.gnd_link = @gnd_link(row.gnd_id, row.term_text)

							obj.personen["_nested:personen__terms"].push
								term: row.term_text
								_display_order: row._display_order
								preferred: if row.term_preferred then true else false
								language: @lang_link(row.term_getty_language)

							obj.personen["_nested:personen__terms"].sort (a, b) ->
								CUI.util.compareIndex(a._display_order, b._display_order)

						if current_bio_id != row.easydb_bio_id and row.easydb_term_id == first_term_id
							current_bio_id = row.easydb_bio_id
							if row.bio_preferred or not current_bio_id
								obj.personen.preferred_biography =
									"en-US": row.biography

							obj.personen["_nested:personen__biographies"].push
								biography: row.biography
								preferred: if row.bio_preferred then true else false

						if row.easydb4_reference_birth_place
							obj.personen.birth_place =
								_objecttype: "orte"
								_mask: "_all_fields"
								_depth: row._depth
								orte:
									"lookup:_id":
										easydb4_reference: row.easydb4_reference_birth_place

						if row.easydb4_reference_death_place
							obj.personen.death_place =
								_objecttype: "orte"
								_mask: "_all_fields"
								orte:
									"lookup:_id":
										easydb4_reference: row.easydb4_reference_death_place

					store_file("personen-"+range+".json", payload)
					.done(dfr.resolve)
					.fail(dfr.reject)
					return

				return dfr.promise()

		)
		.done =>
			console.debug("getty_subject/personen done.")

		return promise


	export_orte: (store_file, orte_limit) ->
		faked_arr = []
		faked_arr[orte_limit-1] = true

		promise = CUI.chunkWork.call(@,
			items: faked_arr
			chunk_size: @chunkSize
			call: (faked_items, offset, len) =>
				limit = faked_items.length

				range = (offset+1)+"-"+Math.min(limit+offset, len)
				splash_text = "getty_subject/orte "+range+"/"+len
				ez5.splash.show("easydb4migration.progress", payload: splash_text)

				dfr = new CUI.Deferred()

				@_migration.query(@sql(1, offset, limit))
				.fail(dfr.reject)
				.done (result) =>
					current_id = null
					payloads = []

					for row in result.rows
						if row.digitale_diathek_id != current_id
							current_id = row.digitale_diathek_id
							current_term_id = null

							obj =
								_objecttype: "orte"
								_mask: "_all_fields"
								_depth: row._depth
								orte:
									_version: 1
									digitale_diathek_id: current_id
									easydb4_reference: row.easydb4_reference
									getty_id: row.getty_id
									gnd_id: row.gnd_id
									viaf_id: row.viaf_id
									preferred_term:
										"en-US": row.easydb_getty_term_preferred_text
									"_nested:orte__terms": []

							if row.easydb4_reference_parent
								obj.orte["lookup:_id_parent"] =
									easydb4_reference: row.easydb4_reference_parent

							payload = payloads[obj._depth]
							if not payload
								payload = payloads[obj._depth] = import_type: "db", objecttype: "orte", objects: []

							payload.objects.push(obj)

						if row.term_preferred # or not current_term_id
							obj.orte.preferred_term =
								"en-US": row.term_text
							obj.orte.getty_link = @getty_link("tgn", row.getty_id, row.term_text)
							obj.orte.gnd_link = @gnd_link(row.gnd_id, row.term_text)

						current_term_id = row.easydb_term_id
						obj.orte["_nested:orte__terms"].push
							term: row.term_text
							_display_order: row.display_order or 99
							preferred: if row.term_preferred then true else false
							language: @lang_link(row.term_getty_language)

						obj.orte["_nested:orte__terms"].sort (a, b) ->
							CUI.util.compareIndex(a._display_order, b._display_order)


					promises = []
					for payload, idx in payloads
						if not payload
							continue
						promises.push(store_file("orte-level-"+idx+"-"+range+".json", payload))

					CUI.when(promises)
					.done(dfr.resolve)
					.fail(dfr.reject)
					return

				return dfr.promise()

		)
		.done =>
			console.debug("getty_subject/orte done.")

		return promise

	lang_link: (lang_ref) ->
		if not lang_ref
			return undefined

		link =
			_objecttype: "sprachen"
			_mask: "_all_fields"
			sprachen:
				"lookup:_id":
					easydb4_reference: lang_ref
		return link

	getty_link: (catalog, subject_id, term) ->
		if not subject_id
			return undefined

		link =
			conceptName: term,
			conceptURI: "http://vocab.getty.edu/"+catalog+"/"+subject_id,
			_fulltext:
				text: term,
				string: term
			_standard:
				text: term

		return link

	gnd_link: (gnd_id, term) ->
		if not gnd_id
			return undefined

		link =
			conceptName: term,
			conceptURI: "http://d-nb.info/gnd/"+gnd_id,
			_fulltext:
				text: term,
				string: term
			_standard:
				text: term

		return link

	sql: (voc_id, offset, limit) ->
		if limit > 0
			off_limit = "LIMIT "+limit
		else
			off_limit = "LIMIT -1"

		off_limit += " OFFSET "+offset

		sql = """
WITH RECURSIVE ids AS (
    SELECT id, 0 as level, lk_easydb_vocabulary_id
    FROM "source.public.getty_subject"
	WHERE lk_easydb_vocabulary_id = """ + voc_id + """ AND fk_father_id IS NULL
UNION ALL
    SELECT a.id, ids.level + 1, a.lk_easydb_vocabulary_id
    FROM "source.public.getty_subject" a
    JOIN ids ON (a.fk_father_id = ids.id)
)
,
ids2 AS (SELECT * FROM ids ORDER BY level, id """ + off_limit + """)

SELECT
   level as _depth,
   a.id as digitale_diathek_id,
   a.getty_subject_id as getty_id,
   "getty_subject:" || a.fk_father_id as easydb4_reference_parent,
   "getty_subject:" || a.id as easydb4_reference,
   a.viaf_id,
   a.gnd_id,
   a.easydb_getty_term_preferred_text,
   b.id AS easydb_term_id,
   b.getty_term_id,
   b.term_text,
   b.display_order,
   b.preferred AS term_preferred,
   "getty_language:" || d.id as term_getty_language,
   d.getty_language_name,
   e.id as easydb_bio_id,
   e.preferred AS bio_preferred,
   e.biography,
   "getty_subject:" || e.birth_place as easydb4_reference_birth_place,
   "getty_subject:" || e.death_place as easydb4_reference_death_place
   FROM ids2 z
   LEFT JOIN "source.public.getty_subject" a ON (z.id = a.id)
   LEFT JOIN "source.public.getty_term" b ON (b.lk_getty_subject_id = a.id)
   LEFT JOIN "source.public.getty_term__getty_language" c ON (c.lk_getty_term_id = b.id)
   LEFT JOIN "source.public.getty_language" d ON (c.lk_getty_language_id = d.id)
   LEFT JOIN "source.public.getty_biography" e ON (e.lk_getty_subject_id = a.id)

ORDER BY a.id, b.id, e.id
"""
		console.debug("SQL:", sql)
		return sql

ez5.session_ready =>
	Easydb4Migration.plugins.registerPlugin(Easydb4MigrationPlugin.EthZuerich)
