create view syncinfo as
with
-- paths to libraries we need to keep around
preserve as (
select distinct 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 and PFC.path not in (select path from waspreserved) then 'new'
when 
	printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
	printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, 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 packages PC
on PC.package = PI.package and PC.status in ('installed','removing','updating')
left join packagefiles PFC
	on PFC.package = PC.package
	and PFC.version = PC.version
	and PFC.release = PC.release
	and PFC.path = PFI.path
where
PI.status = 'installing'

-- every path in updating is either remove or no-op
union
select 'remove',
	printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
	PFU.path, 
	PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
	PFU.mtime, PFU.hash,
	PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
	as configuration,
       	PFU.target, PFU.device,
	null as ohash,
	printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
	null as omds
from packagefiles PFU
join packages PU
on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
-- inner join because the installing package must exist or this shouldn't
-- be an 'updating' package
join packages P on PU.package = P.package and P.status = 'installing'
left join packagefiles PFI on
	PFI.package = P.package
	and PFI.version = P.version
	and PFI.release = P.release
	and PFI.path = PFU.path

-- handle paths owned by other installed packages
left join packages PI on PI.status = 'installed'
left join packagefiles PFC
on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path

where
PU.status in ('updating')
and PFI.path is null
and PFC.path is null
and PFU.path not in (select path from preserve)

-- every path in 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 + case when PFC.configuration = 1 then 2 else 0 end
	as configuration,
       	PFR.target, PFR.device,
	null as ohash,
	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
left join packages P on PU.package = P.package and P.status = 'installing'
left join packagefiles PFI on PFI.path = PFR.path
and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release

-- handle paths owned by other installed packages
left join packages PI on PI.status = 'installed'
left join packagefiles PFC
on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path

where
PU.status in ('removing')
and PFI.path is null
and PFC.path is null
and PFR.path not in (select path from preserve)

-- 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')
;