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
-- but we have to calculate them separately I think.
-- once for new, once for update, and once for no-op, might be able
-- to combine update and no-op
select
'new' 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, null 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
	null 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

left join packages PC
on (PFI.package is not PC.package or PFI.release is not PC.release or PC.version is not PFI.version)
and PC.status in ('installed','removing','updating')
and PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release
*/

where
PI.status = 'installing'
and
not exists (select PFC.path
	from packagefiles PFC
	join packages PC
on PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release
	where PFC.path = PFI.path
	and PC.status in ('installed','removing','updating')
)

/*
(PFC.path is null
	or
PC.status not in ('installed','removing','updating')
)
*/

union
select
case
when PFI.filetype is not 'l'
	and 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'
when PFI.filetype is 'l'
	and PFI.target is PFC.target
	and 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'
when PC.status = 'installed' then 'md conflict'
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 P
on PFI.package = P.package and PFI.version = P.version and PFI.release = P.release

join packagefiles PFC on PFI.path = PFC.path

join packages PC
on (PFI.package is not PC.package or PFI.release is not PC.release or PC.version is not PFI.version)
-- er, no, it's a conflict if it's in installed and it doesn't match
and PC.status in ('installed','updating','removing')
and PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release

where
P.status = 'installing'
--and PFC.path is not null

-- 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

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