#!/bin/bash

#REPOS="core extras nonfree"
REPOS="core extras"
#REPOS="core"
MAIN="main"

DBSUFFIX="db"
DBFILE="${MAIN}.${DBSUFFIX}"

die()
{
	echo -e "Exited ${BASH_SOURCE} at line no ${BASH_LINENO}."
	echo -e "$@"
	exit 1
}

sql()
{
	local sqlcmd="$*"
	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."

	sqlite3 -nullvalue 'NULL' -list -separator '|' "${DBFILE}" << EOF || die "error running '$@'"
${sqlcmd};
EOF
}

sql_all_repos_attached()
{
	local sqlcmd="$*"
	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."

	local SQLQUERY_ATTACH
	local SQLQUERY_DETACH

	for db in ${REPOS}
	do
		# attach database
		SQLQUERY_ATTACH+="attach database '${db}.${DBSUFFIX}' as ${db};"

		# detach database
		SQLQUERY_DETACH+="detach database ${db};"
	done

	sqlite3 -nullvalue 'NULL' -list -separator '|' "${DBFILE}" << EOF || die "error running '$@'"
${SQLQUERY_ATTACH};
${sqlcmd};
${SQLQUERY_DETACH};
EOF
}

if [[ -f ${DBFILE} ]]
then
	echo "deleting old ${DBFILE}..."
	rm ${DBFILE}
fi

# first of all create all sufficient tables
SQLQUERY="create table categories(id integer primary key, pcat text unique);"
SQLQUERY+="create table packages(id integer primary key,pname text,pver text,pbuild text,pcat_id numeric,state text,provide text,pkgtype text,repo_id numeric);"
SQLQUERY+="create table packages_info(id integer primary key, pkg_id numeric, arch text, md5 text, mtime numeric, homepage text, description text, size numeric);"
SQLQUERY+="create table depends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
SQLQUERY+="create table sdepends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
SQLQUERY+="create table virtual_defaults(id integer primary key, vcat text,vname text, pcat text, pname text);"
# run it
sql "${SQLQUERY}"

# add table with all loaded repos
SQLQUERY="create table repositories(id integer primary key, name text);"
for db in ${REPOS}
do
	SQLQUERY+="insert into ${MAIN}.repositories(name) values('${db}');"
done
sql "${SQLQUERY}"


#
# categories
#

# get all categories from all repos
SQLQUERY="select pcat from ${MAIN}.categories"
for db in ${REPOS}
do
	SQLQUERY+=" union select pcat from ${db}.categories"
done
SQLQUERY+=";"
all_categories=$(sql_all_repos_attached "${SQLQUERY}")
# insert all categories
for pcat in ${all_categories}
do
	sql "insert into ${MAIN}.categories(pcat) values('${pcat}')"
done




#
# virtual_defaults
#

# get all categories from all repos
SQLQUERY="select vcat,vname,pcat,pname from ${MAIN}.virtual_defaults"
for db in ${REPOS}
do
	SQLQUERY+=" union select vcat,vname,pcat,pname from ${db}.virtual_defaults"
done
SQLQUERY+=";"
all_virtual_defaults=$(sql_all_repos_attached "${SQLQUERY}")
# insert all virtual_defaults
for line in ${all_virtual_defaults}
do
	vcat="$(echo ${line} | cut -d'|' -f1)"
	vname="$(echo ${line} | cut -d'|' -f2)"
	pcat="$(echo ${line} | cut -d'|' -f3)"
	pname="$(echo ${line} | cut -d'|' -f4)"

	sql "insert into ${MAIN}.virtual_defaults(vcat,vname,pcat,pname) values('${vcat}','${vname}','${pcat}','${pname}')"
done




#
# packages
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of packages with corrected pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.packages(pname,pver,pbuild,pcat_id,state,provide,pkgtype,repo_id)
		select ${db}.packages.pname,
			${db}.packages.pver,
			${db}.packages.pbuild,
			${MAIN}.categories.id,
			${db}.packages.state,
			${db}.packages.provide,
			${db}.packages.pkgtype,
			${MAIN}.repositories.id
		from ${db}.packages
		inner join ${db}.categories
			on ${db}.categories.id=${db}.packages.pcat_id
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat
		inner join ${MAIN}.repositories
			on ${MAIN}.repositories.name='${db}';"
	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"




#
# packages_info
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of packages_info with corrected pkg_ids from main database
	SQLQUERY+="insert into ${MAIN}.packages_info(pkg_id,arch,md5,mtime,homepage,description,size)
		select ${MAIN}.packages.id,
			${db}.packages_info.arch,
			${db}.packages_info.md5,
			${db}.packages_info.mtime,
			${db}.packages_info.homepage,
			${db}.packages_info.description,
			${db}.packages_info.size
		from ${db}.packages_info
		inner join ${db}.packages
			on ${db}.packages.id=${db}.packages_info.pkg_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"




#
# depends
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of depends with corrected pkg_ids and pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.depends(pkg_id,relation,pcat_id,pname,pver,pbuild)
		select ${MAIN}.packages.id,
			${db}.depends.relation,
			${MAIN}.categories.id,
			${db}.depends.pname,
			${db}.depends.pver,
			${db}.depends.pbuild
		from ${db}.depends
		inner join ${db}.packages
			on ${db}.packages.id=${db}.depends.pkg_id
		inner join ${db}.categories
			on ${db}.categories.id=${db}.depends.pcat_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"




#
# sdepends
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of sdepends with corrected pkg_ids and pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.sdepends(pkg_id,relation,pcat_id,pname,pver,pbuild)
		select ${MAIN}.packages.id,
			${db}.sdepends.relation,
			${MAIN}.categories.id,
			${db}.sdepends.pname,
			${db}.sdepends.pver,
			${db}.sdepends.pbuild
		from ${db}.sdepends
		inner join ${db}.packages
			on ${db}.packages.id=${db}.sdepends.pkg_id
		inner join ${db}.categories
			on ${db}.categories.id=${db}.sdepends.pcat_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"