#!/bin/bash

[ "$PROVISIONER_CONFIG" ] || PROVISIONER_CONFIG=/etc/clouddirectory/provisioner.cf

function err() {
	echo "$0: $*" >&2
	exit 1
}

die_help() {
	if [ "$*" ]; then
		echo "$*" >&2
		echo "" >&2
	fi
	echo "Synopis:" >&2
	echo "$0 --users <userfeed> --eam-cif <eam-cif-feed> --ebvv <ebvv-feed> --pb-rm <pb-rm-feed> --pb-cif <pb-cif-feed> --hierarchy <hierarchy-feed>" >&2
	exit 1
}

die_conf() {
	die_help "missing config setting \$$1 in $PROVISIONER_CONFIG"
}

. $PROVISIONER_CONFIG || err "failed to load $PROVISIONER_CONFIG"

[ "$PROVISIONER_PB_CU_DOMAIN"     ] || die_conf "PROVISIONER_PB_CU_DOMAIN"
[ "$PROVISIONER_PB_CU_CLASS"      ] || die_conf "PROVISIONER_PB_CU_CLASS"

[ "$PROVISIONER_ECU_DOMAIN"     ] || die_conf "PROVISIONER_ECU_DOMAIN"
[ "$PROVISIONER_ECU_CLASS"      ] || die_conf "PROVISIONER_ECU_CLASS"

[ "$PROVISIONER_PB_RM_CLASS"      ] || die_conf "PROVISIONER_PB_RM_CLASS"
[ "$PROVISIONER_PB_RM_DISCLAIMER" ] || die_conf "PROVISIONER_PB_RM_DISCLAIMER"
[ "$PROVISIONER_PB_RM_DOMAIN"     ] || die_conf "PROVISIONER_PB_RM_DOMAIN"

[ "$PROVISIONER_EAM_DOMAIN"       ] || die_conf "PROVISIONER_EAM_DOMAIN"

[ "$PROVISIONER_RME_CLASS"        ] || die_conf "PROVISIONER_RME_CLASS"
[ "$PROVISIONER_RME_DISCLAIMER"   ] || die_conf "PROVISIONER_RME_DISCLAIMER"
[ "$PROVISIONER_RME_DOMAIN"       ] || die_conf "PROVISIONER_RME_DOMAIN"

checkinput() {
	[ "$IN_USERFEED" ]    || die_help "--users not specified"
	[ -f "$IN_USERFEED" ] || die_help "user feed file not found: $IN_USERFEED"

	[ "$IN_EAM_CIF" ]     || die_help "--eam-cif not specified"
	[ -f "$IN_EAM_CIF" ]  || die_help "eam-cif feed file not found: $IN_EAM_CIF"

	[ "$IN_EBVV" ]        || die_help "--ebvv not specified"
	[ -f "$IN_EBVV" ]     || die_help "ebvv feed file not found: $IN_EBVV"

	[ "$IN_PB_RM" ]       || die_help "--pb-rm not specified"
	[ -f "$IN_PB_RM" ]    || die_help "pb-rm feed file not found: $IN_PB_RM"

	[ "$IN_PB_CIF" ]      || die_help "--pb-cif not specified"
	[ -f "$IN_PB_CIF" ]   || die_help "pb-cif feed file not found: $IN_PB_CIF"

	[ "$IN_HIERARCHY" ]   || die_help "--hierarchy not specified"
	[ -f "$IN_HIERARCHY" ]|| die_help "hierarchy feed file not found: $IN_HIERARCHY"
}

to_unix() {
	cat "$1" | \
	    dos2unix			| \
	    sed -e "s~#.*~~g; s~\\x1a.*~~g; s~, ~ ~; s~,~;~g; s~'~\\\'~g"
}

load_header() {
echo "
SET CLIENT_ENCODING TO 'latin1';
"
}

load_pb_rm() {
	echo "
CREATE TEMPORARY TABLE import_pb_rm (
	pid		text,
	org_unit	text,
	firstname	text,
	lastname	text,
	email		text,
	org_unit_id	oid,
	ebvv_id		text
);
"
	to_unix $IN_PB_RM											| \
		grep -vE '^PID;'										| \
		sed -e "s~;~','~g;"										| \
		sed -e "s~^~INSERT INTO import_pb_rm (pid,org_unit,lastname,firstname,email) VALUES ('~"	| \
		sed -e "s~\s*$~');~"										| \
		cat

echo "
	UPDATE import_pb_rm SET pid = trim(lower(pid)), email = trim(lower(email));
"
}

