#!/bin/bash
set -e

# deb-control
# Section: utils
# Depends: coreutils, sqlite3, allpkgpath
# Description:'
#  Convert file-list.tsv to file-list.sqlite3
#  .
#  Usage:
#   tsv2sqlite3 <file.tsv>
# '
# FILES:"
# -- создаёт file-unowned.sqlite3
#   /usr/local/bin/unowned-make.sh
# --
# -- классификатор скриптов (подключаемая библиотека)
#   /usr/local/bin/script-classifier.sh
# --
# -- классификатор программ (unowned-файлов, имеющих влаг +x)
#   /usr/local/bin/unowned-progs.sh
# --
# -- скрипт формирующий исходный tsv файл путём перечисления всех файлов в системе
#   /usr/local/bin/allfileslist
# "
# /deb-control

declare -A FUNC_MASKS=(
    ["/boot/"]="sys"
    ["/tmp/"]="tmp"

    ["/var/lib/cache/"]="1"
    ["/var/lib/"]="1"
    ["/var/cache/"]="1"
    ["/var/log/"]="1"

    ["/etc/default/"]="1"
    ["/etc/hosts"]="sys"
    ["/etc/"]="1"

    ["/usr/share/"]="1"
    ["/usr/lib/modules/"]="sys"
    ["/usr/lib/"]="1"

    ["/opt/"]="1"
)

if [[ $# -ne 1 ]]; then
    echo "Usage: $0 file.tsv"
    exit 1
fi

TSV="$1"
# Замена любого исходного расширения TSV на ".tsv"
if [[ "$(basename -- "$TSV")" == *.* ]]; then
    TSV="${TSV%.*}.tsv"
else
    TSV="$TSV.tsv"
fi

# Имя базы данных получаем заменой расширения на .sqlite3
DB="${TSV%.*}.sqlite3"

ALLPKG_DB="/var/lib/allpkgpath/allpkgpath.sqlite3"

if [[ ! -f "$TSV" ]]; then
    echo "File not found: $TSV"
    exit 1
fi

if [[ ! -f "$ALLPKG_DB" ]]; then
    echo "File not found: $ALLPKG_DB"
    exit 1
fi

if [[ -e "$DB" ]]; then
    echo "Database already exists: $DB , Overwrite it?"
    read -n 1 -r -s -p "Press any key to overwrite, or Ctrl+C to stop..."
    rm -f "$DB"
    echo
fi

FILTERED=$(mktemp)
MASKS=$(mktemp)

trap 'rm -f "$FILTERED" "$MASKS"' EXIT


#
# Фильтруем псевдофайловые системы
#

awk '!/^\/(proc|sys|dev|run)(\/|$)/' "$TSV" > "$FILTERED"
#$FILTERED="$TSV"


#
# Формируем TSV масок
#

for mask in "${!FUNC_MASKS[@]}"; do
    printf '%s\t%s\n' "$mask" "${FUNC_MASKS[$mask]}"
done > "$MASKS"


#
# Создаём основную БД
#

sqlite3 "$DB" <<EOF

CREATE TABLE files (
    path TEXT PRIMARY KEY,
    size INTEGER NOT NULL
);

.mode tabs
.import '$FILTERED' files

ALTER TABLE files ADD COLUMN package TEXT;
ALTER TABLE files ADD COLUMN evidence TEXT;

CREATE INDEX idx_files_size ON files(size);

CREATE TEMP TABLE func_masks (
    mask  TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

.mode tabs
.import '$MASKS' func_masks

ATTACH DATABASE '$ALLPKG_DB' AS allpkg;

BEGIN;

--
-- 1. Точные совпадения с allpkgpath.files
--

UPDATE files
SET
    package = (
        SELECT f.package
        FROM allpkg.files AS f
        WHERE f.path = files.path
    ),
    evidence = 'files'
WHERE EXISTS (
    SELECT 1
    FROM allpkg.files AS f
    WHERE f.path = files.path
);


--
-- 2. Фиксированные функциональные маски
--
-- Например:
-- /boot/...      -> sys
-- /tmp/...       -> tmp
-- /home/...      -> user
--

UPDATE files
SET
    package = (
        SELECT m.value
        FROM func_masks AS m
        WHERE m.value != '1'
          AND substr(files.path, 1, length(m.mask)) = m.mask
        ORDER BY length(m.mask) DESC
        LIMIT 1
    ),
    evidence = 'mask'
WHERE package IS NULL
  AND EXISTS (
        SELECT 1
        FROM func_masks AS m
        WHERE m.value != '1'
          AND substr(files.path, 1, length(m.mask)) = m.mask
  );


--
-- 3. Маски со значением 1.
--
-- После маски берём следующий компонент пути
-- и проверяем его как имя Debian-пакета.
--
-- Например:
--
-- /usr/share/foo/bar
--       ^^^
--       проверяем "foo"
--
-- /etc/default/grub
--             ^^^^
--             проверяем "grub"
--

UPDATE files
SET
    package = (
        SELECT p.package
        FROM func_masks AS m
        JOIN allpkg.packages AS p
          ON p.package =
             CASE
                 WHEN substr(files.path, length(m.mask) + 1)
                      LIKE '%/%'
                 THEN substr(
                     substr(files.path, length(m.mask) + 1),
                     1,
                     instr(
                         substr(files.path, length(m.mask) + 1),
                         '/'
                     ) - 1
                 )
                 ELSE substr(files.path, length(m.mask) + 1)
             END
        WHERE m.value = '1'
          AND substr(files.path, 1, length(m.mask)) = m.mask
        ORDER BY length(m.mask) DESC
        LIMIT 1
    ),
    evidence = 'mask-package'
WHERE package IS NULL
  AND EXISTS (
        SELECT 1
        FROM func_masks AS m
        JOIN allpkg.packages AS p
          ON p.package =
             CASE
                 WHEN substr(files.path, length(m.mask) + 1)
                      LIKE '%/%'
                 THEN substr(
                     substr(files.path, length(m.mask) + 1),
                     1,
                     instr(
                         substr(files.path, length(m.mask) + 1),
                         '/'
                     ) - 1
                 )
                 ELSE substr(files.path, length(m.mask) + 1)
             END
        WHERE m.value = '1'
          AND substr(files.path, 1, length(m.mask)) = m.mask
  );

COMMIT;

DETACH DATABASE allpkg;

EOF


echo "Created: $DB"
echo "Files: $(sqlite3 "$DB" 'SELECT COUNT(*) FROM files;')"
echo "Classified: $(sqlite3 "$DB" 'SELECT COUNT(*) FROM files WHERE package IS NOT NULL;')"
echo "Unclassified: $(sqlite3 "$DB" 'SELECT COUNT(*) FROM files WHERE package IS NULL;')"

echo
echo "Evidence:"
sqlite3 -header -column "$DB" '
    SELECT
        COALESCE(evidence, "unclassified") AS evidence,
        COUNT(*) AS cnt
    FROM files
    GROUP BY evidence
    ORDER BY cnt DESC;
'
