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 view filerefs as
select F.hash,
count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
from files F
left join packagefiles PF on PF.hash = F.hash
left join scripts S on S.hash = F.hash
left join elflibraries EL on EL.file = F.hash
left join notes N on N.file = F.hash
group by F.hash
;

-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
	-- primary key columns
	package	text not null,
	version	text not null, -- the upstream version string
	release	integer not null, -- the local release number

	-- metadata columns
	description	text,
	architecture	text,
	url	text,
	status	text,
	licenses	text, -- hash of actual license?  need table for more than one?
	packager	text,
	build_time	integer default (strftime('%s', 'now')),
	install_time	integer,
	hash	text, -- see integ.c for package hash details
	primary key (package,version,release),
	check (typeof(package) = 'text'),
	check (typeof(version) = 'text'),
	check (typeof(release) = 'integer'),
	check (release > 0),
	-- enforce name and version conventions
	check(instr(package,' ') = 0),
	check(instr(package,'/') = 0),
	check(instr(package,':') = 0),
	check(instr(version,' ') = 0),
	check(instr(version,'-') = 0),
	check(instr(version,'/') = 0),
	check(instr(version,':') = 0),
	check(length(package) < 64),
	check(length(package) > 0),
	check(length(version) < 32),
	check(length(version) > 0)
)
without rowid
;

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_delete_trigger instead of
delete on packages_pkgid
begin
	delete from packages where package = OLD.package
	and version = OLD.version and release = OLD.release;
end;

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 default '0644', -- perms, use text for octal rep?
	username	text not null default 'root', -- name of owner
	groupname	text not null default 'root', -- 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
	confhash text, -- last hash on disk
	filetype varchar not null default 'r',
	-- r regular file
	-- d directory
	-- l 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
	-- s unix domain socket -- not supported
	target	text, -- link target for links
	device	integer, -- device file dev_t
	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 (path not in ('.', '..')),
	check (not (filetype = 'r' and hash is null)),
	check (not (filetype = 'c' and device is null)),
	check (not (filetype = 'b' and device is null)),
	check (filetype in ('r','d','l','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 index pkgfile_configuration on packagefiles (configuration) where configuration = 1;

create view packagefiles_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *,
printf('%s:%s:%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,
	device = NEW.device,
	hash = NEW.hash,
	mtime = NEW.mtime
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	and path = OLD.path
	;
end
;

create trigger packagefiles_delete_trigger instead of
delete on packagefiles_pkgid
begin
	delete from packagefiles
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	and path = OLD.path
	;
	update packages set hash = null
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	;
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 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 index elf_library_name_index on elflibraries(soname);

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

create view package_libraries as
select distinct PF.pkgid, EL.soname
from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
;

create view package_libraries_needed as
with pkglibs as (
		select distinct EN.needed as soname, PF.pkgid
		from elfneeded EN
		join packagefiles_pkgid PF on PF.hash = EN.file
		),
     pkgprovides as (
		     select distinct EL.soname, PF.pkgid
		     from elflibraries EL
		     join packagefiles_pkgid PF on PF.hash = EL.file
		    )
     select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
     from pkglibs PL
     left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
;

-- 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 notes (
	id	integer primary key, -- rowid alias
	ts	text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
	note	text not null,
	pkgid	text, -- package
	path	text, -- file path involved
	file	text, -- hash of file
	ack	integer default 0
);

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
);
-- force the url to be repourl/info.repo
-- package urls repourl/pkgid.zpm

-- 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 
-- metadata different
md_conflict as (
	select path, count(distinct
	printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
       	count(distinct PFA.hash) as hashcount
	from packagefiles PFA
	join packages PA
	on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
	where PA.status in ('installing', 'installed')
	group by path
	having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
	) > 1 or count(distinct PFA.hash) > 1)
)
select PFH.*,
	printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
	printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
'hash' as conflict
from packagefiles PFH
where path in (select path from md_conflict where hashcount > 1)
union
select PFM.*,
	printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
	printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
'md' as conflict
from packagefiles PFM
where path in (select path from md_conflict where mdcount > 1)
;

create view needed_libraries as
with recursive
libs(file,needs,provider) as (
	select N.file, N.needed as needs, L.file as provider
	from elfneeded N left join elflibraries L on N.needed = L.soname
	union
	select L.file, N.needed as needs, EL.file as provider
	from libs L
	join elfneeded N on N.file = L.provider
	left join elflibraries EL on N.needed = EL.soname
)
select * from libs;

/*
 * tables for repository info, essentially materalized views
 */
create table repository_libs (
	pkgid text,
	soname text
);

create table repository_libsneeded (
	pkgid text,
	soname text,
	selfsat integer
);

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

commit;