load_pb_cu() {
	echo "
CREATE TEMPORARY TABLE import_pb_cu (
	cif		text,
	rm		text,
	userid		text,
	lastname	text,
	firstname	text,
	ebvv_id		text,
	user_created	boolean default false,
	cif_created	boolean default false,
	duplicate	boolean default false,
	eamppid	text	default	null
);
"
	to_unix $IN_PB_CIF												| \
		grep -vE '^CIF;'											| \
		sed -e "s~;$~~;"											| \
		sed -e 's~,~\\,~'											| \
		sed -e "s~;~','~g"											| \
		sed -e "s~^~INSERT INTO import_pb_cu (cif,rm,userid,lastname,firstname,eamppid) VALUES ('~"			| \
		sed -e "s~\s*$~');~"
echo "
	UPDATE import_pb_cu SET
		cif = trim(lower(cif)),
		rm = trim(lower(rm)),
		userid = trim(lower(userid)),
		firstname = trim(firstname),
		lastname = trim(lastname),
		eamppid = lower(eamppid);

	UPDATE import_pb_cu SET ebvv_id = userid WHERE char_length(userid) IN (6,7);

	CREATE INDEX import_pb_cu_si1 ON import_pb_cu ( cif );
	CREATE INDEX import_pb_cu_si2 ON import_pb_cu ( userid );
"
}

load_userfeed_1() {
	read
	while read line ; do
		case "$line" in
			p*|P*)
			    echo "$line" | \
				sed -e "s~'~\\\'~g"										| \
				sed -e "s~;~',E'~g"										| \
				sed -e "s~^~INSERT INTO import_userfeed_eam(pid,org_unit,lastname,firstname,email) VALUES ('~"	| \
				sed -e "s~\s*$~');~"
			;;
			*)
			    echo "$line" | \
				sed -e "s~'~\\\'~g"										| \
				sed -e "s~;~',E'~g"										| \
				sed -e "s~^~INSERT INTO import_userfeed_int(pid,org_unit,lastname,firstname,email) VALUES ('~"	| \
				sed -e "s~\s*$~');~"
			;;
		esac
	done
}

load_userfeed() {
	echo "CREATE TEMPORARY TABLE import_userfeed_eam ( pid text unique, org_unit text, firstname text, lastname text, email text, ebvv_id text unique );"
	echo "CREATE TEMPORARY TABLE import_userfeed_int ( pid text unique, org_unit text, firstname text, lastname text, email text, org_unit_id oid, ebvv_id text unique );"
	to_unix $IN_USERFEED | sed -e 's~;$~~' | load_userfeed_1
	echo "UPDATE import_userfeed_eam SET pid = lower(pid), email = lower(email);"
	echo "UPDATE import_userfeed_int SET pid = lower(pid), email = lower(email);"
}

load_ebvv() {
	echo "CREATE TEMPORARY TABLE import_ebvv (ebvvid text unique, pid text unique);"
	to_unix $IN_EBVV							| \
		grep -vE "^EBVVID"						| \
		sed -e "s~;~','~g"						| \
		sed -e "s~^~INSERT INTO import_ebvv (ebvvid, pid) VALUES ('~"	| \
		sed -e "s~\s*$~');~"
	echo "UPDATE import_ebvv SET ebvvid = lower(ebvvid), pid = lower(pid);"
}

load_hierarchy() {
	echo "CREATE TEMPORARY TABLE import_hierarchy_raw (pid text, org_unit text, lastname text, firstname text, email text, sub text, org_unit_id oid, parent_id oid);"
	to_unix $IN_HIERARCHY													| \
		grep -vE "^PID"													| \
		sed -e "s~;~','~g"												| \
		sed -e "s~^~INSERT INTO import_hierarchy_raw (pid, org_unit, lastname, firstname, email, sub) VALUES ('~"	| \
		sed -e "s~\s*$~');~"
	echo "CREATE TEMPORARY TABLE import_hierarchy (pid text, org_unit text, lastname text, firstname text, email text, sub text, org_unit_id oid, parent_id oid);"
	echo "INSERT INTO import_hierarchy SELECT DISTINCT * FROM import_hierarchy_raw;"
}

