-- lfs: squashed from 0200_lfs.sql, 0905_lfs_shared_storage.sql.

-- Which repositories may serve an oid. owner_id is denormalized so triggers can bill without a join
-- (the repo row may already be gone during a cascading delete).
CREATE TABLE lfs_objects (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  oid TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  size INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, oid)
);

CREATE INDEX lfs_objects_oid ON lfs_objects (oid);

CREATE TRIGGER lfs_objects_link AFTER INSERT ON lfs_objects BEGIN
  UPDATE lfs_blobs SET refs = refs + 1, orphaned_at = NULL WHERE oid = NEW.oid;
  INSERT OR IGNORE INTO lfs_account_objects (owner_id, oid, size, refs) VALUES (NEW.owner_id, NEW.oid, NEW.size, 0);
  UPDATE lfs_account_objects SET refs = refs + 1 WHERE owner_id = NEW.owner_id AND oid = NEW.oid;
  INSERT OR IGNORE INTO lfs_usage (owner_id) SELECT NEW.owner_id WHERE EXISTS (SELECT 1 FROM accounts WHERE id = NEW.owner_id);
  UPDATE lfs_usage SET storage_bytes = storage_bytes + NEW.size
    WHERE owner_id = NEW.owner_id
      AND (SELECT refs FROM lfs_account_objects WHERE owner_id = NEW.owner_id AND oid = NEW.oid) = 1;
END;

CREATE TRIGGER lfs_objects_unlink AFTER DELETE ON lfs_objects BEGIN
  UPDATE lfs_blobs SET refs = MAX(refs - 1, 0) WHERE oid = OLD.oid;
  UPDATE lfs_blobs SET orphaned_at = CAST(unixepoch('subsec') * 1000 AS INTEGER) WHERE oid = OLD.oid AND refs = 0;
  UPDATE lfs_account_objects SET refs = refs - 1 WHERE owner_id = OLD.owner_id AND oid = OLD.oid;
  UPDATE lfs_usage SET storage_bytes = MAX(storage_bytes - OLD.size, 0)
    WHERE owner_id = OLD.owner_id
      AND (SELECT refs FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid) = 0;
  DELETE FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid AND refs <= 0;
END;

-- Transfers move billing to the new owner.
CREATE TRIGGER lfs_objects_rebill AFTER UPDATE OF owner_id ON lfs_objects WHEN OLD.owner_id != NEW.owner_id BEGIN
  UPDATE lfs_account_objects SET refs = refs - 1 WHERE owner_id = OLD.owner_id AND oid = OLD.oid;
  UPDATE lfs_usage SET storage_bytes = MAX(storage_bytes - OLD.size, 0)
    WHERE owner_id = OLD.owner_id
      AND (SELECT refs FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid) = 0;
  DELETE FROM lfs_account_objects WHERE owner_id = OLD.owner_id AND oid = OLD.oid AND refs <= 0;
  INSERT OR IGNORE INTO lfs_account_objects (owner_id, oid, size, refs) VALUES (NEW.owner_id, NEW.oid, NEW.size, 0);
  UPDATE lfs_account_objects SET refs = refs + 1 WHERE owner_id = NEW.owner_id AND oid = NEW.oid;
  INSERT OR IGNORE INTO lfs_usage (owner_id) VALUES (NEW.owner_id);
  UPDATE lfs_usage SET storage_bytes = storage_bytes + NEW.size
    WHERE owner_id = NEW.owner_id
      AND (SELECT refs FROM lfs_account_objects WHERE owner_id = NEW.owner_id AND oid = NEW.oid) = 1;
END;

CREATE TABLE lfs_locks (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  path TEXT NOT NULL,
  ref_name TEXT,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  UNIQUE (repo_id, path)
);

CREATE INDEX lfs_locks_repo_created ON lfs_locks (repo_id, created_at, id);

CREATE TABLE lfs_usage (
  owner_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  storage_bytes INTEGER NOT NULL DEFAULT 0,
  bandwidth_period TEXT NOT NULL DEFAULT '',
  bandwidth_bytes INTEGER NOT NULL DEFAULT 0
);

-- The single stored copy. refs counts lfs_objects rows; at 0 the sweeper deletes it after a grace period.
CREATE TABLE lfs_blobs (
  oid TEXT PRIMARY KEY,
  size INTEGER NOT NULL,
  r2_key TEXT NOT NULL,
  refs INTEGER NOT NULL DEFAULT 0,
  orphaned_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX lfs_blobs_orphaned ON lfs_blobs (orphaned_at) WHERE orphaned_at IS NOT NULL;

-- Per-account billing: refs counts the account's repositories linking the oid; billed while refs > 0.
CREATE TABLE lfs_account_objects (
  owner_id TEXT NOT NULL,
  oid TEXT NOT NULL,
  size INTEGER NOT NULL,
  refs INTEGER NOT NULL,
  PRIMARY KEY (owner_id, oid)
);