create view syncinfo as
with
-- paths to libraries we need to keep around
preserve as (
select
PFL.*
from packagefiles PFL
join elflibraries EL on EL.file = PFL.hash
join elfneeded EN on EN.needed = EL.soname
join packagefiles PFN on EN.file = PFN.hash
join packages PN
on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
where
PN.status = 'installing' or PN.status = 'installed'
and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
),
waspreserved as (
	select PF.path
	from packagefiles PF
	join elflibraries EL on EL.file = PF.hash
	join elfneeded EN on EN.needed = EL.soname
	join packagefiles PL on EN.file = PL.hash
	join packages P
	on PL.path = P.package and PL.version = P.version and PL.release = P.release
	where
	P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
)

-- every path in 'installing' is either new or update, or no-op
select
case when --PFC.path is null
	P.status is null
       	and PFC.path not in (select path from waspreserved)
	-- if the path is in preserved, this will an update with no old md.
       	then 'new'
when PFI.filetype is PFC.filetype
	and PFI.mode is PFC.mode and PFI.username is PFC.username
       	and PFI.groupname is PFC.groupname and PFI.hash is PFC.hash
	then 'noop'
else 'update'
end as op,
	printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
	PFI.path, PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode,
       	PFI.filetype, PFI.mtime, PFI.hash,
	PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
	as configuration,
       	PFI.target, PFI.device, PFC.hash as ohash,
	printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
	printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
from
packagefiles PFI
join packages PI
on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
left join packagefiles PFC on PFI.path = PFC.path
and (PFI.package is not PFC.package or PFI.release is not PFC.release or PFC.version is not PFI.version)
left join packages P 
	on PFC.package = P.package and PFC.version = P.version and PFC.release = P.release
and P.status in ('installed','removing','updating')
where
PI.status = 'installing'
and (
	--P.status is null or
	PFC.path is null
	or
       	P.status in ('installed','removing','updating')
)

-- every path in updating and removing is either remove or no-op
-- not true, could be an update, but should be handled above
union
select 'remove',
	printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
	PFR.path, 
	PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
	PFR.mtime, PFR.hash,
	PFR.configuration, 
       	--PFR.target, coalesce(P.status, ' null status '), --PFR.device,
       	-- PFI.path, coalesce(PFI.status, ' null status '), --PFR.device,
	PFR.target, PFR.device,
	null as ohash,
	--PFI.package,
	printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
	null as omds
from packagefiles PFR
join packages PU
on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release and PU.status in ('removing','updating')

where
not exists (select path from
	packagefiles PFI
	join packages P
	on PFI.package = P.package and PFI.version = P.version and PFI.release = P.release and P.status in ('installing','installed')
	where PFI.path = PFR.path

)

-- paths in 'installed' or 'updated' are no-ops

union
select 'preserve',
	printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
	PFP.path, 
	PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
	PFP.mtime, PFP.hash,
	PFP.configuration as configuration,
       	PFP.target, PFP.device,
	null as ohash,
	printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
	null as omds
from preserve PFP
	-- TODO don't include installed or installing
	-- left join packages P on P.package = PFP.package ...
	-- where P.status not in ('installed','installing')
;