begin;

PRAGMA application_id = 0x5a504442;
PRAGMA user_version = 1;

-- should be faster with rowid due to the blob content
-- these are really just blobs of data
-- TODO copyright and license information should probably
-- go here
CREATE TABLE files (
	hash text primary key, -- sha256 of content
       	size integer, -- bigint?  certainly need > 2GB
       	compression text, -- always xz?
       	content blob
)
;



create index package_status_index on packages (status);
create index package_package_index on packages (package);

create view packages_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from packages;

create trigger packages_update_trigger instead of
update on packages_pkgid
begin
	update packages
	set package = NEW.package,
	version = NEW.version,
	release = NEW.release,
	description = NEW.description,
	architecture = NEW.architecture,
	url = NEW.url,
	status = NEW.status,
	licenses = NEW.licenses,
	packager = NEW.packager,
	build_time = NEW.build_time,
	install_time = NEW.install_time,
	hash = NEW.hash
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	;
end
;

-- handle package status history with a logging trigger.
create trigger logpkgstatus after update of status on packages
begin insert into zpmlog (action,target,info)
       	values (printf('status change %s %s', OLD.status, NEW.status),
		printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
		NULL); END;

create table packagetags (
	-- package id triple
	package	text,
	version	text,
	release	integer,
	tag	text,
	set_time integer default (strftime('%s', 'now')),
	primary key (package,version,release,tag),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

-- files contained in a package
create table packagefiles (
	-- package id triple
	package	text,
	version	text,
	release	integer,

	path	text, -- filesystem path
	mode	text not null, -- perms, use text for octal rep?
	username	text not null, -- name of owner
	groupname	text not null, -- group of owner
	uid	integer, -- numeric uid, generally ignored
	gid	integer, -- numeric gid, generally ignored
	configuration integer not null default 0, -- boolean if config file
	filetype varchar not null default 'r',
	-- r regular file
	-- d directory
	-- s symlink
	-- h hard link -- not supported
	-- c character special -- not supported
	-- b block special -- not supported
	-- c and b device special files add dev number column
	-- p fifos (i.e. pipe) -- not supported
	target	text, -- link target for links
	-- device file dev numbers
	devmajor	integer,
	devminor	integer,
	hash	text, -- null if not a regular file
	mtime	integer, -- seconds since epoch, finer resolution not needed
	primary key (package,version,release,path),
	foreign key (package,version,release)
		references packages (package,version,release)
		on delete cascade on update cascade,
	check (not (filetype = 'l' and target is null)),
	check (not (filetype = 'h' and target is null)),
	check (target is null or length(target) between 1 and 4095),
	check (hash is null or length(hash) between 1 and 1024),
	check (not (filetype = 'r' and hash is null)),
	check (not (filetype = 'c' and (devmajor is null or devminor is null))),
	check (not (filetype = 'b' and (devmajor is null or devminor is null))),
	check (filetype in ('r','d','s','h','c','b','p')),
	check(length(username) between 1 and 256),
	check(length(groupname) between 1 and 256),
	check (configuration = 0 or configuration = 1)
)
without rowid
;

create index packagefile_package_index on packagefiles (package);
create index packagefile_path_index on packagefiles (path);
create index packagefile_hash_index on packagefiles (hash);

create view packagefiles_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *,
printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
from packagefiles
;

create trigger packagefiles_update_trigger instead of
update on packagefiles_pkgid
begin
	update packagefiles
	set package = NEW.package,
	version = NEW.version,
	release = NEW.release,
	path = NEW.path,
	mode = NEW.mode,
	username = NEW.username,
	groupname = NEW.groupname,
	uid = NEW.uid,
	gid = NEW.gid,
	configuration = NEW.configuration,
	filetype = NEW.filetype,
	target = NEW.target,
	devmajor = NEW.devmajor,
	devminor = NEW.devminor,
	hash = NEW.hash,
	mtime = NEW.mtime
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	and path = OLD.path
	;
end
;

create view installed_ref_count as
select I.path, count(*) as refcount
from installedfiles I
group by I.path
;

create view sync_status_ref_count as
select path, status, count(*) as refcount
from packagefiles_status
where status in ('installed', 'installing', 'removing')
group by path, status
;

create view packagefiles_status as
select P.status, PF.*
from packagefiles_pkgid PF
left join packages_pkgid P on P.pkgid = PF.pkgid
;

create view installedfiles as
select * from packagefiles_status
where status = 'installed'
;

create view install_status as

select 'new' as op, PN.*
from packagefiles_status PN
left join installed_ref_count RC on RC.path = PN.path
where RC.refcount is null
and PN.status = 'installing'

union all

select 'update' as op, PN.*
from packagefiles_status PN
inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
left join installed_ref_count RC on RC.path = PN.path
where RC.refcount = 1
and PN.status = 'installing'
and PI.hash is not PN.hash

union all

select 'conflict' as op, PI.*
from packagefiles_status PN
inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
where PN.status = 'installing'

union all
select 'remove' as op, PI.*
from installedfiles PI
left join packagefiles_status PN
    on PI.path = PN.path and PI.package = PN.package
	and PI.pkgid != PN.pkgid
where PN.path is null
and PI.package in (select package from packages where status = 'installing')

union all
-- remove files in removing, but not installing
select distinct 'remove' as op, PR.*
from packagefiles_status PR
left join packagefiles_status PN
on PR.path = PN.path
and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
where PN.path is null
and PR.status = 'removing'
;

create table pathtags (
	-- package id triple
	package	text,
	version	text,
	release	integer,

	path	text, -- filesystem path
	tag	text,
	primary key (package,version,release,path,tag),
	foreign key (package,version,release,path)
	references packagefiles on delete cascade on update cascade
)
without rowid
;

create view elfdeps as
select PF.pkgid, PF.status, PF.path, N.needed as needs,
PL.path as library, PL.pkgid provider, PL.status as library_status
from packagefiles_status PF
join elfneeded N on N.file = PF.hash
left join elflibraries L on N.needed = L.soname
left join packagefiles_status PL on PL.hash = L.file
;

-- TODO just elf information?
-- and just hash, not package?
create table elflibraries (
	file	text primary key,
	soname	text
)
without rowid
;

create table elfneeded (
	file	text,
	needed	text, -- soname of dependency
	primary key (file, needed)
)
without rowid
;

-- package scripts: table of package, stage, file
create table scripts (
	package	text,
	version	text,
	release	integer,
	stage	text,
	hash	text,
	primary key (package,version,release,stage),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

create view scripts_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from scripts
;

-- package dependencies: table of package, dependency, dep type (package, soname)
create table packagedeps (
	package	text,
	version	text,
	release	integer,
	requires text, -- package name (only)
	minimum text,
	maximum text,
	primary key (package,version,release,package),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

-- capability labels
create table provides (
	package	text,
	subpackage	text,
	label	text -- a capability label
);

create table requires (
	package	text,
	subpackage	text,
	label	text -- a capability label
);

create table packagegroups (
	package	text,
	"group"	text
);

-- zpm actions
-- not sure how machine readable this needs to be,
-- do not at all for now, figure it out later
-- could be worth logging all commands in a history table,
-- the zpm driver could do that and capture the exit status
-- as well
-- might want the history table to note a "group" to tie together
-- sub-invocations, probably an environment variable set if not
-- already set by zpm, probably a uuid or a timestamp
create table zpmlog (
	ts	text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
       	-- timestamp of action
	action	text,
	target	text, -- packagename, repo name, etc
	info	text -- human readable
);

create table history (
	ts	integer, -- again, probably needs timestamp sub second
	cmd	text,
	args	text,
	status	integer
);

create table repository (
	name	text primary key, -- our name for a repo
	url	text not null,
	priority	integer not null default 1,
	refreshed	integer -- last refresh time
);

-- urls for downloading packages.  possibly unneeded
create table repository_packages (
	repo	text,
	pkg	text, -- glob pattern?  in which case others not needed
	version	text,
	release	text,
	url	text
);

-- track which repository a package was cloned from, i.e. where we got it
create table packagesource (
	name	text,
	version	text,
	release	integer,
	repository	text references repository
);

create view syncconflicts as
with
preserved as (
	select BASE.*, 'preserved' as rstatus
	from packagefiles_status BASE
	join elflibraries EL on EL.file = BASE.hash
	where
	BASE.status in ('removed', 'updated')
	and BASE.hash in (select hash from packagefiles_status where
		status in ('installed'))
),
syncstatus as (
	select distinct BASE.*,
       	case when P.status = 'installing' and BASE.status = 'installed'
		then 'updating'
	when BASE.status in ('removed','updated')
		and BASE.path in (select path from preserved) then
		'preserved'
	else
		BASE.status
	end as rstatus
	from packagefiles_status BASE
	left join packages P on P.package = BASE.package
		and BASE.status in ('installed', 'removing')
		and P.status = 'installing'
),
-- metadata different
md_conflict as (
	select path, count(distinct mds) as mdcount,
       	count(distinct hash) as hashcount
	from syncstatus SS
	where SS.rstatus in ('installing', 'installed')
	group by path
	having (count(distinct mds) > 1 or count(distinct hash) > 1)
)
select BASE.*, 'hash' as conflict
from syncstatus BASE
where path in (select path from md_conflict where hashcount > 1)
union
select BASE.*, 'md' as conflict
from syncstatus BASE
where path in (select path from md_conflict where mdcount > 1)
;

create view syncinfo as
with
preserved as (
	select BASE.*, 'preserved' as rstatus
	from packagefiles_status BASE
	join elflibraries EL on EL.file = BASE.hash
	where
	BASE.status in ('removed', 'updated')
	and BASE.hash in (select hash from packagefiles_status where
		status in ('installed'))
),
syncstatus as (
	select distinct BASE.*,
       	case when P.status = 'installing' and BASE.status = 'installed'
		then 'updating'
	when BASE.status in ('removed','updated')
		and BASE.path in (select path from preserved) then
		'preserved'
	else
		BASE.status
	end as rstatus
	from packagefiles_status BASE
	left join packages P on P.package = BASE.package
		and BASE.status in ('installed', 'removing')
		and P.status = 'installing'
),
-- new file: in installing, not in installed or updating or removing
newfiles as (
	select distinct
       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
	target,devminor,devmajor
	from syncstatus SS
	where path not in (select path from syncstatus where
		rstatus in ('installed', 'updating', 'removing')
	)
	and rstatus in ('installing')
),
-- modified: retained, but with different metadata
modified as (
	select distinct
	SS.path, 
	SS.username,
       	SS.uid, SS.groupname, SS.gid, SS.mode,
	SS.filetype, SS.mtime, SS.hash, SS.target, SS.devminor, SS.devmajor
	from syncstatus SS
	join syncstatus OS
	on SS.path = OS.path and SS.pkgid is not OS.pkgid
	-- preserved?
	and OS.rstatus in ('installed','updating','removing')
	and (SS.mds is not OS.mds or SS.hash is not OS.hash)
	where
	SS.rstatus in ('installing')
),
-- preserve: libraries needed by something in installed or installing
needed as (
	select distinct
       	ED.library
	from elfdeps ED
	where status in ('installed', 'installing')
	and library is not null
),
preserve as (
	select distinct
       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
	target,devminor,devmajor
	from syncstatus SS
	where path in (select library from needed)
	and SS.rstatus in ('removing', 'removed')
),
-- remove: cur, not preserved, not in final set
remove as (
	select distinct
       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
	target,devminor,devmajor
	from syncstatus SS
	where path not in (
		select path from syncstatus where
		rstatus in ('installed', 'installing')
	)
	and path not in (select path from preserve)
	and rstatus in ('removing', 'updating')
),
-- expired: libraries that had been preserved, but aren't needed now
expired as (
	select distinct
       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
	target,devminor,devmajor
	from syncstatus BASE
	where hash in (select file from elflibraries where file is not null)
	and path not in (select path from preserve)
	and rstatus in ('removed','updated')
)
select 'update' as op, * from modified
union
select 'remove' as op, * from remove
union
select 'obsolete' as op, * from expired
union
select 'new' as op, * from newfiles
union
select 'preserve' as op, * from preserve
;

commit;