load_eamcif() {
	echo "
CREATE TEMPORARY TABLE import_eamcif (
	eamcif text unique,
	eam_oe_code text,
	eam_oe_code_id oid,
	rme_unit text,
	rme_unit_id oid,
	cc_unit text,
	cc_unit_id oid,
	ia_unit text,
	ia_unit_id oid
);
"
	to_unix $IN_EAM_CIF												| \
		grep -vE '^EAMCIF;'											| \
		grep -vE '^CIF;'											| \
		sed -e "s~;$~~;"											| \
		sed -e "s~^~INSERT INTO import_eamcif (eamcif, eam_oe_code, rme_unit, cc_unit, ia_unit) VALUES ('~"	| \
		sed -e "s~;~','~g"											| \
		sed -e "s~\s*$~');~"
}

import_org_units() {
	echo "

\echo ====> importing org_units

CREATE TEMPORARY TABLE import_orgunit ( id oid, ident text, type_id oid );

-- collect all org units
INSERT INTO import_orgunit ( ident, type_id )
    SELECT DISTINCT eam_oe_code, creditsuisse.org_unit_type_id('EAM OE Code') FROM import_eamcif
        WHERE (eam_oe_code IS NOT NULL) AND (eam_oe_code != '')
    UNION
    SELECT DISTINCT rme_unit, creditsuisse.org_unit_type_id('EAM RME') FROM import_eamcif
        WHERE (rme_unit IS NOT NULL) AND (rme_unit != '')
    UNION
    SELECT DISTINCT cc_unit, creditsuisse.org_unit_type_id('EAM CC') FROM import_eamcif
        WHERE (cc_unit IS NOT NULL) AND (cc_unit != '')
    UNION
    SELECT DISTINCT ia_unit, creditsuisse.org_unit_type_id('EAM IA') FROM import_eamcif
        WHERE (ia_unit IS NOT NULL) AND (ia_unit != '')
    UNION
    SELECT DISTINCT org_unit, creditsuisse.org_unit_type_id('PB RM') FROM import_pb_rm
        WHERE (org_unit IS NOT NULL) AND (org_unit != '')
;

-- drop existing ones
DELETE FROM import_orgunit
    USING creditsuisse.org_unit
    WHERE import_orgunit.ident = creditsuisse.org_unit.ident
;

-- allocate missing ones
INSERT INTO creditsuisse.org_unit ( ident, type_id )
    SELECT DISTINCT ident, type_id
    FROM import_orgunit
;

-- allocate teamleader org_units
INSERT INTO creditsuisse.org_unit ( ident, type_id )
    SELECT DISTINCT org_unit, creditsuisse.org_unit_type_id('TEAMLEADER')
    FROM import_hierarchy
    WHERE NOT EXISTS (SELECT * FROM creditsuisse.org_unit WHERE ident = import_hierarchy.org_unit)
;

UPDATE creditsuisse.org_unit
	SET parent_org = (SELECT id FROM creditsuisse.org_unit AS ou, import_hierarchy
		WHERE import_hierarchy.sub = org_unit.ident AND
		      import_hierarchy.org_unit = ou.ident)
;
"
}

