#!/bin/sh
#
# Spk-sql - SliTaz packages DB, the SQLite way. SQLite is used here as a
# fast, OPTIONAL, DERIVED index over the canonical flat lists (packages.info
# & co): `import` rebuilds it from those files, the query commands read it.
# The flat files stay the source of truth, so a system without sqlite3 keeps
# working on the grep code paths. See AGENTS.md (spk-sql workflow, phase 1).
#
# Copyright (C) SliTaz GNU/Linux - BSD License
# Author: See AUTHORS files
#
. /usr/lib/slitaz/libspk.sh

# The derived DB path ($pkgsql) and the freshness helpers (pkgsql_stamp,
# spk_sql_ready, sql) come from libspk.sh.
db="$pkgsql"

#
# Functions
#

usage() {
	name=$(basename $0)
	cat << EOT

$(boldify $(gettext "Usage:")) $name [command] [pattern|package|path]

$(gettext "SliTaz SQLite packages DB (derived index over packages.info)")

$(boldify $(gettext "Commands:"))
  import          $(gettext "(Re)build the DB from the flat package lists")
  search <pat>    $(gettext "Search name + description (--file: search files)")
  owner <path>    $(gettext "Which package owns a file path")
  deps <pkg>      $(gettext "Full recursive dependency closure")
  rdeps <pkg>     $(gettext "Full recursive reverse-dependency closure")
  list            $(gettext "List every catalogued package")
  query <sql>     $(gettext "Run a raw SQL query (escape hatch)")
  dbi             $(gettext "Database file info (size, counts, stamp)")
  tables          $(gettext "List DB tables")

$(boldify $(gettext "Options:"))
  --file          $(gettext "Make 'search' match file paths (needs files-list)")
  --raw           $(gettext "Plain TSV output (scriptable, no formatting)")

EOT
	exit 0
}

# Single-quote a value for safe interpolation into a SQL string literal.
sql_quote() { printf "%s" "$1" | sed "s/'/''/g"; }

