-- Durable quota and mutation protocol state for Nuthatch.

CREATE TABLE IF NOT EXISTS pools (
    pool_id      TEXT PRIMARY KEY,
    limit_bytes  BIGINT NOT NULL CHECK (limit_bytes > 0),
    used_bytes   BIGINT NOT NULL DEFAULT 0 CHECK (used_bytes >= 0),
    reserved_bytes BIGINT NOT NULL DEFAULT 0 CHECK (reserved_bytes >= 0)
);

INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
VALUES ('shared', 10737418240, 0, 0)
ON CONFLICT (pool_id) DO NOTHING;

CREATE TABLE IF NOT EXISTS mutations (
    user_id           TEXT NOT NULL,
    kind              TEXT NOT NULL CHECK (kind IN ('put', 'delete')),
    operation_id      TEXT NOT NULL,
    pool_id           TEXT NOT NULL REFERENCES pools (pool_id),
    object_key        TEXT NOT NULL,
    relative_path     TEXT NOT NULL,
    declared_size     BIGINT NOT NULL CHECK (declared_size >= 0),
    content_sha256    TEXT,
    prior_size        BIGINT NOT NULL CHECK (prior_size >= 0),
    prior_sha256      TEXT,
    growth_bytes      BIGINT NOT NULL CHECK (growth_bytes >= 0),
    state             TEXT NOT NULL CHECK (
                        state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')
                      ),
    external_state    TEXT NOT NULL DEFAULT 'idle' CHECK (
                        external_state IN ('idle', 'pending', 'settled')
                      ),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, kind, operation_id)
);

CREATE INDEX IF NOT EXISTS mutations_active_key_idx
    ON mutations (object_key)
    WHERE state IN ('prepared', 'applying');

CREATE INDEX IF NOT EXISTS mutations_active_idx
    ON mutations (state)
    WHERE state IN ('prepared', 'applying');

CREATE TABLE IF NOT EXISTS key_locks (
    object_key   TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL,
    kind         TEXT NOT NULL,
    operation_id TEXT NOT NULL,
    acquired_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (user_id, kind, operation_id)
        REFERENCES mutations (user_id, kind, operation_id)
);

CREATE TABLE IF NOT EXISTS objects_meta (
    object_key   TEXT PRIMARY KEY,
    pool_id      TEXT NOT NULL REFERENCES pools (pool_id),
    size_bytes   BIGINT NOT NULL CHECK (size_bytes >= 0),
    content_sha256 TEXT NOT NULL,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);