import_employees() {
	echo "

\echo ====> importing employees

-- add EBVV IDs
UPDATE import_userfeed_int
    SET ebvv_id = import_ebvv.ebvvid
    FROM import_ebvv
    WHERE import_userfeed_int.pid = import_ebvv.pid;

-- disable users not existing anymore
CREATE TEMPORARY TABLE import_employee_filter(ident text);

INSERT INTO import_employee_filter(ident)
    SELECT ident FROM creditsuisse.employee;

DELETE FROM import_employee_filter
    USING import_userfeed_int
    WHERE import_employee_filter.ident = import_userfeed_int.pid;

DELETE FROM import_employee_filter
    USING import_pb_rm
    WHERE import_employee_filter.ident = import_pb_rm.pid;

UPDATE creditsuisse.employee
    SET closed = true
    FROM import_employee_filter
    WHERE creditsuisse.employee.ident = import_employee_filter.ident;

-- update existing users
UPDATE creditsuisse.employee SET
    firstname   = import_userfeed_int.firstname,
    lastname    = import_userfeed_int.lastname,
    cs_email    = import_userfeed_int.email,
    org_unit_id = creditsuisse.org_unit.id,
    ebvv_id     = import_userfeed_int.ebvv_id
FROM
    import_userfeed_int, creditsuisse.org_unit
WHERE
    creditsuisse.employee.ident = import_userfeed_int.pid AND
    creditsuisse.org_unit.ident = import_userfeed_int.org_unit;

DELETE FROM import_userfeed_int
    USING creditsuisse.employee
    WHERE pid = creditsuisse.employee.ident;

UPDATE creditsuisse.employee SET
    firstname   = import_pb_rm.firstname,
    lastname    = import_pb_rm.lastname,
    cs_email    = import_pb_rm.email,
    org_unit_id = creditsuisse.org_unit.id,
    ebvv_id     = import_pb_rm.ebvv_id
FROM
    import_pb_rm, creditsuisse.org_unit
WHERE
    creditsuisse.employee.ident = import_pb_rm.pid AND
    creditsuisse.org_unit.ident = import_pb_rm.org_unit;

DELETE FROM import_pb_rm
    USING creditsuisse.employee
    WHERE pid = creditsuisse.employee.ident;

-- insert new users
INSERT INTO creditsuisse.employee (ident,username,firstname,lastname,cs_email,org_unit_id,ebvv_id,disclaimer_type,class)
    SELECT
        pid,
        pid || '@$PROVISIONER_RME_DOMAIN',
        firstname,
        lastname,
        email,
        creditsuisse.org_unit.id,
        ebvv_id,
        '$PROVISIONER_RME_DISCLAIMER',
        '$PROVISIONER_RME_CLASS'
    FROM
        import_userfeed_int,
        creditsuisse.org_unit
    WHERE
        creditsuisse.org_unit.ident = import_userfeed_int.org_unit;

INSERT INTO creditsuisse.employee (ident,username,firstname,lastname,cs_email,org_unit_id,ebvv_id,disclaimer_type,class)
    SELECT
        pid,
        pid || '@$PROVISIONER_PB_RM_DOMAIN',
        firstname,
        lastname,
        email,
        creditsuisse.org_unit.id,
        ebvv_id,
        '$PROVISIONER_PB_RM_DISCLAIMER',
        '$PROVISIONER_PB_RM_CLASS'
    FROM
        import_pb_rm,
        creditsuisse.org_unit
    WHERE
        creditsuisse.org_unit.ident = import_pb_rm.org_unit;
"
}

import_eam() {
	echo "

\echo ====> importing EAMs

-- add EBVV IDs
UPDATE import_userfeed_eam
    SET ebvv_id = import_ebvv.ebvvid
    FROM import_ebvv
    WHERE import_userfeed_eam.pid = import_ebvv.pid;

-- delete EAMs not existing anymore
DELETE FROM creditsuisse.eam WHERE NOT EXISTS (
    SELECT pid FROM import_userfeed_eam WHERE creditsuisse.eam.ident = import_userfeed_eam.pid);

-- update existing EAMs
UPDATE creditsuisse.eam SET
    firstname = import_userfeed_eam.firstname,
    lastname = import_userfeed_eam.lastname,
    org_unit_id = creditsuisse.org_unit.id,
    ebvv_id = import_userfeed_eam.ebvv_id
FROM
    import_userfeed_eam,
    creditsuisse.org_unit
WHERE
    creditsuisse.eam.ident = import_userfeed_eam.pid AND
    creditsuisse.org_unit.ident = import_userfeed_eam.org_unit;

DELETE FROM import_userfeed_eam
    USING creditsuisse.eam
    WHERE creditsuisse.eam.ident = import_userfeed_eam.pid;

-- insert new EAMs
INSERT INTO creditsuisse.eam (ident, username, org_unit_id, firstname, lastname, email_notification, ebvv_id)
    SELECT
        pid,
        pid || '@$PROVISIONER_EAM_DOMAIN',
        creditsuisse.org_unit.id,
        firstname,
        lastname,
        email,
        ebvv_id
    FROM
        import_userfeed_eam,
        creditsuisse.org_unit
    WHERE
        import_userfeed_eam.org_unit = creditsuisse.org_unit.ident;
"
}

