ballistic-builder-spring/sql/ImageMetaManagement.sql

-- Auto-update updated_at on UPDATE (PostgreSQL)

-- 1) Generic trigger function
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2) Tables
CREATE TABLE IF NOT EXISTS image_meta (
    id            BIGSERIAL PRIMARY KEY,
    original_name TEXT,
    content_type  TEXT NOT NULL,
    byte_size     BIGINT NOT NULL CHECK (byte_size >= 0),
    sha256        CHAR(64),

    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at    TIMESTAMPTZ NULL
);

CREATE TABLE IF NOT EXISTS image_blob (
    image_id     BIGINT PRIMARY KEY,
    data         BYTEA NOT NULL,

    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at   TIMESTAMPTZ NULL,

    CONSTRAINT fk_image_blob_meta
        FOREIGN KEY (image_id)
        REFERENCES image_meta(id)
        ON DELETE CASCADE
);

-- 3) Triggers (drop first to make script re-runnable)
DROP TRIGGER IF EXISTS trg_image_meta_set_updated_at ON image_meta;
CREATE TRIGGER trg_image_meta_set_updated_at
BEFORE UPDATE ON image_meta
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

DROP TRIGGER IF EXISTS trg_image_blob_set_updated_at ON image_blob;
CREATE TRIGGER trg_image_blob_set_updated_at
BEFORE UPDATE ON image_blob
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- 4) Helpful indexes
CREATE INDEX IF NOT EXISTS idx_image_meta_created_at ON image_meta(created_at);
CREATE INDEX IF NOT EXISTS idx_image_meta_sha256 ON image_meta(sha256);
CREATE INDEX IF NOT EXISTS idx_image_meta_deleted_at ON image_meta(deleted_at);
CREATE INDEX IF NOT EXISTS idx_image_blob_deleted_at ON image_blob(deleted_at);