# Create the schema fresh in $1 (default $db). SQLite as a derived cache:
# drop and rebuild. import() builds into a temp copy and renames on success.
create_db() {
	local target="${1:-$db}"
	rm -f "$target"
	sqlite3 "$target" << 'EOT'
CREATE TABLE meta(key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE package(
	repo        TEXT,
	name        TEXT,
	version     TEXT,
	category    TEXT,
	short_desc  TEXT,
	web_site    TEXT,
	tags        TEXT,
	packed      TEXT,
	unpacked    TEXT,
	rsum        TEXT,
	noarch      INTEGER
);
CREATE TABLE depend(name TEXT, dep_name TEXT);
CREATE TABLE provide(name TEXT, provide_name TEXT);
CREATE TABLE file(repo TEXT, name TEXT, path TEXT);
EOT
}

# Append one repo's rows to the staging TSVs. The TSV columns are emitted in
# table order, so sqlite3 .import loads them verbatim (no SQL escaping, and
# bulk-fast). $1 = repo label, $2 = dir holding packages.info/.$SUM/files-list.
stage_repo() {
	local label="$1" dir="$2"
	local info="$dir/packages.info"
	local fl="$dir/files-list.lzma"
	[ -f "$info" ] || return 0

	# package + depend + provide, in one awk pass over packages.info.
	awk -F'\t' -v repo="$label" \
		-v po="$tmpdir/package.tsv" -v de="$tmpdir/depend.tsv" \
		-v pr="$tmpdir/provide.tsv" '
		{
			split($7, sz, " ");                    # field 7 = "packed unpacked"
			noarch = (NF >= 11 ? $11 : 0);
			printf "%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n", \
				repo, $1, $2, $3, $4, $5, $6, sz[1], sz[2], $9, noarch \
				>> po;
			n = split($8, d, " ");                 # field 8 = DEPENDS
			for (i = 1; i <= n; i++)
				if (d[i] != "") printf "%s\t%s\n", $1, d[i] >> de;
			m = split($10, p, " ");                # field 10 = PROVIDE
			for (i = 1; i <= m; i++)
				if (p[i] != "") printf "%s\t%s\n", $1, p[i] >> pr;
		}' "$info"

	# file table from files-list.lzma ("pkg: /path"). Skipped when the list
	# is absent (e.g. no lzma compressor on the mirror) -> 'owner'/--file
	# just fall back to nothing; install/search by name still work.
	if [ -f "$fl" ]; then
		busybox unlzma -c "$fl" 2>/dev/null \
			| sed "s/^\([^:]*\): /${label}\t\1\t/" >> "$tmpdir/file.tsv"
	fi
}

import_db() {
	command -v sqlite3 >/dev/null || { gettext "ERROR: sqlite3 not found"; newline; exit 1; }
	check_root
	mkdir -p "$tmpdir"
	: > "$tmpdir/package.tsv"; : > "$tmpdir/depend.tsv"
	: > "$tmpdir/provide.tsv"; : > "$tmpdir/file.tsv"

	# Official mirror, then every registered extra repo.
	stage_repo official "$(dirname "$pkgsinfo")"
	for d in "$extradb"/*/; do
		[ -d "$d" ] || continue
		stage_repo "$(basename "$d")" "${d%/}"
	done

	# Build into a temp DB and rename on success, so an interrupted or failed
	# import (Ctrl-C, disk full, malformed TSV) leaves the previous index in
	# place instead of wiping it — the fast paths keep working off the old DB
	# until a clean rebuild lands. `rm -f "$db"` up front used to leave every
	# reader with an empty/stampless DB the moment a rebuild started.
	local dbtmp="$db.new"
	create_db "$dbtmp"
	# One transaction: bulk-load the staged TSVs, index, stamp.
	if sqlite3 "$dbtmp" << EOT
.mode tabs
BEGIN;
.import '$tmpdir/package.tsv' package
.import '$tmpdir/depend.tsv' depend
.import '$tmpdir/provide.tsv' provide
.import '$tmpdir/file.tsv' file
COMMIT;
CREATE INDEX idx_pkg_name ON package(name);
CREATE INDEX idx_dep_name ON depend(name);
CREATE INDEX idx_dep_dep ON depend(dep_name);
CREATE INDEX idx_prov ON provide(provide_name);
CREATE INDEX idx_file_path ON file(path);
INSERT INTO meta VALUES('stamp', '$(sql_quote "$(pkgsql_stamp)")');
INSERT INTO meta VALUES('built', '$(date '+%Y-%m-%d %H:%M')');
EOT
	then
		mv -f "$dbtmp" "$db"
	else
		rm -f "$dbtmp"
		rm -rf "$tmpdir"
		gettext "ERROR: import failed; kept the previous index"; newline
		return 1
	fi
	rm -rf "$tmpdir"

	local n=$(sqlite3 "$db" 'SELECT count(*) FROM package')
	gettext "Indexed packages:"; colorize 32 " $n"
}

# Pick the sqlite output mode from --raw (tabs) vs human (column).
sql_mode() { [ "$raw" ] && echo tabs || echo column; }

#
# Handle commands
#

# Query commands need an existing index; only import/gendb build it. Guard
# here so a query against a missing DB prints a clear hint instead of letting
# sqlite3 silently CREATE an empty packages.sql and then fail "no such table"
# on this and every later call.
case "$1" in
	import|gendb|""|*usage|*help) ;;
	*) [ -f "$db" ] || { gettext "No DB (run: spk-sql import)"; newline; exit 1; } ;;
esac

case "$1" in
	""|*usage|*help) usage ;;

	import|gendb) import_db ;;

	search)
		pat=$(sql_quote "$2")
		if [ "$file" ]; then
			sqlite3 "$db" ".mode $(sql_mode)" \
				"SELECT DISTINCT name, path FROM file
				 WHERE path LIKE '%${pat}%' ORDER BY name;"
		else
			# GROUP BY name: one row per package even when it is offered by
			# several repos (official + local/extra).
			sqlite3 "$db" ".mode $(sql_mode)" \
				"SELECT name, version, short_desc FROM package
				 WHERE name LIKE '%${pat}%' OR short_desc LIKE '%${pat}%'
				 GROUP BY name ORDER BY name;"
		fi ;;

	owner)
		path=$(sql_quote "$2")
		sqlite3 "$db" ".mode $(sql_mode)" \
			"SELECT DISTINCT name FROM file WHERE path='${path}';" ;;

	deps)
		pkg=$(sql_quote "$2")
		sqlite3 "$db" ".mode $(sql_mode)" \
			"WITH RECURSIVE r(n) AS (
				SELECT dep_name FROM depend WHERE name='${pkg}'
				UNION
				SELECT d.dep_name FROM depend d JOIN r ON d.name=r.n
			 ) SELECT DISTINCT n FROM r ORDER BY n;" ;;

	rdeps)
		pkg=$(sql_quote "$2")
		sqlite3 "$db" ".mode $(sql_mode)" \
			"WITH RECURSIVE r(n) AS (
				SELECT name FROM depend WHERE dep_name='${pkg}'
				UNION
				SELECT d.name FROM depend d JOIN r ON d.dep_name=r.n
			 ) SELECT DISTINCT n FROM r ORDER BY n;" ;;

	list)
		sqlite3 "$db" ".mode $(sql_mode)" \
			"SELECT name, version, category FROM package
			 GROUP BY name ORDER BY name;" ;;

	query)
		sqlite3 "$db" ".mode $(sql_mode)" "$2" ;;

	dbi)
		[ -f "$db" ] || { gettext "No DB (run: spk-sql import)"; newline; exit 1; }
		gettext "Database :"; echo " $db"
		gettext "Size     :"; echo " $(du -sh "$db" | cut -f1)"
		gettext "Packages :"; echo " $(sqlite3 "$db" 'SELECT count(*) FROM package')"
		gettext "Files    :"; echo " $(sqlite3 "$db" 'SELECT count(*) FROM file')"
		gettext "Stamp    :"; echo " $(sqlite3 "$db" "SELECT value FROM meta WHERE key='stamp'")"
		gettext "Built    :"; echo " $(sqlite3 "$db" "SELECT value FROM meta WHERE key='built'")" ;;

	tables) sqlite3 "$db" '.tables' ;;

	*) usage ;;
esac

exit 0