import_eam_contract() {
	echo "

\echo ====> importing EAM contracts

-- fix IDs
UPDATE import_eamcif
    SET eam_oe_code_id = creditsuisse.org_unit.id
    FROM creditsuisse.org_unit
    WHERE eam_oe_code = creditsuisse.org_unit.ident;

UPDATE import_eamcif
    SET rme_unit_id = creditsuisse.org_unit.id
    FROM creditsuisse.org_unit
    WHERE rme_unit = creditsuisse.org_unit.ident;

UPDATE import_eamcif
    SET cc_unit_id = creditsuisse.org_unit.id
    FROM creditsuisse.org_unit
    WHERE cc_unit = creditsuisse.org_unit.ident;

UPDATE import_eamcif
    SET ia_unit_id = creditsuisse.org_unit.id
    FROM creditsuisse.org_unit
    WHERE ia_unit = creditsuisse.org_unit.ident;

-- delete EAM contracts not existing anymore
DELETE FROM creditsuisse.eam_contract WHERE NOT EXISTS (
    SELECT eamcif FROM import_eamcif WHERE creditsuisse.eam_contract.ident = import_eamcif.eamcif);

-- update existing EAM contracts
UPDATE creditsuisse.eam_contract
    SET oe_code_id  = import_eamcif.eam_oe_code_id,
        rme_unit_id = import_eamcif.rme_unit_id,
        cc_unit_id  = import_eamcif.cc_unit_id,
        ia_unit_id  = import_eamcif.ia_unit_id
    FROM
        import_eamcif
    WHERE
        ident = import_eamcif.eamcif;

DELETE FROM import_eamcif
    USING creditsuisse.eam_contract
    WHERE eamcif = creditsuisse.eam_contract.ident;

-- insert new EAM contracts
INSERT INTO creditsuisse.eam_contract (ident, oe_code_id, rme_unit_id, cc_unit_id, ia_unit_id )
    SELECT eamcif, eam_oe_code_id, rme_unit_id, cc_unit_id, ia_unit_id
    FROM import_eamcif;
"
}

