create view syncconflicts as
with 
-- metadata different
md_conflict as (
	select path, count(distinct
	printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
       	count(distinct PFA.hash) as hashcount
	from packagefiles PFA
	join packages PA
	on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
	where PA.status in ('installing', 'installed')
	group by path
	having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
	) > 1 or count(distinct PFA.hash) > 1)
)
select PFH.*,
	printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
	printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
'hash' as conflict
from packagefiles PFH
	join packages PA
	on PA.package = PFH.package and PA.version = PFH.version and PA.release = PFH.release
where path in (select path from md_conflict where hashcount > 1)
	and PA.status in ('installing', 'installed')
union
select PFM.*,
	printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
	printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
'md' as conflict
from packagefiles PFM
	join packages PA
	on PA.package = PFM.package and PA.version = PFM.version and PA.release = PFM.release
where path in (select path from md_conflict where hashcount > 1)
	and PA.status in ('installing', 'installed')
;