PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = TRUE;

CREATE TABLE nodes (
      id         INTEGER PRIMARY KEY
    , isdir      INTEGER
    , name       TEXT
);
CREATE INDEX index_name ON nodes (name);

CREATE TABLE closures (
      ancestor   INTEGER NOT NULL
    , descendant INTEGER NOT NULL
    , pathlength INTEGER NOT NULL
    , PRIMARY KEY (ancestor, descendant)
    , FOREIGN KEY (ancestor)   REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
    , FOREIGN KEY (descendant) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX index_descendant_ancestor ON closures (descendant, ancestor);

CREATE VIEW parent_of AS
SELECT id
     , ancestor AS parent_id
     , isdir
     , name
FROM nodes
JOIN closures ON descendant = id AND pathlength = 1;

CREATE TABLE orphans (
      id         INTEGER PRIMARY KEY
    , parent_id  INTEGER
    , isdir      INTEGER
    , name       TEXT
);

CREATE VIEW paths AS
SELECT descendant AS id, GROUP_CONCAT(name, '/') AS path
FROM closures
JOIN nodes ON id = ancestor
GROUP BY descendant
ORDER BY pathlength DESC;

CREATE VIEW roots AS
SELECT descendant AS id
FROM closures
GROUP BY descendant
HAVING SUM(pathlength) = 0;

-- null parent == root
CREATE TRIGGER insert_parent_of_orphans INSTEAD OF INSERT ON parent_of
FOR EACH ROW
WHEN NEW.parent_id IS NOT NULL AND (SELECT id FROM nodes WHERE id = NEW.parent_id LIMIT 1) IS NULL
BEGIN
    INSERT INTO orphans (id, parent_id, isdir, name)
    VALUES (NEW.id, NEW.parent_id, NEW.isdir, NEW.name);
END;

CREATE TRIGGER insert_parent_of_closures INSTEAD OF INSERT ON parent_of
FOR EACH ROW
WHEN NEW.parent_id IS NULL OR (SELECT id FROM nodes WHERE id = NEW.parent_id LIMIT 1) IS NOT NULL
BEGIN
    INSERT INTO nodes (id, isdir, name) VALUES (NEW.id, NEW.isdir, NEW.name);

    INSERT INTO closures (ancestor,descendant,pathlength)
    SELECT ancestor, NEW.id, pathlength+1
    FROM closures
    WHERE descendant = NEW.parent_id
    UNION ALL
    SELECT NEW.id, NEW.id, 0;

    DELETE FROM orphans WHERE id = NEW.id;

    INSERT INTO parent_of
    SELECT * FROM orphans
    WHERE parent_id = NEW.id;
END;

-- moving entire subtree
CREATE TRIGGER update_parent_of_move_subtree INSTEAD OF UPDATE ON parent_of
FOR EACH ROW
WHEN NEW.parent_id != OLD.parent_id
BEGIN
    -- orphanize all descendants, but not self
    INSERT INTO orphans (id, parent_id, isdir, name)
    SELECT id, parent_id, isdir, name
    FROM parent_of
    WHERE id IN (SELECT descendant
                 FROM closures
                 WHERE ancestor = NEW.id AND descendant != NEW.id);

    -- delete all descenants from nodes, including self. Should also delete all related closures
    DELETE FROM nodes WHERE id IN (SELECT descendant FROM closures WHERE ancestor = NEW.id);

    -- re-insert self with new parent, and allow all descendants to repopulate recursively
    INSERT INTO parent_of (id, parent_id, isdir, name) VALUES (NEW.id, NEW.parent_id, NEW.isdir, NEW.name);
END;

-- simple name change
CREATE TRIGGER update_parent_of INSTEAD OF UPDATE ON parent_of
FOR EACH ROW
WHEN NEW.parent_id = OLD.parent_id
BEGIN
    UPDATE nodes
    SET name = NEW.name, isdir = NEW.isdir
    WHERE id = NEW.id;
END;

-- Just insert everything into `parent_of` !