-- ============================================================
-- AEGIS MANIFEST (SQLite) - UPDATED: datastore label visible, inner requires PIN
-- ============================================================

-- ----------------------------
-- PRAGMAS (aplicar na abertura da conexão)
-- ----------------------------
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;

-- ============================================================
-- USERS (OIDC identities)
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
                                     user_id        TEXT PRIMARY KEY,          -- GUID/ULID (string)
                                     created_at     INTEGER NOT NULL,           -- epoch seconds
                                     disabled_at    INTEGER,                    -- null = active

    -- OIDC identity (stable pairing)
                                     subject        TEXT NOT NULL,              -- "sub"
                                     issuer         TEXT NOT NULL,              -- "iss"

                                     display_name   TEXT,
                                     email          TEXT,

                                     UNIQUE(subject, issuer)
    );

-- ============================================================
-- DATASTORES
-- ============================================================
-- IMPORTANT:
-- - datastore name is decryptable WITHOUT PIN (using Label Key mechanism)
-- - datastore inner content requires PIN (Master Key unwrapped with PIN-derived KEK)

CREATE TABLE IF NOT EXISTS datastores (
                                          datastore_id     TEXT PRIMARY KEY,          -- GUID/ULID
                                          created_at       INTEGER NOT NULL,
                                          deleted_at       INTEGER,

    -- --------------------------
    -- LABEL (visible without PIN)
    -- --------------------------
    -- name_enc is encrypted with a "Label Key" (LK) that does NOT require datastore PIN.
    -- LK can be derived from a server secret, KMS, or any non-PIN mechanism.
                                          name_enc         BLOB NOT NULL,
                                          name_nonce       BLOB NOT NULL,

    -- Identifies which LK mechanism/version was used (for rotation/migration).
    -- Examples: "lk:server:v1", "lk:kms:key/123", "lk:yubikey:piv:9c"
                                          lk_kid           TEXT NOT NULL,
                                          lk_version       INTEGER NOT NULL DEFAULT 1,

    -- --------------------------
    -- MASTER KEY (PIN protected)
    -- --------------------------
    -- mk_wrapped is the datastore Master Key (MK) encrypted ("wrapped") with a KEK derived from the datastore PIN.
                                          mk_wrapped       BLOB NOT NULL,
                                          mk_nonce         BLOB NOT NULL,
                                          mk_version       INTEGER NOT NULL DEFAULT 1,

    -- PIN KDF parameters (so you can change KDF settings per datastore over time)
                                          pin_kdf          TEXT NOT NULL DEFAULT 'argon2id',  -- 'argon2id' recommended
                                          pin_salt         BLOB NOT NULL,
    -- Store params as JSON text (portable and easy to evolve)
    -- Example: {"memory_kib":65536,"iterations":3,"parallelism":1,"output_len":32}
                                          pin_kdf_params   TEXT NOT NULL,

    -- Optional: quick verifier to reduce unwrap attempts (can be null).
    -- If used, store an AEAD-encrypted constant (e.g., "AEGIS_PIN_OK") with KEK_pin and a nonce.
    -- Otherwise you simply "try unwrap MK" and treat failure as "wrong PIN".
                                          pin_verifier_enc   BLOB,
                                          pin_verifier_nonce BLOB,

    -- Root node of the datastore tree
                                          root_node_id     TEXT NOT NULL,

                                          FOREIGN KEY(root_node_id) REFERENCES nodes(node_id)
    );

-- ============================================================
-- DATASTORE MEMBERS (authorization)
-- ============================================================

CREATE TABLE IF NOT EXISTS datastore_members (
                                                 datastore_id   TEXT NOT NULL,
                                                 user_id        TEXT NOT NULL,
                                                 role           TEXT NOT NULL,              -- 'OWNER'|'ADMIN'|'EDITOR'|'VIEWER'
                                                 created_at     INTEGER NOT NULL,

                                                 PRIMARY KEY(datastore_id, user_id),
    FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)
    );

