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