#!/bin/sh

# nangu.tv report and cdr

rsync="/usr/local/bin/rsync"

# report servers
report_servers="s05.olo01.iptv.riomedia.cz"

# report list
report_list="billing-offers billing-tariffs billing-tariffs-offers-prices billing-tariffs-setup-prices \
provisioning-subscribers provisioning-subscribers-subscriptions provisioning-subscriptions-stb-accounts"

# cdr servers
cdr_servers="s01.olo01.iptv.riomedia.cz s02.olo01.iptv.riomedia.cz"

# cdr list
cdr_list="cdr-offer cdr-tvod"

sql=`mktemp /tmp/fetch-cdr-reports.XXXXX`

echo "begin;" > ${sql};

# check for purge
purge=0

if [ "x$1" = "xpurge" ]; then
	purge=1
fi

# sync reports
for server in $report_servers; do

	local=$HOME/data/reports
	mkdir -p ${local}
	${rsync} -a portax@${server}:/mnt/data/reports/global ${local}

	for report in $report_list; do

		echo "process report ${report}"

		for file in `find ${local} -name "${report}_*.csv"`; do

			if [ -f ${file}.out ]; then
				echo "skip ${file}"
				continue
			fi

			echo ${file}
			touch ${file}.out

			echo "truncate table \"${report}\";" >> ${sql}
			echo " -- ${file}" >> ${sql}
			echo "\copy \"${report}\" from stdin with null as 'None' delimiter as ',' csv quote '\"'" >> ${sql}
			sed -e 's/\r\n/\r/g' -e 1d < ${file} >> ${sql}
			echo "\." >> ${sql}

		done

	done

done

# sync cdr data
for cdr in $cdr_list; do

	echo "process cdr ${cdr}"

	local=$HOME/data/cdr

	if [ $purge = 1 ]; then
		echo "truncate table \"${cdr}\";" >> ${sql}
		rm -r ${local}
		purge=0
	fi

	for server in $cdr_servers; do

		mkdir -p ${local}
		${rsync} -a portax@${server}:/mnt/data/srv/tve/cdr-export ${local}

		for file in `find ${local} -name "${cdr}*.csv"`; do

			if [ -f ${file}.out ]; then
				echo "skip ${file}"
				continue
			fi

			path=${file#${local}/cdr-export/}
			isp_id=${path%/*}

			echo ${file}
			touch ${file}.out

			echo " -- ${file}" >> ${sql}
			echo "\copy \"${cdr}\" from stdin with null as '' delimiter as ',' csv quote as '\"'" >> ${sql}
			sed -e "s/^/${isp_id},/g" -e 1d < ${file} >> ${sql}
			echo "\." >> ${sql}

		done

	done

done

echo "truncate table \"provisioning-subscribers-map\";" >> ${sql}
echo "\\copy \"provisioning-subscribers-map\" from stdin with delimiter as ','" >> ${sql}
(
  echo "\copy (select distinct '400-' || s.pn as subscriber_code,'400-' || s.pn || '-' || s.id::varchar as subscription_code,p.pn,p.profile_id from t_stb s join t_profile p on p.mac=s.relay_mac) to stdout with delimiter as ','"
) | /usr/local/bin/psql -h 172.25.7.12 -Uportax >> ${sql}
echo "\\." >> ${sql}

echo "commit;" >> ${sql}

/usr/local/bin/psql -h 172.25.7.12 -Uvod < ${sql} && rm ${sql}