-- ============================================================
-- NODES (logical tree) - INTERNAL: requires PIN
-- ============================================================

CREATE TABLE IF NOT EXISTS nodes (
                                     node_id        TEXT PRIMARY KEY,           -- GUID/ULID
                                     datastore_id   TEXT NOT NULL,
                                     parent_id      TEXT,                       -- null only for root
                                     kind           INTEGER NOT NULL,           -- 1=DIR, 2=FILE
                                     created_at     INTEGER NOT NULL,
                                     updated_at     INTEGER NOT NULL,
                                     deleted_at     INTEGER,                    -- soft delete/trash

    -- Encrypted node name (requires MK -> PIN)
                                     name_enc       BLOB NOT NULL,
                                     name_nonce     BLOB NOT NULL,

    -- File-only fields (kind=FILE)
                                     main_object_id TEXT,                       -- FK objects.object_id
                                     size_plain     INTEGER,                    -- optional (can leak info; keep or encrypt later)
                                     mime_enc       BLOB,                       -- optional: encrypt mime to reduce metadata leak
                                     mime_nonce     BLOB,

                                     version        INTEGER NOT NULL DEFAULT 1, -- optimistic lock

                                     FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
    FOREIGN KEY(parent_id) REFERENCES nodes(node_id),
    FOREIGN KEY(main_object_id) REFERENCES objects(object_id)
    );

-- ============================================================
-- OBJECTS (encrypted blobs) - INTERNAL: requires PIN
-- ============================================================

CREATE TABLE IF NOT EXISTS objects (
                                       object_id      TEXT PRIMARY KEY,           -- GUID/ULID
                                       datastore_id   TEXT NOT NULL,
                                       created_at     INTEGER NOT NULL,

    -- AEAD algorithm info
                                       cipher         TEXT NOT NULL,              -- e.g. 'AES-256-GCM'
                                       nonce          BLOB NOT NULL,              -- base nonce / object nonce (format depends on your file container)

    -- DEK wrapped by datastore MK (requires PIN)
                                       dek_wrapped    BLOB NOT NULL,
                                       dek_nonce      BLOB NOT NULL,

    -- Reference to blob location (also encrypted to avoid path leaks)
                                       blob_ref_enc   BLOB NOT NULL,
                                       blob_ref_nonce BLOB NOT NULL,

                                       size_cipher    INTEGER NOT NULL,
                                       hash_plain     BLOB,                       -- optional (SHA-256) for integrity/dedupe v2

                                       FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id)
    );

-- ============================================================
-- DERIVATIVES (thumbs/previews/posters/etc.) - INTERNAL: requires PIN
-- ============================================================

CREATE TABLE IF NOT EXISTS derivatives (
                                           derivative_id  TEXT PRIMARY KEY,           -- GUID/ULID
                                           datastore_id   TEXT NOT NULL,
                                           node_id        TEXT NOT NULL,              -- owning file node
                                           created_at     INTEGER NOT NULL,
                                           updated_at     INTEGER NOT NULL,

                                           kind           TEXT NOT NULL,              -- 'THUMB'|'POSTER'|'PREVIEW'|'WAVEFORM'|'SPRITE'...
                                           profile        TEXT NOT NULL,              -- '256w','512w','page1','t=3s','wave:1024',...

                                           object_id      TEXT,                       -- object containing the derivative blob (when READY)
                                           status         INTEGER NOT NULL,           -- 0=PENDING,1=READY,2=FAILED

    -- store error details encrypted (may contain sensitive paths/info)
                                           error_enc      BLOB,
                                           error_nonce    BLOB,

                                           FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
    FOREIGN KEY(node_id) REFERENCES nodes(node_id),
    FOREIGN KEY(object_id) REFERENCES objects(object_id),

    UNIQUE(datastore_id, node_id, kind, profile)
    );

