sprites.sql

-- Game Foundry Studio DEV database DDL
-- Group: Sprites
-- Ownership: docs_build/database/ddl/sprites.sql
-- Target DEV database: gamefoundry_dev
-- Scope: executable grouped table DDL for active Supabase/server API migration.
-- Authoritative key values are generated by the server/API layer.
-- Owned tables: sprite_records, sprite_usage_references
CREATE TABLE IF NOT EXISTS sprite_records (
    key text PRIMARY KEY,
    "gameId" text REFERENCES game_workspace_games(key),
    "ownerUserId" text REFERENCES users(key),
    "name" text NOT NULL,
    "status" text NOT NULL,
    "category" text NOT NULL DEFAULT '',
    "tagKeys" jsonb NOT NULL DEFAULT '[]'::jsonb,
    "source" text NOT NULL DEFAULT '',
    "storageObjectKey" text NOT NULL DEFAULT '',
    "storagePath" text NOT NULL DEFAULT '',
    "originalName" text NOT NULL DEFAULT '',
    "mimeType" text NOT NULL DEFAULT '',
    "width" integer,
    "height" integer,
    "sizeBytes" bigint,
    "checksum" text NOT NULL DEFAULT '',
    "paletteColorKeys" jsonb NOT NULL DEFAULT '[]'::jsonb,
    "archived" boolean NOT NULL DEFAULT false,
    "archivedAt" timestamptz,
    "createdAt" timestamptz NOT NULL DEFAULT now(),
    "updatedAt" timestamptz NOT NULL DEFAULT now(),
    "createdBy" text NOT NULL REFERENCES users(key),
    "updatedBy" text NOT NULL REFERENCES users(key)
);

CREATE INDEX IF NOT EXISTS idx_sprite_records_gameid ON sprite_records ("gameId");
CREATE INDEX IF NOT EXISTS idx_sprite_records_owneruserid ON sprite_records ("ownerUserId");
CREATE INDEX IF NOT EXISTS idx_sprite_records_status ON sprite_records ("status");
CREATE INDEX IF NOT EXISTS idx_sprite_records_createdby ON sprite_records ("createdBy");
CREATE INDEX IF NOT EXISTS idx_sprite_records_updatedby ON sprite_records ("updatedBy");

CREATE TABLE IF NOT EXISTS sprite_usage_references (
    key text PRIMARY KEY,
    "spriteKey" text NOT NULL REFERENCES sprite_records(key),
    "sourceType" text NOT NULL,
    "sourceKey" text NOT NULL,
    "label" text NOT NULL DEFAULT '',
    "createdAt" timestamptz NOT NULL DEFAULT now(),
    "updatedAt" timestamptz NOT NULL DEFAULT now(),
    "createdBy" text NOT NULL REFERENCES users(key),
    "updatedBy" text NOT NULL REFERENCES users(key)
);

CREATE INDEX IF NOT EXISTS idx_sprite_usage_references_spritekey ON sprite_usage_references ("spriteKey");
CREATE INDEX IF NOT EXISTS idx_sprite_usage_references_source ON sprite_usage_references ("sourceType", "sourceKey");
CREATE INDEX IF NOT EXISTS idx_sprite_usage_references_createdby ON sprite_usage_references ("createdBy");
CREATE INDEX IF NOT EXISTS idx_sprite_usage_references_updatedby ON sprite_usage_references ("updatedBy");