import_pb_cu() {
	echo "


\echo ====> PB-CU: CU => ECU conversion

update creditsuisse.pb_customer AS old_data set class = 'ECU',username=old_data.ident||'@$PROVISIONER_ECU_DOMAIN' where 
	old_data.cif in (select cif from creditsuisse.pb_customer where cif IN (select cif from import_pb_cu where eamppid != '')) AND 
	class = 'PB-CU';


\echo ====> PB-CU: checking for duplicates
CREATE TEMPORARY TABLE import_pb_ids ( id text, origin text );
INSERT INTO import_pb_ids ( id, origin )
	SELECT userid, 'new' FROM import_pb_cu;
INSERT INTO import_pb_ids ( id, origin )
	SELECT ident, 'old' FROM creditsuisse.pb_customer WHERE closed;

CREATE TEMPORARY VIEW import_pb_dup AS
	SELECT id, count(id)
	FROM import_pb_ids
	GROUP BY id
	HAVING count(id) > 1;

UPDATE import_pb_cu
	SET duplicate = true
	FROM import_pb_dup
	WHERE
		import_pb_cu.userid = import_pb_dup.id;

\echo ====> close dropped PB customer accounts
UPDATE creditsuisse.pb_customer
	SET closed = true
	WHERE
		closed = false AND
		NOT EXISTS (SELECT * FROM import_pb_cu WHERE creditsuisse.pb_customer.ident = import_pb_cu.userid)
;

\echo ====> marking existing users
UPDATE import_pb_cu
	SET user_created = true
	FROM creditsuisse.pb_customer
	WHERE
		userid = creditsuisse.pb_customer.ident
;



\echo ====> creating new users
INSERT INTO creditsuisse.pb_customer(ident,username,cif,firstname,lastname,disclaimer_type,class)
	SELECT
		userid,
		(CASE WHEN import_pb_cu.eamppid != '' 
            THEN
				userid||'@$PROVISIONER_ECU_DOMAIN'
            ELSE
				userid||'@$PROVISIONER_PB_CU_DOMAIN'
        END),
		cif,
		firstname,
		lastname,
		(CASE WHEN import_pb_cu.eamppid != ''
            THEN
            	'$PROVISIONER_ECU_DISCLAIMER'
            ELSE
				'$PROVISIONER_PB_CU_DISCLAIMER'
        END),
		(CASE WHEN import_pb_cu.eamppid != ''
			THEN
				'$PROVISIONER_ECU_CLASS'
			ELSE
				'$PROVISIONER_PB_CU_CLASS'
		END)
	FROM
		import_pb_cu
	WHERE
		NOT user_created AND duplicate IS NOT TRUE
;

update creditsuisse.pb_customer AS old_data set class = 'PB-CU',username=old_data.ident||'@$PROVISIONER_PB_CU_DOMAIN' where old_data.cif in (select cif from creditsuisse.pb_customer where cif IN (select cif from import_pb_cu where eamppid = '')) AND class = 'ECU';

\echo ====> update existing users
UPDATE creditsuisse.pb_customer
	SET
		firstname = import_pb_cu.firstname,
		lastname = import_pb_cu.lastname
	FROM
		import_pb_cu
	WHERE
		creditsuisse.pb_customer.ident = import_pb_cu.userid;

\echo ====> dropping obsolete RM<->CIF assignments
DELETE FROM creditsuisse.pb_cif
	WHERE NOT EXISTS (
		SELECT * FROM import_pb_cu
			WHERE
				pb_cif.ident = import_pb_cu.cif AND
				pb_cif.rm = import_pb_cu.rm
	)
;

\echo ====> clear existing RM<->CIF assignments from loader table
DELETE FROM import_pb_cu
	USING creditsuisse.pb_cif
	WHERE
		import_pb_cu.cif = pb_cif.ident	AND
		import_pb_cu.rm = pb_cif.rm
;

\echo ====> add new RM<->CIF assignments
INSERT INTO creditsuisse.pb_cif (ident, rm)
	SELECT DISTINCT
		cif,
		rm
	FROM
		import_pb_cu
;

\echo ====> copying back duplicate to status table
CREATE TABLE IF NOT EXISTS creditsuisse.prov_pbcu_duplicates ( id text, firstname text, lastname text );
DELETE FROM creditsuisse.prov_pbcu_duplicates;
INSERT INTO creditsuisse.prov_pbcu_duplicates(id, firstname, lastname)
	SELECT
		import_pb_cu.userid,
		import_pb_cu.firstname,
		import_pb_cu.lastname
	FROM
		import_pb_cu,
		import_pb_dup
	WHERE
		import_pb_cu.userid = import_pb_dup.id;
"
}

report_duplicates() {
	DUPLICATES=`echo "SELECT * FROM creditsuisse.prov_pbcu_duplicates" | psql -q -t`
	if [ "$DUPLICATES" ]; then
		(
			echo ""
			echo "WARNING! Duplicate PB customers:"
			echo "================================"
			echo "$DUPLICATES"
			echo ""
		) >&2
	fi
}

generate_sql() {
	load_header
	load_userfeed
	load_eamcif
	load_ebvv
	load_pb_rm
	load_pb_cu
	load_hierarchy
	import_org_units
	import_employees
	import_eam
	import_eam_contract
	import_pb_cu
}

## parse command line arguments
while [ "$1" ]; do
	case "$1" in
		--users)
			shift
			IN_USERFEED="$1"
			shift
		;;
		--eam-cif)
			shift
			IN_EAM_CIF="$1"
			shift
		;;
		--ebvv)
			shift
			IN_EBVV="$1"
			shift
		;;
		--pb-rm)
			shift
			IN_PB_RM="$1"
			shift
		;;
		--pb-cif)
			shift
			IN_PB_CIF="$1"
			shift
		;;
		--hierarchy)
			shift
			IN_HIERARCHY="$1"
			shift
		;;
		--help)
			die_help
		;;
		*)
			die_help "unsupported argument: $1"
		;;
	esac
done

checkinput

generate_sql | psql
#generate_sql 

report_duplicates
