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

-- 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
--	pkgid	text, -- the three above joined with '-'

	-- 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,
	checksum	text, -- checksum of package contents.  null for incompleted packages
	primary key (package,version,release),
	check (typeof(package) = 'text'),
	check (typeof(version) = 'text'),
	check (typeof(release) = 'integer'),
	check (release > 0)
	-- TODO enforce name and version conventions
	-- check(instr(version,'-') = 0)
	-- check(instr(package,'/') = 0)
	-- check(instr(package,'/') = 0)
	-- check(instr(version,' ') = 0)
	-- check(instr(package,' ') = 0)
	-- check(instr(package,' ') = 0)
	-- check(length(package) < 64)
	-- check(length(version) < 32)
)
without rowid
;

create index package_status_index on packages (status);

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,
	checksum = NEW.checksum
	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
);

-- packagefile hash is columns as text, joined with null bytes, then
-- sha256 sum of that
-- package checksum is package columns as text, joined with null bytes,
-- other than the checksum and install_time column
-- then that hashed.  finally, that hash, plus the ascii sorted
-- hashes of the package files all joined with newlines, hashed.
-- really don't like this.

-- 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 and b device special files add dev number column
	-- b block special
	-- p fifos (i.e. pipe)
	target	text, -- link target for links
	-- device file dev numbers, should probably be a separate table
	devmajor	integer,
	devminor	integer,
	hash	text, -- null if no actual content, i.e. anything but a regular file
	mtime	integer, -- seconds since epoch, finer resolution probably 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 = 'r' and hash is null)),
	check (not (filetype = 'c' and (devmajor is null or devminor is null)))
)
without rowid
;

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

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 table elfinfo (
	file	text primary key, -- hash of blob
	elftype	text,
	foreign key (file) references files on delete cascade
)
without rowid
;

create table elfdeps (
	file	text,
	soname	text,
	dependency text,
	primary key (file, soname, dependency),
	foreign key (file) references files on delete cascade
)
without rowid
;

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

create table elfneeded (
	file	text,
	needed	text, -- soname of dependency
	primary key (file, needed),
	foreign key (file) references files on delete cascade
)
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
);

commit;