-- pkgatlas schema v1
--
-- One SQLite database per (manager, release, arch) snapshot, or a combined catalog
-- containing many snapshots. The same schema serves both.
--
-- Design notes:
--   * `file.basename` is the hot lookup key (a -lX query becomes basename `libX.so`).
--   * `path` is normalized: no leading slash, directories excluded (see SPEC §3).
--   * Dev-relevant kinds only are stored; the full upstream index is ~6x larger for
--     no benefit (SPEC §3.5).

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- Key/value build metadata: schema_version, generator, built_at, ...
CREATE TABLE IF NOT EXISTS meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- One row per ingested upstream index.
CREATE TABLE IF NOT EXISTS snapshot (
    id            INTEGER PRIMARY KEY,
    manager       TEXT NOT NULL,   -- deb | rpm | pacman
    distro        TEXT NOT NULL,   -- debian | ubuntu | opensuse | fedora | arch
    release       TEXT NOT NULL,   -- trixie | noble | tumbleweed | 43
    arch          TEXT NOT NULL,   -- amd64 | riscv64 | x86_64 | arm64 | noarch
    repo          TEXT,            -- core | extra | oss | main | universe | NULL
    upstream_date TEXT,            -- from Release / repomd, when available
    fetched_at    TEXT NOT NULL,   -- RFC3339
    source_url    TEXT NOT NULL,
    UNIQUE (manager, distro, release, arch, repo)
);

CREATE TABLE IF NOT EXISTS package (
    id          INTEGER PRIMARY KEY,
    snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,     -- binary package name (installable)
    version     TEXT,
    src_package TEXT,
    repo        TEXT
);

CREATE INDEX IF NOT EXISTS idx_package_name     ON package(name);
CREATE INDEX IF NOT EXISTS idx_package_snapshot ON package(snapshot_id);

-- A single file provided by a package.
CREATE TABLE IF NOT EXISTS file (
    id          INTEGER PRIMARY KEY,
    snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
    package_id  INTEGER NOT NULL REFERENCES package(id)  ON DELETE CASCADE,
    path        TEXT NOT NULL,     -- normalized: 'usr/lib/x86_64-linux-gnu/libcurses.so'
    basename    TEXT NOT NULL,     -- 'libcurses.so'
    kind        TEXT NOT NULL      -- lib|static|header|pc|cmake|m4|bin
);

CREATE INDEX IF NOT EXISTS idx_file_basename ON file(basename);
CREATE INDEX IF NOT EXISTS idx_file_path     ON file(path);
CREATE INDEX IF NOT EXISTS idx_file_package  ON file(package_id);
CREATE INDEX IF NOT EXISTS idx_file_snapshot ON file(snapshot_id);

-- Flattened view matching the /v1/resolve response shape.
CREATE VIEW IF NOT EXISTS v_resolve AS
SELECT
    f.basename       AS basename,
    f.path           AS path,
    f.kind           AS kind,
    p.name           AS package,
    p.version        AS pkg_version,
    s.manager        AS manager,
    s.distro         AS distro,
    s.release        AS release,
    s.arch           AS arch,
    s.repo           AS repo
FROM file f
JOIN package  p ON p.id = f.package_id
JOIN snapshot s ON s.id = f.snapshot_id;

-- Core lookup: resolve a library flag to installable packages.
--
--   SELECT DISTINCT package, distro, release, arch, path
--   FROM v_resolve WHERE basename = 'libcurses.so';
--
-- Ambiguity is expected (SPEC §4): '-lcurl' legitimately returns two packages, and the
-- same flag can resolve differently per distro. Callers must treat >1 distinct package
-- as ambiguous rather than picking one.