-- +goose up
CREATE TABLE IF NOT EXISTS analytics_cache (
    id BIGSERIAL PRIMARY KEY,
    user_id TEXT NOT NULL,
    platform TEXT NOT NULL,
    tool_name TEXT NOT NULL,
    time_range_hash TEXT NOT NULL,
    result JSONB NOT NULL,
    computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    UNIQUE (user_id, platform, tool_name, time_range_hash)
);

CREATE INDEX IF NOT EXISTS idx_analytics_cache_lookup 
  ON analytics_cache (user_id, platform, tool_name, time_range_hash);

CREATE INDEX IF NOT EXISTS idx_analytics_cache_expires 
  ON analytics_cache (expires_at);

-- +goose down
DROP INDEX IF EXISTS idx_analytics_cache_expires;
DROP INDEX IF EXISTS idx_analytics_cache_lookup;
DROP TABLE IF EXISTS analytics_cache;