-- ============================================================
-- JOBS (SQLite queue) - does NOT require PIN itself
-- Payload should be encrypted with MK if it contains sensitive info (recommended).
-- ============================================================

CREATE TABLE IF NOT EXISTS jobs (
                                    job_id         TEXT PRIMARY KEY,           -- GUID/ULID
                                    datastore_id   TEXT NOT NULL,
                                    created_at     INTEGER NOT NULL,
                                    available_at   INTEGER NOT NULL,           -- for backoff scheduling
                                    picked_at      INTEGER,
                                    finished_at    INTEGER,

                                    kind           TEXT NOT NULL,              -- 'GEN_DERIVATIVES'|'REWRAP_KEYS' etc.

                                    payload_enc    BLOB NOT NULL,
                                    payload_nonce  BLOB NOT NULL,

                                    status         INTEGER NOT NULL,           -- 0=QUEUED,1=RUNNING,2=DONE,3=FAILED
                                    attempts       INTEGER NOT NULL DEFAULT 0,

                                    FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id)
    );

-- ============================================================
-- AUDIT LOG (encrypted details) - INTERNAL: may require PIN depending on what you put in details
-- ============================================================

CREATE TABLE IF NOT EXISTS audit_log (
                                         audit_id       TEXT PRIMARY KEY,           -- GUID/ULID
                                         datastore_id   TEXT NOT NULL,
                                         created_at     INTEGER NOT NULL,
                                         actor_user_id  TEXT,                       -- null for system/worker
                                         action         TEXT NOT NULL,              -- 'UPLOAD','RENAME','MOVE','DELETE','DOWNLOAD','UNLOCK','LOCK'...

                                         target_node_id TEXT,
                                         details_enc    BLOB NOT NULL,
                                         details_nonce  BLOB NOT NULL,

                                         FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
    FOREIGN KEY(actor_user_id) REFERENCES users(user_id),
    FOREIGN KEY(target_node_id) REFERENCES nodes(node_id)
    );

-- ============================================================
-- INDEXES (hot paths)
-- ============================================================

-- list directory: by datastore + parent, excluding deleted
CREATE INDEX IF NOT EXISTS idx_nodes_parent
    ON nodes(datastore_id, parent_id, deleted_at);

CREATE INDEX IF NOT EXISTS idx_nodes_parent_kind
    ON nodes(datastore_id, parent_id, kind, deleted_at);

CREATE INDEX IF NOT EXISTS idx_nodes_main_object
    ON nodes(main_object_id);

CREATE INDEX IF NOT EXISTS idx_objects_datastore_created
    ON objects(datastore_id, created_at);

CREATE INDEX IF NOT EXISTS idx_derivatives_node
    ON derivatives(datastore_id, node_id, kind, status);

-- jobs queue
CREATE INDEX IF NOT EXISTS idx_jobs_queue
    ON jobs(status, available_at);

-- members lookup
CREATE INDEX IF NOT EXISTS idx_members_user
    ON datastore_members(user_id);

-- datastore listing for a user typically joins members -> datastores
CREATE INDEX IF NOT EXISTS idx_members_datastore
    ON datastore_members(datastore_id);

-- audit time queries
CREATE INDEX IF NOT EXISTS idx_audit_datastore_time
    ON audit_log(datastore_id, created_at);

-- ============================================================
-- TRIGGERS (touch updated_at / version)
-- NOTE: SQLite default PRAGMA recursive_triggers is OFF, so these won't recurse infinitely.
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_nodes_touch
AFTER UPDATE ON nodes
                            FOR EACH ROW
BEGIN
UPDATE nodes
SET updated_at = strftime('%s','now'),
    version = version + 1
WHERE node_id = NEW.node_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_derivatives_touch
AFTER UPDATE ON derivatives
                            FOR EACH ROW
BEGIN
UPDATE derivatives
SET updated_at = strftime('%s','now')
WHERE derivative_id = NEW.derivative_id;
END;